MDLS.ru

Новости

Проект "ЧПУ на Ардуино"

Обновлен дизайн сайта "Простой станок с ЧПУ на Ардуино" по адресу http://ecnc.ru

Подробнее

Разработан сайт "Свет православия"

Разработан сайт "Свет православия" http://svet-pravoslaviya.ru

Подробнее

Проект "ЧПУ на Ардуино"

Открытый проект "Простой станок с ЧПУ на Ардуино" перенесён на http://ecnc.ru

Подробнее

Личная страница "Частный переводчик"

Частный переводчик поможет провести переговоры, осуществит последовательный, синхронный, письменный переводы. http://tran.mdls.ru

Подробнее

Кэширование аналитических запросов в MS SQL Server 2000

Писать аналитические запросы – задача непростая. Ещё более сложно написать подобные запросы оптимально, чтобы они выполнялись за максимально короткое время. Для этого зачастую приходится пересматривать структуру “боевой” базы, создавать или меня индексы. Однако, со временем в базе появляются всё более сложные взаимосвязи, отреагировав на которые в аналитических запросах, вдруг обнаруживаешь, что они начали работать медленно.

            В этом случае стоит использовать кэширование общего результата. То есть, если у нас имеется запрос

select * from view_analitica where ind=1,

который выполняется 10 минут. То имеет смысл результат запроса

select * from view_analitica

вставить в промежуточную таблицу table_analitica. А затем выполнять запрос

select * from table_analitica where ind=1

Разумеется, таблицу table_analitica необходимо грамотно проиндексировать,  и мы это можем сделать так, как захотим: это наша таблица.

Далее с некоторой периодичностью, скажем один раз в час, мы будем обновлять данные в этой таблице. Теперь актуальность запросов будет зависеть от выбранного интервала обновления. Систематическое обновление можно обеспечить с помощью Job’а.

            Задача обновления данных предполагает два варианта решения:

  1. Полное блокирование промежуточной таблицы, затем удаление всех данных из неё и запись актуальной информации.
  2. Сравнение имеющейся информации в table_analitica со свежей, полученной из запроса, и последующее удаление, обновление или вставка соответствующих строк.

Следует добавить, что зачастую заведомо требуется обновление информации не во всей промежуточной таблице, а только за какой-то период, например последние 3 дня. Это ненамного усложняет задачу и при этом снижает нагрузку на сервер и сокращает время обновления данных в кэш-таблице.

Теперь ещё следует учесть следующее: в процессе изменения структуры исходной БД могут поменяться длины и типы каких-то полей. Хотелось бы, чтобы на подобные казусы система реагировала сама, без помощи программиста. И не забывала пересобирать все процедуры и функции, которые работают с нашей промежуточной таблицей.

В итоге родился следующий комплекс, состоящий из двух хранимых функций и двух процедур.

 

Процедура recompile_all обеспечивает автоматическую пересборку всех процедур.

 

CREATE PROCEDURE recompile_all AS

declare @table_name nvarchar(30), @i int

DECLARE @tab CURSOR

set @i=0

set @tab = CURSOR LOCAL STATIC FOR select [name] from sysobjects where status>=0 and type in('p','v')

 

OPEN @tab

FETCH NEXT FROM @tab INTO @table_name

WHILE @@FETCH_STATUS = 0

BEGIN

/*set @i=@i+1

print str(@i)+'    '+ @table_name */

 exec sp_recompile @table_name

 FETCH NEXT FROM @tab INTO @table_name

END

CLOSE @tab

DEALLOCATE @tab


GO

 

Функция get_type_definition используется для подстановки типов при формировании запроса на создание кэш-таблицы.

 

CREATE FUNCTION get_type_definition (@id_type int, @len int=0, @prec int=0, @scale int=0) 

RETURNS varchar(50) AS 

BEGIN

            declare @ret as varchar(50)

 

            set @ret=(select top 1 name from systypes (nolock) where xtype=@id_type and not name='sysname')

 

            select @ret=@ret +     case @ret        when 'decimal' then '('+ltrim(str(@prec))+','+ltrim(str(@scale))+')'

                                                                       when 'numeric'             then '('+ltrim(str(@prec))+','+ltrim(str(@scale))+')'

                                                                       when 'float'       then '('+ltrim(str(@len))+')'

                                                                       when 'char'       then '('+ltrim(str(@len))+')'

                                                                       when 'varchar' then '('+ltrim(str(@len))+')'

                                                                       when 'nchar'     then '('+ltrim(str(@len))+')'

                                                                       when 'nvarchar'            then '('+ltrim(str(@len))+')'

                                                                       when 'binary'    then '('+ltrim(str(@len))+')'

                                                                       when 'varbinary'           then '('+ltrim(str(@len))+')'

                                                                       else ''

                                               end


            return @ret

END

 

Функция get_type_isnull ипользуется при сравнениях значений полей. Она обеспечивает отждествление null и0.

 

CREATE FUNCTION get_type_isnull (@id_type int, @var_name varchar(50)) 

RETURNS varchar(50) AS 

BEGIN

            declare @ret as varchar(50)

            declare @tp as varchar(50)

 

            set @tp=(select top 1 name from systypes (nolock) where xtype=@id_type and not name='sysname')

 

            select @ret=                case @tp         when 'decimal' then 'isnull('+@var_name+',0)'

                                                                       when 'numeric'             then 'isnull('+@var_name+',0)'

                                                                       when 'float'       then 'isnull('+@var_name+',0)'

                                                                       when 'char'       then 'isnull('+@var_name+','''')'

                                                                       when 'varchar' then 'isnull('+@var_name+','''')'

                                                                       when 'nchar'     then 'isnull('+@var_name+','''')'

                                                                       when 'nvarchar'            then 'isnull('+@var_name+','''')'

                                                                       when 'bigint'     then 'isnull('+@var_name+',0)'

                                                                       when 'int'          then 'isnull('+@var_name+',0)'

                                                                       when 'smallint' then 'isnull('+@var_name+',0)'

                                                                       when 'tinyint'    then 'isnull('+@var_name+',0)'

                                                                       when 'bit'         then 'isnull('+@var_name+',0)'

                                                                       else @var_name

                                               end

 

            return @ret

END

 

Далее приведён код процедуры обновления данных в кэш-таблице. Эта процедура обеспечивает создание или модификацию кэш-таблицы. Первый параметр – строка с кэшируемым запросом; второй – название кэш-таблицы; третий – условие обновления.

 

CREATE PROCEDURE sp_reload_cache @query as nvarchar(4000), @tbl nvarchar(50), @reload_cond nvarchar(4000)=''

AS

 

set nocount on

 

declare @ct nvarchar(4000), @et nvarchar(4000), @at nvarchar(4000)

declare @strsq as nvarchar(4000)

 

set @strsq='if exists (select * from tempdb.dbo.sysobjects (nolock) where name=''##depot_tmp_'+rtrim(ltrim(str(@@spid)))+''') drop table ##depot_tmp_'+rtrim(ltrim(str(@@spid)))

exec (@strsq)

 

set @strsq='select * into ##depot_tmp_'+rtrim(ltrim(str(@@spid)))+' from('+@query+') a'

if (@reload_cond<>'') set @strsq=@strsq+' where ' + @reload_cond

exec (@strsq)

 

if (not exists (select * from sysobjects (nolock) where name=@tbl))

begin

            --Если кэш-таблицы нет, то создаём её. ВНИМАНИЕ! Индексы создавать в ручную!

            set @ct=''

            select  @ct=@ct+case when @ct='' then '' else ',' end +name+' '+dbo.get_type_definition(xtype,length,xprec,xscale)

                        from tempdb..syscolumns (nolock)

                        where id = (select id from tempdb..sysobjects (nolock) where name='##depot_tmp_'+rtrim(ltrim(str(@@spid))))

            set @ct='create table ' +@tbl+ '('+@ct+')'

            exec (@ct)

end

else

begin

            --Проверяем, изменились ли поля в кэше - как правило, происходит при изменении запроса

            DECLARE alter_cursor CURSOR FOR

            select case       when a.name is null then 'ALTER TABLE ' +@tbl+ ' ADD '+b.name+' '+dbo.get_type_definition(b.xtype,b.length,b.xprec,b.xscale)

                                   when b.name is null then 'ALTER TABLE ' +@tbl+ ' DROP COLUMN '+a.name

                                   when a.xtype=b.xtype and a.length<b.length then 'ALTER TABLE ' +@tbl+ ' ALTER COLUMN '+b.name+' '+dbo.get_type_definition(b.xtype,b.length,b.xprec,b.xscale)

                                   else 'ALTER TABLE ' +@tbl+ ' DROP COLUMN '+a.name+';ALTER TABLE ' +@tbl+ ' ADD '+b.name+' '+dbo.get_type_definition(b.xtype,b.length,b.xprec,b.xscale)

            end as alter_cmd from

            (select * from syscolumns (nolock) where id = (select id from sysobjects (nolock) where name=@tbl)) a

                        full join

            (select * from tempdb..syscolumns (nolock) where id = (select id from tempdb..sysobjects (nolock) where name='##depot_tmp_'+rtrim(ltrim(str(@@spid))))) b

                        on a.name=b.name

            where isnull(a.xtype,0)<>isnull(b.xtype,0) or isnull(a.length,0)<isnull(b.length,0)           --если поменялся тип поля или его длина увеличилась

 

            open alter_cursor

            fetch next from alter_cursor

            into @ct

            while @@FETCH_STATUS = 0

            begin

                        print 'Изменение структуры таблицы: '+@ct

                        exec (@ct)       --собственно, это изменения и есть         

                        fetch next from alter_cursor

                        into @ct

            end

            close alter_cursor

            deallocate alter_cursor

            exec recompile_all

end


--Формируем списки полей таблицы для последующих запросов

set @ct=''

set @et=''

set @at=''

select  @ct=@ct + case when @ct='' then '' else ',' end + name,                                                       --просто список полей

            @at=@at + case when @at='' then '' else ',' end + dbo.get_type_isnull(xtype, 'a.'+name),      --список с isnull'ами

            @et=@et + case when @et='' then '' else ' and ' end +

                        dbo.get_type_isnull(xtype, 'a.'+name)+'='+dbo.get_type_isnull(xtype, 'b.'+name)        --список с isnull'ами для where

from syscolumns (nolock) where id = (select id from sysobjects (nolock) where name=@tbl)

 

--загружаем в #tmp общие строки для старых данных и новых (то, что обновлять не будем)

set @strsq='select top 0 * into ##tmp_'+rtrim(ltrim(str(@@spid)))+' from ##depot_tmp_'+rtrim(ltrim(str(@@spid)))

exec (@strsq)

 

if (@reload_cond='')

            set @strsq='insert into ##tmp_'+rtrim(ltrim(str(@@spid)))+'('+@ct+') (select '+@at+' from '+@tbl+' a (nolock) inner join ##depot_tmp_'+rtrim(ltrim(str(@@spid)))+' b on '+@et+')'

else

            set @strsq='insert into ##tmp_'+rtrim(ltrim(str(@@spid)))+'('+@ct+') (select '+@at+' from (select * from '+@tbl+' (nolock) where '+@reload_cond+') a inner join ##depot_tmp_'+rtrim(ltrim(str(@@spid)))+' b on '+@et+')'

 

exec (@strsq)


--удаляем данные, которые требуют обновления из кэш-таблицы

set @strsq='delete '+@tbl+' from '+@tbl+' a where not exists (select * from ##tmp_'+rtrim(ltrim(str(@@spid)))+' b where '+@et+')'

if (@reload_cond<>'')

            set @strsq=@strsq+' and '+@reload_cond

exec (@strsq)


--вставляем новые данные в кэш-таблицу

set @strsq='insert into '+@tbl+'('+@ct+') (select '+@ct+' from ##depot_tmp_'+rtrim(ltrim(str(@@spid)))+' a where not exists (select * from ##tmp_'+rtrim(ltrim(str(@@spid)))+' b where '+@et+'))'

exec (@strsq)

set @strsq='drop table ##tmp_'+rtrim(ltrim(str(@@spid)))

exec (@strsq)

 

set @strsq='drop table ##depot_tmp_'+rtrim(ltrim(str(@@spid)))

exec (@strsq)

GO

 

Обратите внимание, что используются глобальные временные таблицы со SPID процесса в качестве постфикса. Это позволяет формируемым запросом создавать таблицы, доступные из других формируемых запросов. И при этом исключается пересечение названий глобальных временных таблиц, созданных параллельно запущенными процедурами обновления данных в кэш-таблицах.

 

В итоге запрос на обновление данных в кэш-таблице с индексами 1, 2, 3, 4 будет выглядеть следующим образом:

 

sp_reload_cache ‘select * from view_analitica’, ‘table_analitica’, ‘ind in (1,2,3,4)’

 

Коротаевский Андрей