Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Проблемы с CLR агрегирющей функцией  [new]
Alexey30
Member

Откуда:
Сообщений: 163
Добрый день.

Есть CLR функция для получения данных для фильтров.
Фильтры бывают двух видов:

1) Посчитать количество записей с определнным признаком (Красных - 3, Зеленых - 6)
2) Найти максимальное и минимальное значение (Цена от 10 до 50)

Код CLR функции:

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined, //use clr serialization to serialize the intermediate result
    IsInvariantToNulls = true, //optimizer property
    IsInvariantToDuplicates = true, //optimizer property
    IsInvariantToOrder = true, //optimizer property
    IsNullIfEmpty = true,
    MaxByteSize=-1) //maximum size in bytes of persisted value
]
public struct FiltersAgg  : IBinarySerialize
{
    private Dictionary<Int64, Int64> values;
    private Int64 _min, _max;
    private int filterTypeID;
 
    public void Init()
    {
        values = new Dictionary<Int64, Int64>();
        _min = Int64.MaxValue;
        _max = Int64.MinValue;
    }

    public void Accumulate(SqlInt64 value, SqlInt32 FilterTypeID)
    {
        filterTypeID = FilterTypeID.Value;
        switch(FilterTypeID.Value)
        {
            case 1:
                if (value.IsNull)
                    return;
                if (values.ContainsKey(value.Value))
                    values[value.Value]++;
                else
                    values.Add(value.Value, 1);
                break;
            case 2:
                if (value.IsNull)
                    return;
                if (value.Value < _min)
                    _min = value.Value;
                if (value.Value > _max)
                    _max = value.Value;
                break;
        }
    }

    public void Merge(FiltersAgg other)
    {
        switch(filterTypeID)
        {
            case 1:
                foreach (var q in other.values.Keys)
                {
                    if (values.ContainsKey(q))
                        values[q] += other.values[q];
                    else
                        values.Add(q, other.values[q]);
                }
                break;
            case 2:
                if (other._min < _min)
                    _min = other._min;
                if (other._max > _max)
                    _max = other._max;

                break;
        }
    }

    public SqlString Terminate()
    {
        string res = null;
        switch (filterTypeID)
        {
            case 1:
                res = JoinItems();
                break;
            case 2:
                res = _min + "=" + _max;
                break;
        }
        return new SqlString(res);
    }

    private string JoinItems()
    {
        var sb = new StringBuilder();

        foreach(Int64 k in values.Keys)
            sb.Append(k + "=" + values[k] + ",");

        if (values.Count > 0)
            sb.Remove(sb.Length - 1, 1);
        
        return sb.ToString();
    }

    public void Read(BinaryReader r)
    {
        filterTypeID = r.ReadInt32();
        _min = r.ReadInt64();
        _max = r.ReadInt64();

        values = new Dictionary<Int64, Int64>();
        int quan = r.ReadInt32();
        for (int i = 0; i < quan; i++)
            values.Add(r.ReadInt64(), r.ReadInt64());
     }

    public void Write(BinaryWriter w)
    {
        w.Write(filterTypeID);
        w.Write(_min);
        w.Write(_max);
        w.Write(values.Count);
        foreach (Int64 k in values.Keys)
        {
            w.Write(k);
            w.Write(values[k]);
        }
    }
}


Ранее все работало нормально, но теперь очень странная ситуация.
Вызываем фильтры для всей таблицы, без фильтрации:
SELECT  
	dbo.FiltersAgg(RoomCount, 2) [RoomCount]
	,dbo.FiltersAgg(TotalArea, 2) [TotalArea] 
	,dbo.FiltersAgg(PriceRUR, 2) [PriceRUR] 
	,dbo.FiltersAgg(HouseMaterialTypeID, 1) [HouseMaterialTypeID]
	,dbo.FiltersAgg(HouseBuildingTypeID, 1) [HouseBuildingTypeID] 
	,dbo.FiltersAgg(LiftID, 1) [LiftID] 
	,dbo.FiltersAgg(HouseStoreys, 2) [HouseStoreys]
	
FROM Flats t
LEFT JOIN ObjectsPrices p ON p.ObjectID=t.FlatID


В результате получаем

RoomCount	TotalArea	PriceRUR	HouseMaterialTypeID	HouseBuildingTypeID	LiftID	HouseStoreys
NULL NULL NULL NULL NULL NULL NULL

Если добавим условие, например,

WHERE t.FlatID > 100000


то результат нормальный:

RoomCount	TotalArea	PriceRUR	HouseMaterialTypeID	HouseBuildingTypeID	LiftID	HouseStoreys
0=45 0=3919 0=145000000 5=25651,1=33814,4=6922,3=56577,6=61476,2=390,7=765,0=3878 1=129270,2=2695,9=10711,5=6544,11=20454,4=3600,6=1056,3=10221,7=1966,8=61,0=2895 4=1679,1=34865,2=142427,3=7349,5=247,0=2906 0=999

Я предположил, что дело в каком-либо переполнении и изменил int32 на int64 для значений и опять попробовал на всей таблице - в результате опять NULL без каких-либо ошибок по всем полям.

Затем я попробовал выяснить, что будет, если изменить запрос - закомментировать несоклько строк, например вот так:

SELECT  
	dbo.FiltersAgg(RoomCount, 2) [RoomCount]
	,dbo.FiltersAgg(TotalArea, 2) [TotalArea] 
	,dbo.FiltersAgg(PriceRUR, 2) [PriceRUR] 
--	,dbo.FiltersAgg(HouseMaterialTypeID, 1) [HouseMaterialTypeID]
--	,dbo.FiltersAgg(HouseBuildingTypeID, 1) [HouseBuildingTypeID] 
	,dbo.FiltersAgg(LiftID, 1) [LiftID] 
	,dbo.FiltersAgg(HouseStoreys, 2) [HouseStoreys]
	
FROM Flats t
LEFT JOIN ObjectsPrices p ON p.ObjectID=t.FlatID


И получил нормальный результат:
RoomCount	TotalArea	PriceRUR	LiftID	HouseStoreys
0=53 0=3919 0=489600000 2=153234,4=2791,1=53339,3=10785,5=305,0=2906 0=999

Попробовав еще несколько раз стало понятно, что если я убираю иногда одно, а иногда два поля, то остальные считаются без проблем. При этом неважно, какие из полей убрать - оставшиеся всегда считаются нормально - нет ошибки в каком-либо конкретном поле - судя по всему происходит какое-то переполнение только при просчете по всем полям на большом объеме данных (на малом объеме и со всеми полями все работает отлично).

Подскажите, пожалуйста, в какую сторону копать? Из-за чего может быть такая проблема?

Спасибо.
15 янв 15, 15:38    [17123008]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
Glory
Member

Откуда:
Сообщений: 104751
Начните с установки на своей сервер последних сервиспаков и патчей
15 янв 15, 15:41    [17123040]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
нафига для етого CLR ?
для вопрпосов по С# есть форум по языку
15 янв 15, 15:42    [17123047]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
Alexey30
Member

Откуда:
Сообщений: 163
Glory, сервер боевой, сейчас на нем стоит

Microsoft SQL Server 2008 (SP3) - 10.0.5520.0 (X64)
Jul 11 2014 16:11:50
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Знаю, что вышел четвертый сервис пак. Но не боязно ли его стаивть, пока он еще не стал обязательным обновлением?
Спасибо.


Maxx, так вопрос то не по c#. С C# проблем нету, разговор о поведении MS SQL.
15 янв 15, 16:03    [17123184]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Alexey30
Maxx, так вопрос то не по c#. С C# проблем нету, разговор о поведении MS SQL.

ну так покажите ошибку от MSSQL.... пока я только вижу,что ф-ция работает не стабильно
15 янв 15, 16:14    [17123255]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
Alexey30
Member

Откуда:
Сообщений: 163
Maxx, еслиб была, то показал бы.
Функция работает абсолютно стабильно сама по себе.
Неверно работает MS SQL с этой функцией, я вроде все подробно описал в первом сообщении.
15 янв 15, 17:19    [17123698]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8839
Alexey30,

Сделайте сборку UNSAFE, пишите в файл протокол, ищите ошибки.
15 янв 15, 17:25    [17123754]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4902
Alexey30

Подскажите, пожалуйста, в какую сторону копать? Из-за чего может быть такая проблема?

Спасибо.


Вот вы завели этот справочник

    private Dictionary<Int64, Int64> values;


А сколько его экземпляров будет создано в рамках запроса? Не будет один аггрегаг перетирать значение другого аггрегата?

Ещё Dictionary не threadsafe и у вас от этого не стоит никакой защиты. Попробуйте ConcurrentDictionary
15 янв 15, 18:59    [17124238]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
Alexes
Member

Откуда:
Сообщений: 1100
Alexey30
Попробовав еще несколько раз стало понятно, что если я убираю иногда одно, а иногда два поля, то остальные считаются без проблем. При этом неважно, какие из полей убрать - оставшиеся всегда считаются нормально - нет ошибки в каком-либо конкретном поле - судя по всему происходит какое-то переполнение только при просчете по всем полям на большом объеме данных (на малом объеме и со всеми полями все работает отлично).

Подскажите, пожалуйста, в какую сторону копать? Из-за чего может быть такая проблема?

Спасибо.

Возможно, из-за большого объема данных происходит выгрузка clr-домена. Посмотрите, нет ли в error log чего-нибудь по этому поводу?
15 янв 15, 19:24    [17124325]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31994
Alexey30
Попробовав еще несколько раз стало понятно, что если я убираю иногда одно, а иногда два поля, то остальные считаются без проблем. При этом неважно, какие из полей убрать - оставшиеся всегда считаются нормально - нет ошибки в каком-либо конкретном поле - судя по всему происходит какое-то переполнение только при просчете по всем полям на большом объеме данных (на малом объеме и со всеми полями все работает отлично).
Можно сравнить плану - может, там будут видны какие то отличия, типа распараллеливания?
15 янв 15, 19:29    [17124335]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
Alexey30
Member

Откуда:
Сообщений: 163
Glory
Начните с установки на своей сервер последних сервиспаков и патчей

Установил SP4, к сожалению, не помогло.

alexeyvg, a_voronin, Alexes
огромное спасибо за советы - кажется, двигаемся в правильном направлении.

alexeyvg
Можно сравнить плану - может, там будут видны какие то отличия, типа распараллеливания?


Действительно, есть разница в планах (они в аттаче). В неуспешном добавляется
Картинка с другого сайта.

Alexes
Возможно, из-за большого объема данных происходит выгрузка clr-домена. Посмотрите, нет ли в error log чего-нибудь по этому поводу?

Нет, выгрузка точно не происходит. Если бы была выгрузка, то ошибка, видимо, была бы видна сразу и в логе, но ни там ни там ничего нет.

a_voronin
Ещё Dictionary не threadsafe и у вас от этого не стоит никакой защиты. Попробуйте ConcurrentDictionary


Думаю, это ключевой момент. У меня MS SQL 2008, он, на сколько я понял, не позволяет использовать Framework 4.0 (в котором ConcurrentDictionary даже не требует UNSAFE).
Пробовал, как описано здесь http://stackoverflow.com/questions/2940241/making-dictionary-access-thread-safe , использовать lock, но мне так и не удалось задеплойить ассемблю (точнее, автоматически из Visual Studio не удалось, вручную скрипт запустил - получилось, но при выполнении - ошибка) - столкнулся с такой же проблемой ,как и у этого человека: http://stackoverflow.com/questions/16303557/multithreaded-caching-in-sql-clr

System.Security.HostProtectionException:
Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All
The demanded resources were: Synchronization, ExternalThreading


Деплойил под правами sysadmin, TRUSTWORTHY у базы включена. Оно вроде как деплойится, но при выполнении выплевывет ошибку.

Какие есть варианты? Может быть удастся каким-либо образом хотя бы сделать работу с lock?

Еще раз спасибо за уделенное время и внимание.

К сообщению приложен файл (Trash.zip - 10Kb) cкачать
16 янв 15, 16:47    [17129190]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4902
Alexey30
Какие есть варианты? Может быть удастся каким-либо образом хотя бы сделать работу с lock?


Оборачивайте все места обращения к справочникам lock {} но боюсь она матюгнётся опять

Надо нарыть что-то threadsafe
16 янв 15, 18:25    [17129844]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31994
Alexey30
Какие есть варианты?
Вот оно, реальное обоснование для перехода на SQL Server 2012+ :-)
16 янв 15, 20:54    [17130562]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
мимокрокодилдилдил
Guest
a_voronin
Alexey30
Подскажите, пожалуйста, в какую сторону копать? Из-за чего может быть такая проблема?

Спасибо.


Вот вы завели этот справочник

    private Dictionary<Int64, Int64> values;


А сколько его экземпляров будет создано в рамках запроса? Не будет один аггрегаг перетирать значение другого аггрегата?

Ещё Dictionary не threadsafe и у вас от этого не стоит никакой защиты. Попробуйте ConcurrentDictionary


не будет. структур FIltersAgg будет создано как минимум по количеству различных групп значений * на partitions hash таблицы в планах. в FIltersAgg.Merge собственно идет merge структур, которые принадлежат одной группе, но разным hash partitions...
16 янв 15, 21:00    [17130576]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31994
Alexey30
кажется, двигаемся в правильном направлении.
Мне кажется, локи тут ни при чём. У вас же не отклонения, у вас вообще NULL выводится. И ошибки не случаются.
Alexey30
Функция работает абсолютно стабильно сама по себе.
Вот я не уверен, что вы отладили всё?

Может, какие то NULL значения появляются в:
if (values.ContainsKey(q))
    values[q] += other.values[q];
?

Самое правильное, писать протокол, как уже выше предлагали.
16 янв 15, 21:52    [17130801]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
Antoshka
Member

Откуда:
Сообщений: 828
Alexey30,

Попробуй объявить класс вместо структуры
16 янв 15, 23:48    [17131113]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
Alexey30
Member

Откуда:
Сообщений: 163
a_voronin
Оборачивайте все места обращения к справочникам lock {} но боюсь она матюгнётся опять

Надо нарыть что-то threadsafe


Да, из-за lock будет та же самая проблема с развертыванием, поэтому, видимо, не вариант.

alexeyvg
Вот оно, реальное обоснование для перехода на SQL Server 2012+ :-)

Да вот честно говоря не хотелось бы этого делать - на этом сервере все работает без проблем, кроме вот этого единственного узкого места. Не знаю правда, если от перехода на более новый сервер производительность улучшится, то может действительно стоит задуматься?

alexeyvg
Вот я не уверен, что вы отладили всё?

Может, какие то NULL значения появляются в:
if (values.ContainsKey(q))
    values[q] += other.values[q];
?

Самое правильное, писать протокол, как уже выше предлагали.

Нет, там не может быть null, так как указан именно Int64 (он же long). Если бы был Nullable Long (long?), то такое могло бы быть.
Кроме того, как и говорил, если убрать некоторые поля (любые два поля) и использовать тот же набор данных, то результат по остальным (небурнанным) выводится верный, если вернуть убранные и убрать любые другие два, то результат опять будет правильным. Т.е. дело не в данных а в каком-то общем переполнении, что ли....

Antoshka
Alexey30,

Попробуй объявить класс вместо структуры


Пробовал, не помогло :(
19 янв 15, 18:26    [17139530]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
Alexey30
Member

Откуда:
Сообщений: 163
Господа, спасибо за поддержку, баг выловил.

Я добавил во все case условие по умолчанию
 default:
                    throw new Exception("Terminate: Unsupported FilterTypeID = " + filterTypeID);


Сообщения с ошибками сыпались из процедур Merge и Terminate.
В результате имеем сообщения такого вида:

1/19/2015 7:21:02 PM Merge: Unsupported FilterTypeID = 0

1/19/2015 7:21:02 PM Terminate: Unsupported FilterTypeID = 0


Дело было в том, что Merge вызывается раньше, чем Accumulate, поэтому filterTypeID не был инициализирован.
Добавил в Merge строку
filterTypeID = other.filterTypeID;

и все стало на свои места.

Однако, все-равно, иногда (когда именно и с чем это связано на данный момент непонятно), сыпятся сообщения из Terminate (из Merge уже нет).

1/19/2015 7:42:44 PM Terminate: Unsupported FilterTypeID = 0

По идее, есть только два места, где можно передать filterTypeID - Accumulate и Merge.
Как может получиться так, что в Terminate он равен нулю?

Спасибо!

Финальный код на данный момент:
[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined, //use clr serialization to serialize the intermediate result
    IsInvariantToNulls = true, //optimizer property
    IsInvariantToDuplicates = true, //optimizer property
    IsInvariantToOrder = true, //optimizer property
    IsNullIfEmpty = true,
    MaxByteSize=-1) //maximum size in bytes of persisted value
]
public class FiltersAgg  : IBinarySerialize
{
    
    private Dictionary<Int64, Int64> values;
    private Int64 _min, _max;
    private int filterTypeID;
    

    public void Init()
    {
        //LogException(new Exception("test"));
        try
        {
            values = new Dictionary<Int64, Int64>();
            _min = Int64.MaxValue;
            _max = Int64.MinValue;
        }
        catch (Exception ex)
        {
            LogException(ex);
        }
    }

    public void Accumulate(SqlInt64 value, SqlInt32 FilterTypeID)
    {
        try
        {
            filterTypeID = FilterTypeID.Value;
            switch (FilterTypeID.Value)
            {
                case 1:
                    if (value.IsNull)
                        return;


                    if (values.ContainsKey(value.Value))
                        values[value.Value]++;
                    else
                        values.Add(value.Value, 1);

                    break;
                case 2:
                    if (value.IsNull)
                        return;
                    if (value.Value < _min)
                        _min = value.Value;
                    if (value.Value > _max)
                        _max = value.Value;
                    break;
                default:
                    throw new Exception("Accumulate: Unsupported FilterTypeID = " + filterTypeID);
            }
        }
        catch (Exception ex)
        {
            LogException(ex);
        }

    }

    public void Merge(FiltersAgg other)
    {
        filterTypeID = other.filterTypeID;
        try
        {
            switch (filterTypeID)
            {
                case 1:

                {
                    foreach (var q in other.values.Keys)
                    {
   
                        if (values.ContainsKey(q))
                            values[q] += other.values[q];
                        else
                            values.Add(q, other.values[q]);
                    }
                }
                    break;
                case 2:
                    if (other._min < _min)
                        _min = other._min;
                    if (other._max > _max)
                        _max = other._max;

                    break;
                default:
                    throw new Exception("Merge: Unsupported FilterTypeID = " + filterTypeID);
            }
        }
        catch (Exception ex)
        {
            LogException(ex);
        }
    }

    public SqlString Terminate()
    {
        string res = null;
        SqlString sqlRes = null;
        try
        {
            switch (filterTypeID)
            {
                case 1:
                    res = JoinItems();
                    break;
                case 2:
                    res = _min + "=" + _max;
                    break;
                default:
                    throw new Exception("Terminate: Unsupported FilterTypeID = " + filterTypeID);
            }
            sqlRes = new SqlString(res);
        }
        catch (Exception ex)
        {
            LogException(ex);
        }
        return sqlRes;
    }

    private string JoinItems()
    {
        var sb = new StringBuilder();
        string res;
        try
        {

            {
                foreach (Int64 k in values.Keys)
                    sb.Append(k + "=" + values[k] + ",");

                if (values.Count > 0)
                    sb.Remove(sb.Length - 1, 1);
            }
            res = sb.ToString();
        }
        catch (Exception ex)
        {
            LogException(ex);
            res = "ERROR: " + ex.Message;
        }
        return res;
    }

    public void Read(BinaryReader r)
    {
        try
        {
            filterTypeID = r.ReadInt32();
            _min = r.ReadInt64();
            _max = r.ReadInt64();

            values = new Dictionary<Int64, Int64>();

            {
                int quan = r.ReadInt32();
                    for (int i = 0; i < quan; i++)
                        values.Add(r.ReadInt64(), r.ReadInt64());
            }
        }
        catch (Exception ex)
        {
            LogException(ex);
        }
    }

    public void Write(BinaryWriter w)
    {
        try
        {
            w.Write(filterTypeID);
            w.Write(_min);
            w.Write(_max);

            {
                w.Write(values.Count);
                foreach (Int64 k in values.Keys)
                {
                    w.Write(k);
                    w.Write(values[k]);
                }
            }
        }
        catch (Exception ex)
        {
            LogException(ex);
        }
    }

    void LogException(Exception ex)
    {
     
        try
        {
            using (var file = new StreamWriter(@"F:\log2.txt", true))
            {
                file.WriteLine(DateTime.Now + "\t" + ex.Message + "\r\n");
            }
        }
        catch { }
        


    }
}
19 янв 15, 19:49    [17139802]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
Alexey30
Member

Откуда:
Сообщений: 163
Выяснил.

Это происходит, когда нет данных для агрегирования. Т.е. когда запрос вызывается для пустого набора данных - соответственно не вызывается ни Accumulate, ни Merge.

Еще раз всем большое спасибо, надеюсь, эта тема кому-либо пригодится.
19 янв 15, 20:40    [17139997]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с CLR агрегирющей функцией  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Alexey30,

и канечно был виноват MSSQL
20 янв 15, 10:10    [17141398]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить