Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Ручное кэширование справочника в NET.CLR  [new]
Alexander Us
Member

Откуда:
Сообщений: 1153
При работе с DataWarehouse столкнулся с такой проблемой:
--есть табличка с текстами (справочник):
CREATE TABLE TabTexts(TextID int primary key, Text varchar(200)); -- тут около двухтысяч строк
--и таблица с данными:
CREATE TABLE DATA(DataID int identity(1,1) primary key, TextID001 int,TextID002 int,TextID003 int, ...) -- около 15 мил. строк 
Обе таблицы пополняются/изменяются раз в день.
Понадобилось (быстро!) выводить тексты из таблицы с данными.

Первым делом поробовал join/subquery:
SELECT
 D.DataID
,T001.Text as T001
,T002.Text as T002
,T003.Text as T003
--,..... и т.д. примерно от 40 до 200 столбцов
FROM DATA D
LEFT JOIN TabTexts T001 on D.TextID001 =T001.TextID  
LEFT JOIN TabTexts T002 on D.TextID002 =T002.TextID 
LEFT JOIN TabTexts T003 on D.TextID003 =T003.TextID 
--,.....

Оказалось что:
- медленно
- при увеличении количества джоинов время выполнения начинает расти экспоненциально
(на моём кампе эффект наступает при где то при переходе с 10 на 20 столбцов/джоинов).

Затем поробовал справочник сделать memory optimized: выигрыша в производительности не было.
(SQL Server: 2014 - 12.0.2254.0 (X64), Developer Edition (64-bit); OS: Win7 Pro)



--------------------------------------------------------------------------------------------------------------------------------------------
После мытарств пришёл к решению кэшить справочник в текущем AppDomain и вызывать CLR функцией:
--зарузка справочника в кэш
select dbo.fn_LoadTexts ('List1','SELECT TextID, Text from TabTexts order by TextID') 
--получение развёрнутых данных
select 
 D.DataID
,dbo.fn_GetText('List1',D.TextID001) as T001
,dbo.fn_GetText('List1',D.TextID002) as T002
,dbo.fn_GetText('List1',D.TextID003) as T003
--,....
FROM DATA D
Время выполнения ускорилось на порядок.

При этом решении остаётся неясным несколько вопросов, кто в курсе прошу просветить:

- как долго живёт AppDomain?
В моём контексте я предполагаю, что изменив данные в таблице TabTexts
мне будет достаточно единожды актуализировать закэшеные данные и,
если не было рестарта сервера или базы данные в кэше не должны пропасть.
Верно ли предположение?

- есть ли другие подводные камни/ противопоказания против такого решения?

Всем спасибо.


--------------------------------------------------------------------------------------------------------------------------------------------
Исходники:
(синхронизация потоков не реализована. т.к. у меня читает/пишет одон клиент )
Public Class MemoryText

    Private Shared ReadOnly mData As New MemoryData

    <SqlFunction(DataAccess:=DataAccessKind.None, IsDeterministic:=False, IsPrecise:=False, Name:="fn_GetText")> _
    Public Shared Function GetText(CollectionName As SqlString, KonastanteID As SqlInt32) As String
        If KonastanteID.IsNull Then
            Return Nothing
        Else
            Return mData.GetText(CollectionName.Value, KonastanteID.Value)
        End If
    End Function

    <SqlFunction(DataAccess:=DataAccessKind.None, IsDeterministic:=True, IsPrecise:=False, Name:="fn_ClearTextCollection")> _
    Public Shared Function Clear(CollectionName As SqlString) As Integer
        mData.Clear(CollectionName.Value)
        Return 0
    End Function

    <SqlFunction(DataAccess:=DataAccessKind.Read, IsDeterministic:=False, IsPrecise:=False, Name:="fn_LoadTextCollection")> _
    Public Shared Function Load(CollectionName As SqlString, SqlText As SqlString) As Integer
        Return mData.Load(CollectionName.Value, SqlText.Value)
    End Function

End Class


Friend Class MemoryData

    Private mDict As New Dictionary(Of String, Dictionary(Of Integer, String))

    Public Function Clear(CoollectionName As String) As Integer
        If CoollectionName = "ALL" Then
            Dim ret = mDict.Count
            mDict.Clear()
            Return ret
        ElseIf mDict.ContainsKey(CoollectionName) Then
            mDict(CoollectionName).Clear()
            Return 1
        Else
            Return 0
        End If
    End Function

    Public Function Load(CollectionName As String, SQLText As String) As Integer
        If CollectionName = "ALL" Then Throw New Exception("CollectionName ALL reserved")
        If Me.mDict.ContainsKey(CollectionName) Then Me.mDict.Remove(CollectionName)

        Dim curDict = New Dictionary(Of Integer, String)
        Me.mDict.Add(CollectionName, curDict)

        Using cn = New SqlConnection("context connection=true")
            cn.Open()

            Using cm = New SqlCommand(SQLText, cn)
                Using r = cm.ExecuteReader()
                    While r.Read
                        curDict.Add(r.GetInt32(0), r.GetString(1))
                    End While
                End Using
            End Using
        End Using

        Return curDict.Count
    End Function

    Public Function GetText(CollectionName As String, TextID As Integer) As String
        Dim curDict As Dictionary(Of Integer, String)

        If Not mDict.TryGetValue(CollectionName, curDict) Then Throw New Exception("Collection was not loaded")

        If curDict.ContainsKey(TextID) Then
            Return curDict(TextID)
        Else
            Return Nothing
        End If
    End Function

End Class


CREATE ASSEMBLY SqlUtils3
FROM 'C:\xxxxx.dll' 
WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION fn_GetText(@CollectionName as nvarchar(100), @TextID as int)
RETURNS nvarchar(500)
AS EXTERNAL NAME SqlUtils3.[xxxxx.MemoryText].[GetText];
GO

CREATE FUNCTION fn_LoadTexts(@CollectionName as nvarchar(100), @SqlText as nvarchar(4000))
RETURNS int
AS EXTERNAL NAME SqlUtils3.[xxxxx.MemoryText].[Load];
GO

CREATE FUNCTION fn_ClearTexts(@CollectionName as nvarchar(100))
RETURNS int
AS EXTERNAL NAME SqlUtils3.[xxxxx.MemoryText].[Clear];
GO
18 май 15, 15:42    [17656314]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Alexander Us,

Не пробовали план поизучать на отсутствие нужных индексов?
Зачем вам вообще скуль если вы данные храните в бейсике?
18 май 15, 16:05    [17656511]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Alexander Us
Member

Откуда:
Сообщений: 1153
WarAnt
Не пробовали план поизучать на отсутствие нужных индексов?
Зачем вам вообще скуль если вы данные храните в бейсике?


- Пробовал, с индексами проблем не заметил. Да я и T-SQL скрипт выше привёл. Каких индексов там по Вашему не хватает?
- Да, увы, данные справочника запихал в бейсик. и избавился таким образом от многих десятков джоинов: всё ради времени выполнения.
18 май 15, 16:27    [17656653]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Glory
Member

Откуда:
Сообщений: 104760
Alexander Us
- Пробовал,с индексами проблем не заметил.

И что вы вообще увидели в этом плане ?

Alexander Us
- Да, увы, данные справочника запихал в бейсик. и избавился таким образом от многих десятков джоинов: всё ради времени выполнения.

Т.е. в вашем плане именно джойны брали больше всего ресурсов ?
18 май 15, 16:30    [17656674]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Alexander Us
WarAnt
Не пробовали план поизучать на отсутствие нужных индексов?
Зачем вам вообще скуль если вы данные храните в бейсике?


- Пробовал, с индексами проблем не заметил. Да я и T-SQL скрипт выше привёл. Каких индексов там по Вашему не хватает?
- Да, увы, данные справочника запихал в бейсик. и избавился таким образом от многих десятков джоинов: всё ради времени выполнения.


Вы вот этот обрезок скриптом называете?
SELECT
 D.DataID
,T001.Text as T001
,T002.Text as T002
,T003.Text as T003
--,..... и т.д. примерно от 40 до 200 столбцов
FROM DATA D
LEFT JOIN TabTexts T001 on D.TextID001 =T001.TextID  
LEFT JOIN TabTexts T002 on D.TextID002 =T002.TextID 
LEFT JOIN TabTexts T003 on D.TextID003 =T003.TextID 
--,.....


По моему всех не хватает, так как кроме кластерного в таблице tabtexts других то и нету.
А точнее, как всегда, говорит план.
18 май 15, 16:33    [17656697]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Alexander Us
Member

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

- План приложен.
- Это не обрезок, это селект на первые три столбца.

К сообщению приложен файл (plan1.sqlplan - 86Kb) cкачать
18 май 15, 16:52    [17656862]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Alexander Us,

Судя по планы, вы выгружаете все ваши 15 млн. записей сразу и хотите, что бы это работало быстро, подход по моему неправильный.
и кстати статистика то не в курсе, что у вас там 15 млн записей.
Зачем каждый раз запрашивать всю базу?
Вобщем тут проблема, как мне видится, не запросе, а в архитектуре.
18 май 15, 17:03    [17656945]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Alexander Us,

По поводу вашего вопроса о clr, ждите ошибок на сервере типа out of memory и необходимости ребута сервиса. Это практически неизбежно.
18 май 15, 17:05    [17656956]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Alexander Us
Member

Откуда:
Сообщений: 1153
WarAnt
Alexander Us,

Судя по планы, вы выгружаете все ваши 15 млн. записей сразу и хотите, что бы это работало быстро, подход по моему неправильный.
и кстати статистика то не в курсе, что у вас там 15 млн записей.
Зачем каждый раз запрашивать всю базу?
Вобщем тут проблема, как мне видится, не запросе, а в архитектуре.


- архитектура не обсуждается, по крайней мере до тех пор, есть возможность работать с существующей.

Да гружу все 15 миллионов, и хочу при этом БЫСТРО развернуть несколко десятков столбцов.

Тест на 5 столбцах:
join/subquery: 21 сек
net.clr: 9сек

Тест на 10 столбцах:
join/subquery: 42 сек
net.clr: 18 сек

Тест на 20 столбцах:
join/subquery: 210сек
net.clr: 33сек

это при: i5, SSD, 24 GB Ram for SQL Server
18 май 15, 17:22    [17657079]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Alexander Us
Member

Откуда:
Сообщений: 1153
WarAnt
Alexander Us,

По поводу вашего вопроса о clr, ждите ошибок на сервере типа out of memory и необходимости ребута сервиса. Это практически неизбежно.


Почему?
Ведь справочник грузится в память один раз.
18 май 15, 17:24    [17657089]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37143
Alexander Us
WarAnt
Alexander Us,

По поводу вашего вопроса о clr, ждите ошибок на сервере типа out of memory и необходимости ребута сервиса. Это практически неизбежно.


Почему?
Ведь справочник грузится в память один раз.
При memory pressure сборки выгружаются чуть ли не первым делом.
18 май 15, 17:26    [17657100]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Alexander Us
Member

Откуда:
Сообщений: 1153
Гавриленко Сергей Алексеевич
При memory pressure сборки выгружаются чуть ли не первым делом.


К сожалению я не в курсе насчёт "memory pressure".

Правильно ли я понимаю, Вы имеете ввиду, что при недостатке памяти сервер выгружает неиспользуемые сборки?
В таком случае вряд ли стоит ждать "out of memory" как указал коллега WarAnt.

Рассуждая логически, можно предположить, что если в одном скрипте(сессии) загрузить/обновить справочник и затем сразу его использовать, то при "memory pressure" сервер не станет выгружать сборку - т.к. она находится в использовании.

Правильно ли предположение?

select dbo.fn_LoadTexts ('List1','SELECT TextID, Text from TabTexts order by TextID');
select 
 D.DataID
,dbo.fn_GetText('List1',D.TextID001) as T001
,dbo.fn_GetText('List1',D.TextID002) as T002
,dbo.fn_GetText('List1',D.TextID003) as T003
FROM DATA D
18 май 15, 17:42    [17657177]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Alexander Us
Member

Откуда:
Сообщений: 1153
Гавриленко Сергей Алексеевич,

Сергей Алексеевич, а правильно ли предположение, что если нет memory pressure, то сборка остаётся в памяти сколь угодно долго?

*вариант с рестартом не рассматривается
18 май 15, 17:58    [17657256]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37143
Alexander Us
Гавриленко Сергей Алексеевич
При memory pressure сборки выгружаются чуть ли не первым делом.


К сожалению я не в курсе насчёт "memory pressure".

Правильно ли я понимаю, Вы имеете ввиду, что при недостатке памяти сервер выгружает неиспользуемые сборки?
В таком случае вряд ли стоит ждать "out of memory" как указал коллега WarAnt.

Рассуждая логически, можно предположить, что если в одном скрипте(сессии) загрузить/обновить справочник и затем сразу его использовать, то при "memory pressure" сервер не станет выгружать сборку - т.к. она находится в использовании.

Правильно ли предположение?

select dbo.fn_LoadTexts ('List1','SELECT TextID, Text from TabTexts order by TextID');
select 
 D.DataID
,dbo.fn_GetText('List1',D.TextID001) as T001
,dbo.fn_GetText('List1',D.TextID002) as T002
,dbo.fn_GetText('List1',D.TextID003) as T003
FROM DATA D
Сервер как миленький станет выгружать сборки, а при выполнении запроса вы получите обломинго: "AppDomain %i (%.*ls) is marked for unload due to memory pressure".
18 май 15, 18:02    [17657270]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Alexander Us
Member

Откуда:
Сообщений: 1153
Гавриленко Сергей Алексеевич
...как миленький станет выгружать сборки, а при выполнении запроса вы получите обломинго: "AppDomain %i (%.*ls) is marked for unload due to memory pressure".


.. как миленький станет выгружать сборки ..
В случае чего: переполнения памяти или сам по себе, когда левая нога захочет?

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

Следовательно, используемое мной кэширование спавочника в сборке не будет принципиально худшим решением чем любая другая "легальная" NET.CLR функция. Или я что то недопонял?
18 май 15, 18:11    [17657320]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8316
Alexander Us,

чего то Вы не договариваете, не может этот запрос долго работать. Скорее всего, у Вас не оптимальный план выполнения.
18 май 15, 18:23    [17657380]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Владислав Колосов
Alexander Us,

чего то Вы не договариваете, не может этот запрос долго работать. Скорее всего, у Вас не оптимальный план выполнения.


у него статистика показывает 1/15 от реального объема данных, чего хотите от плана:)
18 май 15, 18:28    [17657412]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Alexander Us
Следовательно, используемое мной кэширование спавочника в сборке не будет принципиально худшим решением чем любая другая "легальная" NET.CLR функция. Или я что то недопонял?



Никто и не говорит что оно худшее, все решения имеют свои -+, про минусы данного решения Вам рассказали, решать только Вам что использовать.
18 май 15, 18:31    [17657425]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Alexander Us
Member

Откуда:
Сообщений: 1153
Владислав Колосов, план приложен. Смотрите выше.

При выдаче небольшого количества столбцов 1-5 всё ок, работает быстро.
При увеличении соличества столбцов время выполнения растёт экспоненциально. (join/subquery)
При переходе на функцию с кэшированием справочника в сборке, время выполнения растёт линейно.

По завершении разработки мне понадобится выдавать от 40 до примерно 100 столбцов т.е. с случае с join это до 100 left join.

Главное, что меня заботит, нет ли в решении с "кэшированием справочника в сборке" какой нибудь принципиальной засады/граблей.
18 май 15, 18:37    [17657455]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37143
Если вам так важна скорость, просто материализируйте вашу выборку. По скорости уделает все ваши варианты.

Сообщение было отредактировано: 18 май 15, 18:41
18 май 15, 18:40    [17657471]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Alexander Us
Member

Откуда:
Сообщений: 1153
WarAnt
...Никто и не говорит что оно худшее, все решения имеют свои -+, про минусы данного решения Вам рассказали, решать только Вам что использовать.
WarAnt, под "принципиально худшим решением" я имею ввиду решение, не могущее работать в принципе там, где работают "легальные" NET.CLR функции. Т.к. я использовал "нелегальный" подход - хранение данных в AppDomain то меня беспокоят скрытые грабли.

Например, при каком либо событии бы непредсказуемо опуспошался бы мой кэш а легальные функции NET.CLR продолжали бы себе спокойненько работать.
18 май 15, 18:50    [17657518]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Alexander Us
Member

Откуда:
Сообщений: 1153
Гавриленко Сергей Алексеевич
Если вам так важна скорость, просто материализируйте вашу выборку. По скорости уделает все ваши варианты.


Сергей Алексеевич,

1) главное: там left OUTER jojn, а это низя в indexed view

2) пересоздание/актуазизация indexed view тоже займёт время, а оно растёт экспоненциально количеству столбцов
18 май 15, 18:57    [17657549]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37143
Alexander Us
Гавриленко Сергей Алексеевич
Если вам так важна скорость, просто материализируйте вашу выборку. По скорости уделает все ваши варианты.


Сергей Алексеевич,

1) главное: там left OUTER jojn, а это низя в indexed view

2) пересоздание/актуазизация indexed view тоже займёт время, а оно растёт экспоненциально количеству столбцов

1. Indexed View -- это не единственный способ материализации
2. Изначально залить придется один раз, потом просто поддерживать.
18 май 15, 18:59    [17657568]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8316
Любопытно, CLR позволяет формировать таблицу за один проход и поиск осуществляет в массиве. Мы как-то пробовали использовать подобное, но большие значения индексов массива давали довольно-таки невысокую производительность.
18 май 15, 19:12    [17657629]     Ответить | Цитировать Сообщить модератору
 Re: Ручное кэширование справочника в NET.CLR  [new]
Alexander Us
Member

Откуда:
Сообщений: 1153
WarAnt
...у него статистика показывает 1/15 от реального объема данных, чего хотите от плана:)


Пожалуйста: план для реального объёма данных с выдачей 10 столбцов.

Время выполнения:

Джоин: 44 сек
Сборка: 18 сек

К сообщению приложен файл (Plans 2 3.zip - 16Kb) cкачать
18 май 15, 19:16    [17657644]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить