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

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

Имеется программа на Delphi вычисляющая некоторые данные. Данные заносятся во временную таблицу, а обработка данных идет в цикле через хранимую процедуру.

Для справки стоит сказать, что совсем недавно переехали на новый сервер (с 2000 SP4 на 2008 R2), хотя я не уверен что причина в этом.

Особенностью программы является то, что пользователи задают различные параметры (фильтры) и по ним строится запрос. Грубо говоря каждый запрос может быть уникальным в зависимости от набора выбранных параметров. Для расчета таблицы данных используется тот же самый запрос.

Раньше для расчета вызывалась стандартная хранимая процедура с набором параметров и, естественно, этим самым уникальным запросом, затем делалось несколько REPLACE'ов и выполнялась гора динамический запросов. Это было узкое место, но оно работало гладко.


В общем недавно я начал оптимизировать процесс "расчета данных" и динамический создавать хранимку и ее выполнять в уже готовом виде. Но почему-то "первый запрос" в цикле выполняется крайне долго.

Тест:
DECLARE
	@LastIDRowIn bigint,
	@LastIDRowOut bigint,
	@TimeStart datetime
	
 SET @TimeStart = getdate()
	

PRINT 'LOCAL: '+CAST(DATEDIFF(second, @TimeStart, getdate()) AS VARCHAR(30))

CREATE TABLE #tbl_Res_16020115416151218 (
	[ID] [bigint] PRIMARY KEY,
	[Name] [varchar] (300) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[Type] [tinyint] DEFAULT 0 NOT NULL,
	[NOrder] [bigint] DEFAULT 0 NOT NULL,
	[S1] [bigint] DEFAULT 0 NOT NULL,
	[S2] [bigint] DEFAULT 0 NOT NULL,
	[S3] [bigint] DEFAULT 0 NOT NULL,
	[S4] [bigint] DEFAULT 0 NOT NULL,
	[S5] [bigint] DEFAULT 0 NOT NULL,
	[S6] [bigint] DEFAULT 0 NOT NULL,
	[SCount] [bigint] DEFAULT 0 NOT NULL,
	[SUMM] AS ([S1] + [S2] + [S3] + [S4] + [S5] + [S6]) ,
	[SADD1] [bigint] DEFAULT 0 NOT NULL,
	[SADD2] [bigint] DEFAULT 0 NOT NULL,
	[SADD3] [bigint] DEFAULT 0 NOT NULL,
	[Stat] [bit] DEFAULT 0 NOT NULL
)
;
PRINT 'LOCAL: '+CAST(DATEDIFF(second, @TimeStart, getdate()) AS VARCHAR(30))

INSERT INTO #tbl_Res_16020115416151218 (ID, Name, NOrder) (SELECT DISTINCT ID, Name, NOrder FROM viw_MassMedia_NonKill WHERE ID IN (SELECT ID_MassMedia FROM tbl_Notes WHERE ID IN (SELECT tbl_Notes.ID  FROM tbl_Notes, viw_Komment WHERE  tbl_Notes.DateNote BETWEEN '2010-01-06 0:00:00' AND '2010-12-24 0:00:00' AND ((viw_Komment.Komment LIKE '%новосиб%')) AND (tbl_Notes.Note LIKE '%ДТП%') AND tbl_Notes.ID_Komment=viw_Komment.ID AND tbl_Notes.[Kill] IS NULL ) ) )
PRINT 'LOCAL: '+CAST(DATEDIFF(second, @TimeStart, getdate()) AS VARCHAR(30))

INSERT INTO #tbl_Res_16020115416151218 (ID, Name, NOrder) (SELECT ID,Name, NOrder FROM viw_MassMedia_NonKill WHERE ID IN (SELECT DISTINCT ID_Parent FROM viw_MassMedia_NonKill WHERE ID IN (SELECT ID FROM #tbl_Res_16020115416151218) AND NOT ID_Parent IN (SELECT ID FROM #tbl_Res_16020115416151218) ) )
PRINT 'LOCAL: '+CAST(DATEDIFF(second, @TimeStart, getdate()) AS VARCHAR(30))

DELETE FROM #tbl_Res_16020115416151218 WHERE ID IN (SELECT ID FROM viw_MassMedia_NonKill WHERE ID_Parent IN (SELECT ID FROM viw_MassMedia WHERE ID_Parent IN (2,25,54,55)));DELETE FROM #tbl_Res_16020115416151218 WHERE ID IN (0,1);DELETE FROM #tbl_Res_16020115416151218 WHERE ID IN (SELECT ID FROM viw_MassMedia_NonKill WHERE ID_Parent=1)
PRINT 'LOCAL: '+CAST(DATEDIFF(second, @TimeStart, getdate()) AS VARCHAR(30))

SET @LastIDRowIn = 103

EXEC [sp_REPORT_ENGENIE_16020115416151218_ByCharacter] @LastIDRowIn, @LastIDRowOut OUTPUT

PRINT 'LOCAL: '+CAST(DATEDIFF(second, @TimeStart, getdate()) AS VARCHAR(30))

SELECT @LastIDRowOut

DROP TABLE #tbl_Res_16020115416151218


Пример хранимки
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_REPORT_ENGENIE_16020115416151218_ByCharacter] @LastIDRowIn bigint, @LastIDRowOut bigint OUTPUT AS
 DECLARE @SQL_d datetime,@SQL_i int, @TimeStart datetime
 SET @SQL_d = getdate() SET @SQL_i = 0 SET @TimeStart = getdate()
 PRINT 'PROCEDURE: (SET) '+CAST(DATEDIFF(second, @TimeStart, getdate()) AS VARCHAR(30))
 SET @LastIDRowOut = @LastIDRowIn
 PRINT 'PROCEDURE: (SET2) '+CAST(DATEDIFF(second, @TimeStart, getdate()) AS VARCHAR(30))
 IF (@LastIDRowOut = 0) SET @LastIDRowOut = (SELECT MIN(ID) FROM #tbl_Res_16020115416151218)
 PRINT 'PROCEDURE: (IF=0) '+CAST(DATEDIFF(second, @TimeStart, getdate()) AS VARCHAR(30))
 WHILE ( (@SQL_i<2) AND (@LastIDRowOut>0) ) BEGIN
       PRINT 'PROCEDURE: (WHILE) '+CAST(DATEDIFF(second, @TimeStart, getdate()) AS VARCHAR(30))
       UPDATE #tbl_Res_16020115416151218 SET S1 = (SELECT count(*)  FROM tbl_Notes, tbl_Note_Person, viw_Komment WHERE  tbl_Notes.DateNote BETWEEN '2010-01-06 0:00:00' AND '2010-12-24 0:00:00' AND ((viw_Komment.Komment LIKE '%новосиб%')) AND (tbl_Notes.Note LIKE '%ДТП%') AND tbl_Notes.ID_MassMedia IN (SELECT ID FROM viw_MassMedia WHERE ID_Parent=@LastIDRowOut UNION SELECT @LastIDRowOut) AND tbl_Notes.ID_Komment=viw_Komment.ID AND tbl_Notes.ID=tbl_Note_Person.ID_Note AND tbl_Notes.[Kill] IS NULL  AND tbl_Note_Person.ID_Character=3) WHERE ID=@LastIDRowOut
       PRINT 'PROCEDURE: (S1) '+CAST(DATEDIFF(second, @TimeStart, getdate()) AS VARCHAR(30))
       UPDATE #tbl_Res_16020115416151218 SET S2 = (SELECT count(*)  FROM tbl_Notes, tbl_Note_Person, viw_Komment WHERE  tbl_Notes.DateNote BETWEEN '2010-01-06 0:00:00' AND '2010-12-24 0:00:00' AND ((viw_Komment.Komment LIKE '%новосиб%')) AND (tbl_Notes.Note LIKE '%ДТП%') AND tbl_Notes.ID_MassMedia IN (SELECT ID FROM viw_MassMedia WHERE ID_Parent=@LastIDRowOut UNION SELECT @LastIDRowOut) AND tbl_Notes.ID_Komment=viw_Komment.ID AND tbl_Notes.ID=tbl_Note_Person.ID_Note AND tbl_Notes.[Kill] IS NULL  AND tbl_Note_Person.ID_Character=4) WHERE ID=@LastIDRowOut
       PRINT 'PROCEDURE: (S2) '+CAST(DATEDIFF(second, @TimeStart, getdate()) AS VARCHAR(30))
       UPDATE #tbl_Res_16020115416151218 SET S3 = (SELECT count(*)  FROM tbl_Notes, tbl_Note_Person, viw_Komment WHERE  tbl_Notes.DateNote BETWEEN '2010-01-06 0:00:00' AND '2010-12-24 0:00:00' AND ((viw_Komment.Komment LIKE '%новосиб%')) AND (tbl_Notes.Note LIKE '%ДТП%') AND tbl_Notes.ID_MassMedia IN (SELECT ID FROM viw_MassMedia WHERE ID_Parent=@LastIDRowOut UNION SELECT @LastIDRowOut) AND tbl_Notes.ID_Komment=viw_Komment.ID AND tbl_Notes.ID=tbl_Note_Person.ID_Note AND tbl_Notes.[Kill] IS NULL  AND tbl_Note_Person.ID_Character=5) WHERE ID=@LastIDRowOut
       PRINT 'PROCEDURE: (S3) '+CAST(DATEDIFF(second, @TimeStart, getdate()) AS VARCHAR(30))
       UPDATE #tbl_Res_16020115416151218 SET S4 = (SELECT count(*)  FROM tbl_Notes, tbl_Note_Person, viw_Komment WHERE  tbl_Notes.DateNote BETWEEN '2010-01-06 0:00:00' AND '2010-12-24 0:00:00' AND ((viw_Komment.Komment LIKE '%новосиб%')) AND (tbl_Notes.Note LIKE '%ДТП%') AND tbl_Notes.ID_MassMedia IN (SELECT ID FROM viw_MassMedia WHERE ID_Parent=@LastIDRowOut UNION SELECT @LastIDRowOut) AND tbl_Notes.ID_Komment=viw_Komment.ID AND tbl_Notes.ID=tbl_Note_Person.ID_Note AND tbl_Notes.[Kill] IS NULL  AND tbl_Note_Person.ID_Character=6) WHERE ID=@LastIDRowOut
       PRINT 'PROCEDURE: (S4) '+CAST(DATEDIFF(second, @TimeStart, getdate()) AS VARCHAR(30))
       UPDATE #tbl_Res_16020115416151218 SET S5 = (SELECT count(*)  FROM tbl_Notes, tbl_Note_Person, viw_Komment WHERE  tbl_Notes.DateNote BETWEEN '2010-01-06 0:00:00' AND '2010-12-24 0:00:00' AND ((viw_Komment.Komment LIKE '%новосиб%')) AND (tbl_Notes.Note LIKE '%ДТП%') AND tbl_Notes.ID_MassMedia IN (SELECT ID FROM viw_MassMedia WHERE ID_Parent=@LastIDRowOut UNION SELECT @LastIDRowOut) AND tbl_Notes.ID_Komment=viw_Komment.ID AND tbl_Notes.ID=tbl_Note_Person.ID_Note AND tbl_Notes.[Kill] IS NULL  AND tbl_Note_Person.ID_Character=7) WHERE ID=@LastIDRowOut
       PRINT 'PROCEDURE: (S5) '+CAST(DATEDIFF(second, @TimeStart, getdate()) AS VARCHAR(30))
 SET @LastIDRowOut = (SELECT MIN(ID) FROM #tbl_Res_16020115416151218 WHERE ID>@LastIDRowOut)
 PRINT 'PROCEDURE (SET MIN): '+CAST(DATEDIFF(second, @TimeStart, getdate()) AS VARCHAR(30))
 IF (@LastIDRowOut IS NULL) SET @LastIDRowOut = 0
 SET @SQL_i = DATEDIFF(SECOND, @SQL_d, getdate())
 END 


Результат работы:
LOCAL: 0
LOCAL: 0

(строк обработано: 9)

(строк обработано: 1)
LOCAL: 2

(строк обработано: 6)

(строк обработано: 1)
LOCAL: 2

(строк обработано: 5)

(строк обработано: 1)

(строк обработано: 0)

(строк обработано: 1)

(строк обработано: 2)

(строк обработано: 1)
LOCAL: 2
PROCEDURE: (SET) 0
PROCEDURE: (SET2) 0
PROCEDURE: (IF=0) 0
PROCEDURE: (WHILE) 0

(строк обработано: 1)

(строк обработано: 1)
PROCEDURE: (S1) 39

(строк обработано: 1)

(строк обработано: 1)
PROCEDURE: (S2) 40

(строк обработано: 1)

(строк обработано: 1)
PROCEDURE: (S3) 40

(строк обработано: 1)

(строк обработано: 1)
PROCEDURE: (S4) 40

(строк обработано: 1)

(строк обработано: 1)
PROCEDURE: (S5) 40

(строк обработано: 1)
PROCEDURE (SET MIN): 40
LOCAL: 42

(строк обработано: 1)


Как видно между
PROCEDURE: (WHILE) 0
PROCEDURE: (S1) 39
слишком большой промежуток времени.

Подскажите пожалуйста из-за чего так?
16 дек 11, 13:35    [11777234]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти тормоза!  [new]
WarAnt
Member

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

какие объемы таблицы #tbl_Res_16020115416151218 после заполнения?
какой план у update это таблицы
невидно ни одного создания индекса в коде, их что правда нет??
16 дек 11, 14:02    [11777468]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти тормоза!  [new]
ясненько
Guest
Missory,

автор
FROM tbl_Notes, tbl_Note_Person, viw_Komment

джойны через запятую - эт плохо, пнятненько?
автор
((viw_Komment.Komment LIKE '%новосиб%')) AND (tbl_Notes.Note LIKE '%ДТП%')

если лайк начинается с % - эт плохо, пнятненько? эт индексов, а в некоторых случаев - и детей не будет, ясненько, пнятненько?
автор
(SELECT count(*)  FROM...AND tbl_Note_Person.ID_Character=7)

все каунты вычисляют одну и ту же выборку, отличие только в одном единственном id.
вместо 5 сканов можно вычислить все то же самое одним сканом. и апдейтный скан будет тоже всего один вместо пяти.
автор
SELECT count(CASE WHEN tbl_Note_Person.ID_Character=7 THEN 1 END), count(case..5) FROM

пнятненько?
автор
WHERE ID=@LastIDRowOut

SET @LastIDRowOut = (SELECT MIN(ID) FROM #tbl_Res_16020115416151218 WHERE ID>@LastIDRowOut)

CROSS/OUTER APPLY - это гораздо более быстрый цикл чем весь этот while, пнятненько?

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

и при генерации динамического sql не надо на пробелах и переводах строк экономить. никто кроме себя любимого читать жеж не будет, че глаза ломать?

наркотики - эт плохо! пнятненько?
16 дек 11, 20:19    [11780367]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти тормоза!  [new]
Missory
Member

Откуда: Новосибирск
Сообщений: 101
WarAnt,

Таблицы мелкие, порядка 20-30 записей.
Не более 300.
Поэтому и индексов нету. План на работе )
Но все процедуры все эти запросы работают как надо - быстро и четко.
16 дек 11, 20:21    [11780371]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти тормоза!  [new]
пнятненько
Guest
Missory
Таблицы мелкие, порядка 20-30 записей.

нафига цикл и 5 апдейтов тогда? одним запросом написать все вышеперечисленное не проблема.
2 вариант - tempdb и прочие вопросы не к коду, а к окружению.
16 дек 11, 20:32    [11780403]     Ответить | Цитировать Сообщить модератору
 Re: Помогите найти тормоза!  [new]
Missory
Member

Откуда: Новосибирск
Сообщений: 101
Спасибо, разобрался.
Оказывается APPLY очень клевая штука.
Вот только я не смог додуматься как это все сделать через UPDATE, поэтому немного переписал программу (оно и к лучшему) и теперь все делается раз в 8-10 быстрее.
Теперь все делается одним запросом.


КРУТА ) Спасибо за советы!

INSERT INTO ##tbl_Res_5720111521151245 ([ID], [Name], [NOrder], [S1], [S2], [S3], [S4], [S5])
   SELECT
   tRES.ID AS [ID],
   max(tRES.[Name]) AS [Name],
   max(tRES.[NOrder]) AS [NOrder],
COUNT(CASE WHEN tbl_Notes.ID_Character=3 THEN 1 ELSE NULL END) AS [S1],
COUNT(CASE WHEN tbl_Notes.ID_Character=4 THEN 1 ELSE NULL END) AS [S2],
COUNT(CASE WHEN tbl_Notes.ID_Character=5 THEN 1 ELSE NULL END) AS [S3],
COUNT(CASE WHEN tbl_Notes.ID_Character=6 THEN 1 ELSE NULL END) AS [S4],
COUNT(CASE WHEN tbl_Notes.ID_Character=7 THEN 1 ELSE NULL END) AS [S5] 
FROM tbl_Notes INNER JOIN tbl_Note_Organization
ON (tbl_Notes.ID=tbl_Note_Organization.ID_Note)  CROSS APPLY (SELECT vO.ID, vO.FullName AS [Name], vO.NOrder FROM viw_Organization_NonKill vO WHERE tbl_Note_Organization.ID_Organization=vO.ID) AS tRES 
WHERE 
 tbl_Notes.DateNote BETWEEN '2010-11-13 0:00:00' AND '2010-11-13 23:59:59'
 AND ((tbl_Notes.ID_Level = 4) )
 AND tbl_Notes.ID_Repeated=0
 AND tbl_Notes.ID=tbl_Note_Organization.ID_Note
 AND tbl_Notes.[Kill] IS NULL 
 GROUP BY tRES.ID
21 дек 11, 12:20    [11801917]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить