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

Откуда: Оренбург
Сообщений: 12
Есть таблица move_table, в которую записываются операции по карте клиента (взносы, изъятия, оплаты и т.д.), есть таблица cards_table, где хранится список карт клиента, и таблица rep_table, это временная таблица, куда при формировании отчета складываются данные для дальнейшей обработки (просмотра или отправки на печать). Изначально база работала под FireBird. Сейчас пытаемся перенести это все на MS SQL. Структура создана полностью идентична, данные перенесены все полностью один к одному. Перенесена встроенная процедура формирования отчета. При одинаковых условиях процедура на FireBird выполняется в 2-2,5 раза быстрее! Возможно, что из-за незнания MS SQL процедуру перенесли не верно и можно что-то переделать. Если не тяжело ткните пальцем где не так. FireBird версии 2.1, MS SQL Server 2005 - 9.00.3042.00 (Intel X86) Express Edition on Windows NT 5.1
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[rep_summ](
    @SDATE datetime,
    @FDATE datetime,
    @ID_USER integer,
    @ALL_CARDS smallint,
    @NOW_SMEN smallint,
    @DEL_NULL smallint,
    @MIN_SUMM numeric(12,2))

AS
BEGIN
	declare @MDATE datetime,
	@TMP_DATE datetime,
	@MTIME datetime,
	@TYPEV integer,
	@NCARD integer,
	@TYPEO integer,
	@RCOUNT integer,
	@SUMMA float,
	@OST_S float,
	@OST_E float,
	@OWNER varchar(50),
	@TYPEV2 varchar(50),
	@NCARD_TEMP integer,
	@TOCARD integer;
	begin transaction;

	DELETE FROM REP_TABLE where REP_TABLE.id_user=@ID_USER;

	declare tmp_rep cursor for select ncard, summa, typeo, typev, logic_date, mtime, tocard from move_table WHERE (move_table.logic_date between @SDATE and @FDATE);

	open tmp_rep;
	fetch next from tmp_rep into @ncard,@summa,@typeo,@typev,@mdate,@mtime,@tocard;
	while @@FETCH_STATUS=0
	begin
		set @owner=(select cards_table.owner  from cards_table where cards_table.number=@ncard);
		if @typev is null
			set @typev=0;
		set @typev2='';
		if @typev<>0
			set @typev2=(select typev from valute_table where key1=@typev);
		/*	if exists (select number from rep_table where number=@ncard and id_user=@id_user)
				begin
					set @rcount=1
				end
			else
				begin
					set @rcount=0
				end			*/
		set @rcount=(select count(number) from rep_table where number=@ncard and id_user=@id_user);
		if @rcount=0
  		begin
				set @TMP_DATE=@sdate-1;
				set @ost_s=0;
				set @ost_e=0;
				execute get_ost @TMP_DATE,@ncard,@ost_s output;
				execute get_ost @fdate,@ncard,@ost_s output;
				insert into rep_table (number,fdate,mtime,typeo_,typev_,owner,typev,sost,fost,vznos,izyat,plat,skidki,vhod,id_user,move) 
					values (@ncard,@mdate,@mtime,@typeo,@typev,@owner,@typev2,@ost_s,@ost_e,0,0,0,0,0,@id_user,0);     
			end;
			if @typeo=1
				update rep_table set vznos=vznos+@summa where number=@ncard and id_user=@id_user;
			if @typeo=2 
				update rep_table set izyat=izyat+@summa where number=@ncard and id_user=@id_user;
			if @typeo=3 
				if @tocard is null 
					update rep_table set move=move+@summa where number=@ncard and id_user=@id_user;
				else
					update rep_table set move=move-@summa where number=@ncard and id_user=@id_user;
			if @typeo=4 
				update rep_table set plat=plat+@summa where number=@ncard and id_user=@id_user;
			if @typeo=5 
				update rep_table set skidki=skidki+@summa where number=@ncard and id_user=@id_user;
			if @typeo=6 
				update rep_table set vhod=vhod+@summa where number=@ncard and id_user=@id_user;

		fetch next from tmp_rep into @ncard,@summa,@typeo,@typeV,@mdate,@mtime,@tocard;
	end

CLOSE tmp_rep;
DEALLOCATE tmp_rep;

if @all_cards=1
	begin		
		declare	tmp_rep cursor for select number from cards_table;
		open tmp_rep;
		fetch next from tmp_rep into @ncard_temp;
		while @@FETCH_STATUS=0
		begin			
			/*if exists (select number from rep_table where number=@ncard_temp and id_user=@id_user)
				begin
					set @rcount=1
				end
			else
				begin
					set @rcount=0
				end	*/		
	

			set @rcount=(select count(number) from rep_table where number=@ncard_temp and id_user=@id_user);			
			if @rcount=0
				begin					
					set @owner=(select cards_table.owner  from cards_table where cards_table.number=@ncard);
					set @TMP_DATE=@sdate-1;
					set @ost_s=0;
					set @ost_e=0;
					execute get_ost @TMP_DATE,@ncard,@ost_s output;
					execute get_ost @fdate,@ncard,@ost_s output;
					insert into rep_table (number,owner,sost,fost,id_user) values (@ncard_temp,@owner,@ost_s,@ost_e,@id_user);					
				end;
			fetch next from tmp_rep into @ncard_temp;
		end
		CLOSE tmp_rep;
		DEALLOCATE tmp_rep;
	end

END

28 дек 09, 07:11    [8125176]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
заменить процедуру get_ost функцией, убрать курсор
28 дек 09, 08:58    [8125302]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
Березан Алексей
Member

Откуда: Оренбург
Сообщений: 12
Зайцев Фёдор
заменить процедуру get_ost функцией, убрать курсор


Вычисление процедуры занимает сотые доли секунды и сопоставимо по скорости с FireBird. Хотя можно попробовать.
А как без курсора перебрать все строки полученного запроса? Какой для этого есть механизм?
28 дек 09, 09:34    [8125446]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
Березан Алексей

А как без курсора перебрать все строки полученного запроса? Какой для этого есть механизм?
Вам же написали: заменить процедуру get_ost функцией, с нарастающей суммой правда сложнее, но тоже можно наверное
а вообще надо такую структуру иметь чтобы получать остатки запросом
28 дек 09, 10:41    [8125747]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
Березан Алексей
Зайцев Фёдор
заменить процедуру get_ost функцией, убрать курсор


Вычисление процедуры занимает сотые доли секунды и сопоставимо по скорости с FireBird. Хотя можно попробовать.
А как без курсора перебрать все строки полученного запроса? Какой для этого есть механизм?


тут update в цикле можно заменить на update... from...
мешает только execute
28 дек 09, 11:12    [8125902]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
давайте я это в форум по MS SQL перекину, там Вам больше советов дадут
тут проблема в том что Вы с FireBird-ским мышлением работаете с MS SQL
28 дек 09, 12:37    [8126438]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
замените
select @c = count() from ....
if @c = 0  begin
...
end
на
if not exists(select * from ...) begin
...
end
-------------------------
There’s no silver bullet!
28 дек 09, 13:16    [8126761]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
кстати можно писать вместо set @rcount=(select count(number) from rep_table where number=@ncard) так: select @rcount=count(*) from rep_table where number=@ncard Модератор: перенесено из сравнение СУБД
Модератор: Тема перенесена из форума "Сравнение СУБД".


Сообщение было отредактировано: 28 дек 09, 13:57
28 дек 09, 13:56    [8127081]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
Березан Алексей
Member

Откуда: Оренбург
Сообщений: 12
locky
замените
select @c = count() from ....
if @c = 0  begin
...
end
на
if not exists(select * from ...) begin
...
end
-------------------------
There’s no silver bullet!


Вы наверное не заметили, там чуть выше есть exists в закомментаренном виде. Я пробовал, по скорости это ничего не дает. Когда поле number не было индексировано, выполнялось гораздо дольше, после индексации в разы быстрее и разницы нет.
28 дек 09, 14:08    [8127167]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
Березан Алексей
Member

Откуда: Оренбург
Сообщений: 12
SergSuper
давайте я это в форум по MS SQL перекину, там Вам больше советов дадут
тут проблема в том что Вы с FireBird-ским мышлением работаете с MS SQL

Полностью с Вами согласен. Вот поэтому и хотел бы услышать от умных людей в какую сторону мышление направить. Потому как кроме курсора аналогичного решения не нашел, как сделать по другому не могу так же найти. А менять всю полностью структуру базы, что бы это обойти и работать только на запросах, слишком на данный момент сложно. Да и все же мучает вопрос почему же при равных условиях MS SQL на столько медленней.
28 дек 09, 14:11    [8127186]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
текст процедуры get_ost можно увидеть?
28 дек 09, 14:14    [8127204]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5499
Блог
объявление курсора поменять на declare cursor local static forward_only
Всяко побыстрее будет. ;)
28 дек 09, 14:43    [8127417]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
Березан Алексей
Member

Откуда: Оренбург
Сообщений: 12
SergSuper
текст процедуры get_ost можно увидеть?


Есть таблица ost_dayli в которой содержатся остатки по картам на какой-то день. Заполняется не каждый день, а в момент использования карты. Процедура ищет последнюю запись на указанную дату или перед ней.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GET_OST](
    @LOGIC_DATE datetime,
    @NCARD integer,
    @SUMM_ float OUTPUT)
AS
BEGIN
	set @SUMM_=0;
	declare @TMP_DATE datetime, @TMP_SUMM float;
  set @tmp_summ=0;
  set @summ_=0;
  set @tmp_date=(select max(logic_date) from ost_dayli where ncard=@ncard and logic_date<=@logic_date) 
  if @tmp_date is not null
  	begin
    	declare tmp_rep1 cursor for select SOSTATOK from ost_dayli where ncard=@ncard and logic_date=@tmp_date order by logic_date
  		open tmp_rep1;
      fetch next from tmp_rep1 into @TMP_SUMM;
      while @@FETCH_STATUS=0
      begin
	      if (@tmp_summ is null)
        	set @tmp_summ=0;
        fetch next from tmp_rep1 into @TMP_SUMM;  
      end
			CLOSE tmp_rep1;
			DEALLOCATE tmp_rep1;
    end
  set @summ_=@tmp_summ;
END

28 дек 09, 17:56    [8128843]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
мне кажется что эту процедуру можно заменить на такую функцию
create function get_ost(@LOGIC_DATE datetime,
    @NCARD integer) returns float
AS
begin
	declare @SUMM_ float
	select top 1 @SUMM_=SOSTATOK 
		from ost_dayli 
		where ncard=@ncard and logic_date=@tmp_date and logic_date<=@logic_date
		order by logic_date desk
	return isnull(@SUMM_, 0)
end
я написал особо не задумываясь, возможны ошибки, но Вы главное поймите принцип

если поймёте, то возможно сможете обойтись и без этой функции и делать тот отчет одним запросом
28 дек 09, 18:18    [8128974]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
Березан Алексей
Member

Откуда: Оренбург
Сообщений: 12
Создал функцию следующего вида:
CREATE FUNCTION [dbo].[GetOst2] 
(@LOGIC_DATE datetime, @NCARD integer)
RETURNS float
AS
BEGIN
	declare @TMP_DATE datetime, @TMP_SUMM float;
  set @tmp_summ=0;
  set @tmp_date=(select max(logic_date) from ost_dayli where ncard=@ncard and logic_date<=@logic_date) 
  if @tmp_date is not null
  	begin
    	set @tmp_summ=(select top 1 SOSTATOK from ost_dayli where ncard=@ncard and logic_date=@tmp_date)
	      if (@tmp_summ is null)
        	set @tmp_summ=0;
    end
	RETURN @tmp_summ;
END

получилось, что на объемах данных до одного месяца, скорость формирования отчета увеличилось и стала такой же как в ФБ. На больших объемах пока проигрыш так же почти в двое. Что-то необходимо придумывать с циклом
28 дек 09, 20:20    [8129294]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Березан Алексей,

Что-то у вас не совсем то же самое, что SergSuper предложил, написано...
Вас так и тянет в файрбёрд...
К тому же, для этого запроса надо план посмотреть, индексы потюнить.

Сообщение было отредактировано: 29 дек 09, 06:35
29 дек 09, 06:34    [8129935]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
Березан Алексей
Создал функцию следующего вида:
Зачем? Чем Вас мой вариант не устроил? Вам надо было разобраться как это работает, а не бездумно вставлять куски в свой вариант.
Березан Алексей
Что-то необходимо придумывать с циклом
Выкидывать. Для чего мы функцию то делаем?
29 дек 09, 10:33    [8130617]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
djХомяГ_
Guest
В отличии от FB (IB), в MS SQL можно и нужно использовать выборку непосредственно селектом в хранимых процедурах (функциях), что принесёт в 99.9% выигрыш в скорости. То есть не используйте циклы (курсоры)
ну и к слову (хотя в данном случае на скорость это не влияет)
вместо 
if (@tmp_summ is null)
        	set @tmp_summ=0;

можно написать return isnull(@tmp_summ,0)
29 дек 09, 11:00    [8130821]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
aleks2
Guest
djХомяГ_
В отличии от FB (IB), в MS SQL можно и нужно использовать выборку непосредственно селектом в хранимых процедурах (функциях), что принесёт в 99.9% выигрыш в скорости. То есть не используйте циклы (курсоры)
ну и к слову (хотя в данном случае на скорость это не влияет)
вместо 
if (@tmp_summ is null)
        	set @tmp_summ=0;

можно написать return isnull(@tmp_summ,0)


Хе-хе... чо уж пить боржоми чайными ложками?

CREATE FUNCTION [dbo].[GetOst2] 
(@LOGIC_DATE datetime, @NCARD integer)
RETURNS float
AS
BEGIN
RETURN ISNULL(
	  (select top 1 SOSTATOK from ost_dayli where ncard=@ncard and logic_date<=@logic_date ORDER BY logic_date DESC)
	 ,0
	)
END

29 дек 09, 15:24    [8133219]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
Березан Алексей
Member

Откуда: Оренбург
Сообщений: 12
aleks2,

спасибо, получается, что это самый оптимальный вариант функции. Теперь осталось "победить" оставшийся курсор
declare tmp_rep cursor for select ncard, summa, typeo, typev, logic_date, mtime, tocard from move_table 
WHERE (move_table.logic_date between @SDATE and @FDATE);
2 янв 10, 21:25    [8144076]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
а зачем использовать курсор для этой конструкции?
			if @typeo=1
				update rep_table set vznos=vznos+@summa where number=@ncard and id_user=@id_user;
			if @typeo=2 
				update rep_table set izyat=izyat+@summa where number=@ncard and id_user=@id_user;
			if @typeo=3 
				if @tocard is null 
					update rep_table set move=move+@summa where number=@ncard and id_user=@id_user;
				else
					update rep_table set move=move-@summa where number=@ncard and id_user=@id_user;
			if @typeo=4 
				update rep_table set plat=plat+@summa where number=@ncard and id_user=@id_user;
			if @typeo=5 
				update rep_table set skidki=skidki+@summa where number=@ncard and id_user=@id_user;
			if @typeo=6 
				update rep_table set vhod=vhod+@summa where number=@ncard and id_user=@id_user;

имхо, запросом можно сделать что-то подходящее
select ncard, summa = sum(summa), typeo
from move_table WHERE (move_table.logic_date between @SDATE and @FDATE)
group by ncard, typeo;
А вообще, надо детально разбираться в логике, слишком "процедурный" подход использовался при написании процедуры.
2 янв 10, 22:06    [8144166]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
Березан Алексей
Member

Откуда: Оренбург
Сообщений: 12
AAron,

Там смысл такой: таблица MOVE_TABLE, в которую записываются различные операции по карте (взнос, изъятие, оплата и т.д.), данная процедура предназначена для подготовки данных при печати отчета, отчет должен вывести по каждой (или не каждой) карте начальный остаток, приход, расход, изъятие.... и конечный остаток. Процедура перебирает MOVE_TABLE и если карта попалась впервые добавляет новую строку в REP_TABLE, если же строка уже есть, проверяет тип операции и суммирует нужную колонку, т.к. операций может быть неограниченное количество.
В принципе Ваш запрос гораздо упрощает схему, но опять же он "вертикальный", т.е. выводит в колонку все карты со всеми суммами по каждой операции, а это все по каждой карте нужно поместить в "горизонталь", т.е. что бы по каждой карте было ни несколько строк, а несколько колонок, и ничего "умнее" циклического перебора не придумал.
3 янв 10, 20:57    [8145304]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
Glory
Member

Откуда:
Сообщений: 104760
Березан Алексей
AAron,

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

Это называется перекрестный запрос
И делается он одним селектом через case или pivot
3 янв 10, 21:12    [8145337]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
Березан Алексей
Member

Откуда: Оренбург
Сообщений: 12
Glory

Это называется перекрестный запрос
И делается он одним селектом через case или pivot


!!! Это действительно гораздо эффективней, компактней и быстрее. Спасибо всем огромное! Я тут стооолько нового узнал ))). Пойду переделывать процедуру и учить мат.часть дальше
3 янв 10, 21:55    [8145421]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL медленнее FireBird помогите разобраться  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Березан Алексей
и ничего "умнее" циклического перебора не придумал.
Вот даже не вникая в суть задачи: а вы попробуйте придумать. Я Вам как работавший и с FB, и с MSSQL говорю.
автор
отчет должен вывести по каждой (или не каждой) карте начальный остаток, приход, расход, изъятие.... и конечный остаток.
Не проще ли это сделать на клиенте? Я, конечно, понимаю, что FB предоставляет очень эффективные средства позаписной обработки, но все же - для чего? Для чего вы пытаетесь весь отчет воспроизвести выходным рекордсетом?

ЗЫ. Перестраивайте мозги на другую СУБД.
3 янв 10, 21:58    [8145425]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить