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

Откуда:
Сообщений: 186
Ребята помогите в такой проблеме.

Для некоторых деталей запрос на входимость выполняется очень долго - 5 минут 8 секунд. При этом выдает 29557 записей.

Клиент делфи вылетает через 3 минуты с ошибкой Timeout SQL operation.

Вопрос в том как в процедуре закончить рекурсивный цикл по времени скажем через 3 минуты ?
Или может как то оптимизировать запрос ?



/* ВЕДОМОСТЬ ВХОДИМОСТИ
								(с) 29.12.2008 RESEARCH	*/
CREATE    PROCEDURE [dbo].[PRIM] (@IZD VARCHAR(30)) AS


SELECT 1 UR,@IZD CHTO,@IZD CUDA,1 VX INTO #PRIM
DECLARE @UR INT
SET @UR=1

WHILE @@ROWCOUNT>0 
BEGIN
	SELECT @UR=@UR+1
	INSERT INTO #PRIM
	SELECT @UR UR,B.CHTO, B.CUDA, SUM(B.KVO*P.VX) VX FROM BAZSPEC B, #PRIM P
	WHERE  (B.CHTO=P.CUDA) AND (P.UR>=@UR-1)
	GROUP BY UR,B.CHTO,B.CUDA
END

SELECT CUDA, SUM(VX) TOTAL INTO #TOTAL FROM #PRIM
GROUP BY CUDA

SELECT PR.UR,UPPER(PR.CHTO) CHTO, dbo.NAM(PR.CHTO)NAMCHTO,UPPER(PR.CUDA) CUDA,dbo.NAM(PR.CUDA)NAMCUDA,PR.VX,TTL.TOTAL FROM #PRIM PR
LEFT JOIN #TOTAL TTL ON TTL.CUDA=PR.CUDA
ORDER BY 1 DESC ,4,2

RETURN @UR

GO
28 янв 13, 15:27    [13839892]     Ответить | Цитировать Сообщить модератору
 Re: Как прервать запрос по таймауту  [new]
Гость333
Member

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

Покажите CREATE TABLE на таблицу BAZSPEC (включая все индексы). А также напишите, сколько записей в этой таблице.

RESEARCH
При этом выдает 29557 записей

Сколько итераций цикла при этом выполняется?
28 янв 13, 15:31    [13839928]     Ответить | Цитировать Сообщить модератору
 Re: Как прервать запрос по таймауту  [new]
RESEARCH
Member

Откуда:
Сообщений: 186
CREATE TABLE [BAZSPEC] (
	[CUDA] [varchar] (25) COLLATE Cyrillic_General_CI_AS NULL ,
	[PRR] [varchar] (1) COLLATE Cyrillic_General_CI_AS NULL ,
	[PRU] [varchar] (1) COLLATE Cyrillic_General_CI_AS NULL ,
	[CHTO] [varchar] (25) COLLATE Cyrillic_General_CI_AS NULL ,
	[KVO] [float] NULL ,
	[TABN] [int] NULL CONSTRAINT [DF__BAZSPEC__TABN__10E07F16] DEFAULT (0),
	[DAT] [smalldatetime] NULL ,
	CONSTRAINT [Пустые значения запрещены] CHECK ([CUDA] > '' and [CHTO] > ''),
	CONSTRAINT [Рекурсивная входимость запрещена] CHECK ([CUDA] <> [CHTO])
) ON [PRIMARY]

CREATE  CLUSTERED  INDEX [cudachto] ON [dbo].[BAZSPEC]([CUDA], [CHTO]) ON [PRIMARY]
CREATE  INDEX [CHTOCUDA] ON [dbo].[BAZSPEC]([CHTO], [CUDA]) ON [PRIMARY]


1850454 записей

(ОТГРУЗКА)СЛ030210
4 8 72502016119 8.0 49006 2010-02-08 10:03:00 (ОТГРУЗКА)СЛ030210
3 0 8ЮР293004 4.0 49006 2010-02-08 10:03:00 (ОТГРУЗКА)СЛ030210
3 0 8ЯТ151822 4.0 49006 2010-02-08 10:03:00 (ОТГРУЗКА)СЛ030210
6 8 91700293509 3.6000000000000001 49006 2010-02-08 10:03:00 (ОТГРУЗКА)СЛ030210
6 8 93530100209 22.800000000000001 49006 2010-02-08 10:03:00 (ОТГРУЗКА)СЛ030210
6 8 95140200109 0.23999999999999999 49006 2010-02-08 10:03:00
28 янв 13, 15:38    [13839987]     Ответить | Цитировать Сообщить модератору
 Re: Как прервать запрос по таймауту  [new]
RESEARCH
Member

Откуда:
Сообщений: 186
9 итераций
28 янв 13, 15:39    [13839993]     Ответить | Цитировать Сообщить модератору
 Re: Как прервать запрос по таймауту  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Ок, а dbo.NAM — что за функция? Если вместо dbo.NAM(PR.CHTO) и dbo.NAM(PR.CUDA) в последнем селекте подставить пустые строки, то вместо 5 минут 8 секунд — сколько будет выполняться процедура?

RESEARCH
	CONSTRAINT [Пустые значения запрещены] CHECK ...
	CONSTRAINT [Рекурсивная входимость запрещена] CHECK ...

Любопытный приём, надо будет взять на заметку.
28 янв 13, 16:07    [13840191]     Ответить | Цитировать Сообщить модератору
 Re: Как прервать запрос по таймауту  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
Гость333
RESEARCH
	CONSTRAINT [Пустые значения запрещены] CHECK ...
	CONSTRAINT [Рекурсивная входимость запрещена] CHECK ...

Любопытный приём, надо будет взять на заметку.
Особенно если учесть, что CHECK CONSTRAINT радостно пропускает NULLы
28 янв 13, 16:21    [13840324]     Ответить | Цитировать Сообщить модератору
 Re: Как прервать запрос по таймауту  [new]
RESEARCH
Member

Откуда:
Сообщений: 186
Нулы туда не попадают так как прграмный код дает только ''... хотя вобще верное замечание надо исправить


так что нет никаких способов вычислить время выполнения цикла и прервать его через 3 минуты ?
29 янв 13, 12:06    [13843471]     Ответить | Цитировать Сообщить модератору
 Re: Как прервать запрос по таймауту  [new]
Гость333
Member

Откуда:
Сообщений: 3683
RESEARCH
так что нет никаких способов вычислить время выполнения цикла и прервать его через 3 минуты ?

Чтобы при этом процедура завершилась штатным способом, без всяких "kill spid" и тайм-аутов? Нету такого.

Вы не туда копаете. Процедуру можно и нужно оптимизировать. Пока из того, что вы показали, я больше всего подозреваю неведомую функцию dbo.NAM. Применение скалярной пользовательской функции 60000 раз (2 раза на каждую из 29557 записей) при определённых условиях способно напрочь убить быстродействие запроса.

Так что покажите, что делает функция dbo.NAM, и посмотрите, какая будет скорость, если убрать её из запроса.
29 янв 13, 12:28    [13843598]     Ответить | Цитировать Сообщить модератору
 Re: Как прервать запрос по таймауту  [new]
Glory
Member

Откуда:
Сообщений: 104760
RESEARCH
так что нет никаких способов вычислить время выполнения цикла и прервать его через 3 минуты ?

Время выполнения вычисляется как разность между временем начала цикла(которое надо запомнить) и текущим временем.
29 янв 13, 13:20    [13843903]     Ответить | Цитировать Сообщить модератору
 Re: Как прервать запрос по таймауту  [new]
RESEARCH
Member

Откуда:
Сообщений: 186
автор
Время выполнения вычисляется как разность между временем начала цикла(которое надо запомнить) и текущим временем.


гениально это именно то что мне нужно подумал я
но потом решил все таки проверить первый совет хотя он и показался мне маловероятным
ведь функция NAM не подводила на малых выброках

Пока из того, что вы показали, я больше всего подозреваю неведомую функцию dbo.NAM. Применение скалярной пользовательской функции 60000 раз (2 раза на каждую из 29557 записей) при определённых условиях способно напрочь убить быстродействие запроса.


все же убрав эту функцию я с удивлением обнаружил что запрос выполнился за 7 секунд

итого конечная выборка теперь выглядит так

SELECT PR.UR,UPPER(PR.CHTO) CHTO, COALESCE(W1.NAME,NC1.NAMAT,@NONAM) NAMCHTO,UPPER(PR.CUDA) CUDA, COALESCE(W2.NAME,NC2.NAMAT,@NONAM) NAMCUDA,PR.VX,TTL.TOTAL FROM #PRIM PR
LEFT JOIN WHAT W1 ON W1.CHTO=PR.CHTO
LEFT JOIN NOMCEN NC1 ON NC1.KODM=PR.CHTO
LEFT JOIN WHAT W2 ON W2.CHTO=PR.CUDA
LEFT JOIN NOMCEN NC2 ON NC2.KODM=PR.CUDA
LEFT JOIN #TOTAL TTL ON TTL.CUDA=PR.CUDA
ORDER BY 1 DESC ,4,2


спасибо обоим за советы

PS запустил формирование дерева входимости на Delphi клиенте - построил два уровня и пока висит ггг
30 янв 13, 14:44    [13851045]     Ответить | Цитировать Сообщить модератору
 Re: Как прервать запрос по таймауту  [new]
user89
Member

Откуда:
Сообщений: 2083
RESEARCH
запустил формирование дерева входимости на Delphi клиенте - построил два уровня и пока висит
Вместо ADOTable лучше использовать ADODataSet. Сортировать лучше на клиенте.
Перемещение по большому/отсортированному набору данных очень медленное. Я строил дерево (HTML, 5 уровней) и использовал объект RecordSet. Примерно так:
  ADODataSet1.Recordset.Sort := нужные столбцы;

  ADODataSet1.Recordset.MoveFirst;

  //Двигаемся по результату SQL-запроса и считываем данные
  while not ADODataSet1.Recordset.Eof do
  begin
    ...
    ADODataSet1.Recordset.MoveNext;
  end; // while not ADODataSet1.Recordset.Eof
30 янв 13, 16:25    [13851878]     Ответить | Цитировать Сообщить модератору
 Re: Как прервать запрос по таймауту  [new]
user89
Member

Откуда:
Сообщений: 2083
RESEARCH
Клиент делфи вылетает через 3 минуты с ошибкой Timeout SQL operation.
чтобы долгий запрос не отваливался по тайм-ауту, его можно выполнить в асинхронном режиме. При этом можно спокойно работать с приложением и дальше, не обращая внимания на запрос (его можно и прервать по кнопке). Там ничего сложного нет, примерчик Запрос в отдельном потоке
30 янв 13, 16:36    [13851967]     Ответить | Цитировать Сообщить модератору
 Re: Как прервать запрос по таймауту  [new]
RESEARCH
Member

Откуда:
Сообщений: 186
автор
Вместо ADOTable лучше использовать ADODataSet. Сортировать лучше на клиенте.
Перемещение по большому/отсортированному набору данных очень медленное.


я использую компонент TTreeView для построения дерева из модуля ComCtrls

PROCEDURE TfmPRIM.FillTree (Q:TDataSET;TV:TTreeView);

        {       ПЛАН ПОСТРОЕНИЯ ДЕРЕВА ПРИМЕНЯЕМОСТИ

        1. Создаем корень дерева
        2. Начинаем цикл по уровням начиная с 1
           Обнуляем счетчик добавлений подъэлементов
         3. Цикл по элементам дерева начиная с 1 элемента
          4. Определяем уровень текущего элемента дерева
          5. Если уровень элемента дерева соответствует уровню цикла то
                6. Фильтруем набор данных по уровню цикла и коду элемента дерева
                7. В цикле добавляем к элементу дерева подэлементы уровня набора данных, увеличиваем счетчик добавлений и счетчик элементов дерева
          8. Переходим к следующему элементу дерева до окончания набора элементов
        9. Если счетчик добавлений подъэелементов пустой то выходим из цикла уровней            }




CONST c='''';  VAR ur,tr,U: Word;   N:integer; S: String;



FUNCTION Element(var QU:TDataSET): String;                                       // Возвращаем строку описания позиции
BEGIN WITH QU DO Result:=INTtoSTR(UR)                                            // уровень
                        +'   '+FieldBYName('CUDA').asString                      // обозначение
                        +'   '+FieldBYName('NAMCUDA').asString                   // наименование
                        +'  [' +FieldBYName('VX').asString                       // количество в сборке
                        +'/'  +FieldBYName('TOTAL').asString+'] ';               // входимость в изделие
                        QU.Next; INC(N); END;




//Заполняем деревце данными
BEGIN


   TV.Items.Clear; meStatus.Lines.Clear; UR:=1;

   if Q.isEmpty then Exit;
   { 1. Создаем корень дерева }
   Q.Filter:='UR=1'; Q.Filtered:=True; Q.First;
   TV.Items.AddChild(nil,Q.FieldBYName('CHTO').asString+'  '+Q.FieldBYName('NAMCHTO').asString);
   while not Q.EOF do TV.Items.AddChild(TV.Items[0],Element(Q));
   meStatus.lines.add ('Корень создан');

   { 2. Начинаем цикл по уровням начиная с 1 }
   REPEAT INC(UR);
        { Обнуляем счетчик добавлений подъэлементов }    N:=0;

   { 3. Цикл по элементам дерева начиная с 1 элемента }  TR:=1;
     REPEAT
        { 4. Определяем уровень текущего элемента дерева }
        S:=copy(TV.Items[TR].Text,0,2);
     //   try U:=STRtoINT(copy(S,Length(S)-1,2)) except U:=100; end;              // на случай коллизии
        if S[2]=' ' then  U:=STRtoINT(S[1])
                    else  U:=STRtoINT(S)   ;
        { 5. Если уровень элемента дерева соответствует уровню цикла то }
        if U=UR-1 then begin
          { 6. Фильтруем набор данных по уровню цикла и коду элемента дерева }
          S:='UR='+INTtoSTR(ur)+' and CHTO='+c+copy(TV.Items[tr].Text,5,11)+c;
          Q.Filter:=S;
          { 7. В цикле добавляем к элементу дерева подэлементы уровня набора данных, увеличиваем счетчик добавлений и счетчик элементов дерева}
          Q.First; while not Q.EOF do TV.Items.AddChild(TV.Items[TR],Element(Q));
                     end { if U=UR};

     {8. Переходим к следующему элементу дерева, до окончания набора элементов }
     INC(TR);
     UNTIL TR>=TV.Items.Count;

   if N>0 then meStatus.lines.add ('Уровень '+inttostr(ur)+'. '+inttostr(N)+' позиций.');
   { 9. Если счетчик добавлений подъэелементов пустой? то выходим из цикла уровней }
   UNTIL N=0;
   Q.Filtered:=False;
END;
30 янв 13, 19:14    [13852854]     Ответить | Цитировать Сообщить модератору
 Re: Как прервать запрос по таймауту  [new]
гениально
Guest
пардон за оффтоп, но "индийский стиль" бывает не только в программировании:
подъэелемент, равно как и подъэлемент, это круто!
30 янв 13, 19:19    [13852882]     Ответить | Цитировать Сообщить модератору
 Re: Как прервать запрос по таймауту  [new]
user89
Member

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

для больших данных вместо TTreeView лучше использовать VirtualTreeView.

+ Где-то нарыл ускорение TTreeView
Если вы хотите производить поиск по дереву, может быть для того, чтобы найти узел, соответствующий определенному критерию, то НЕ ДЕЛАЙТЕ ЭТО ТАКИМ ОБРАЗОМ:
for i := 0 to pred(MyTreeView.Items.Count) do
begin
  if MyTreeView.Items[i].Text = 'Банзай' then
    break;
end;
...если вам не дорого время обработки массива узлов.


Значительно быстрее будет так:
Noddy := MyTreeView.Items[0];
Searching := true;
while (Searching) and (Noddy <> nil) do
begin
  if Noddy.text = SearchTarget then
  begin
    Searching := False;
    MyTreeView.Selected := Noddy;
    MyTreeView.SetFocus;
  end
  else
  begin
    Noddy := Noddy.GetNext
  end;
end;

В моей системе приведенный выше код показал скорость 33 милисекунды при работе с деревом, имеющим 171 узел. Первый поиск
потребовал 2.15 секунд. Оказывается, процесс индексирования очень медленный. Я подозреваю, что при каждой индексации свойства Items, вы осуществляете линейный поиск, но это нигде не засвидетельствовано, поэтому я могу ошибаться.
Вам действительно не нужно просматривать все дерево, чтобы найти что вам нужно - получить таким образом доступ к MyTreeView.Items[170] займет много больше времени, чем получения доступа к MyTreeView.Items[1]. Как правило, для отслеживания позиции в дереве TreeView, нужно использовать временную переменную TTreeNode, а не использовать целочисленные индексы. Возможно, свойство ItemId как раз и необходимо для такого применения, но, к сожалению, я никак не могу понять абзац в электронной документации, касающийся данного свойства:
"Свойство ItemId является дескриптором TTreeNode типа HTreeItem
и однозначно идентифицирует каждый элемент дерева. Используйте
это свойство, если вам необходимо получить доступ к элементам
дерева из внешнего по отношению к TreeView элемента управления."

Возможно, еще будет ускорение TTreeView при таком подходе:
MyTreeView.BeginUpdate;
<Заполняем дерево>
MyTreeView.EndUpdate;
30 янв 13, 19:38    [13852943]     Ответить | Цитировать Сообщить модератору
 Re: Как прервать запрос по таймауту  [new]
RESEARCH
Member

Откуда:
Сообщений: 186
а я то думаю что здесь не так, попробую исправить может заработает
30 янв 13, 19:42    [13852954]     Ответить | Цитировать Сообщить модератору
 Re: Как прервать запрос по таймауту  [new]
RESEARCH
Member

Откуда:
Сообщений: 186
автор
для больших данных вместо TTreeView лучше использовать VirtualTreeView.



VirtualTreeView в каком модуле, в ComCtrl такого нету, чем он отличается от TTreeView ?

MyTreeView.BeginUpdate; - не нашел в списке такого метода это вероятно относится к Virtual

по поводу поиска по индексу в самую точку вероятно надо использовать указатели
30 янв 13, 19:53    [13852984]     Ответить | Цитировать Сообщить модератору
 Re: Как прервать запрос по таймауту  [new]
user89
Member

Откуда:
Сообщений: 2083
RESEARCH
MyTreeView.BeginUpdate; - не нашел в списке такого метода
Ага. Правильнее
MyTreeView.Items.BeginUpdate

VirtualTreeView – это бесплатный компонент, написанный аж в 1999 году на смену тормознутому TTreeView. По нему в гугле много материала. Сам компонент качаем отсюда (исправлено много глюков) http://delphi-gems.com/index.php/controls/virtual-treeview
Хорошие доки с примерами http://yadi.sk/d/VSLfp4Iv2FRBU

VirtualTreeView используется в почтовом клиенте The Bat и во многих других продуктах
31 янв 13, 10:03    [13854811]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить