Обновлен дизайн сайта "Простой станок с ЧПУ на Ардуино" по адресу http://ecnc.ru
Разработан сайт "Свет православия" http://svet-pravoslaviya.ru
Открытый проект "Простой станок с ЧПУ на Ардуино" перенесён на http://ecnc.ru
Частный переводчик поможет провести переговоры, осуществит последовательный, синхронный, письменный переводы. http://tran.mdls.ru
Писать аналитические запросы – задача непростая. Ещё более сложно написать подобные запросы оптимально, чтобы они выполнялись за максимально короткое время. Для этого зачастую приходится пересматривать структуру “боевой” базы, создавать или меня индексы. Однако, со временем в базе появляются всё более сложные взаимосвязи, отреагировав на которые в аналитических запросах, вдруг обнаруживаешь, что они начали работать медленно.
В этом случае стоит использовать кэширование общего результата. То есть, если у нас имеется запрос
select * from view_analitica where ind=1,
который выполняется 10 минут. То имеет смысл результат запроса
select * from view_analitica
вставить в промежуточную таблицу table_analitica. А затем выполнять запрос
select * from table_analitica where ind=1
Разумеется, таблицу table_analitica необходимо грамотно проиндексировать, и мы это можем сделать так, как захотим: это наша таблица.
Далее с некоторой периодичностью, скажем один раз в час, мы будем обновлять данные в этой таблице. Теперь актуальность запросов будет зависеть от выбранного интервала обновления. Систематическое обновление можно обеспечить с помощью Job’а.
Задача обновления данных предполагает два варианта решения:
Следует добавить, что зачастую заведомо требуется обновление информации не во всей промежуточной таблице, а только за какой-то период, например последние 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)’
Коротаевский Андрей