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

Откуда: Ukraine
Сообщений: 160
SQL 2005. Поведение одинаковое на сервере без ServicePack и с SP3
Следующий запрос виснет

if (
select count(*) from stas p1 join stas p2 on p1.id=p2.id+1 
  where (p1.tar<p2.tar or p1.tar=p2.tar and p1.date1 <=p2.date1) 
)>0 
print 1 else print 2
Но если запустить подзапрос в скобках после if, то он мгновенно отрабатывает с результатом 0.
Возможно, что это просто долгое выполнение, но терпения дожидаться нет.

В общем, проблема решилась, даже двумя способами:

1) с помощью создания индекса по stas.id.

2) переделыванием запроса на
declare @k int
select @k = count(*) from stas p1 join stas p2 on p1.id=p2.id+1 
  where (p1.tar<p2.tar or p1.tar=p2.tar and p1.date1 <=p2.date1) 
if @k>0 
print 1 else print 2
Но интересен принцип, почему так происходит? Что можно почитать по этому поводу?
Спасибо
23 июн 09, 11:17    [7331936]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36800
Использовать индексы для увеличения производительности запросов на выборку? Какая нелепость!
23 июн 09, 11:21    [7331960]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Zum-Zum,

Откройте для себя SQL-оператор EXISTS
23 июн 09, 11:24    [7331989]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
Zum-Zum
Member

Откуда: Ukraine
Сообщений: 160
Я же написал, что проблема решена, кстати Exists - третий вариант, но только в случае, если сравнение идет с нулем. Мне непонятно другое: что мешает оптимизатору в случае заворачивания запроса в условие для if ?
23 июн 09, 11:31    [7332043]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
Fire83
Member

Откуда: Гомель-Минск
Сообщений: 474
Zum-Zum,
дело не в том, что вы вынесли оператор из if, а в том что индекс создали!
23 июн 09, 12:18    [7332371]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
Zum-Zum
Member

Откуда: Ukraine
Сообщений: 160
Но в случае с временной переменной индекса нет, а запрос не тормозит...
23 июн 09, 13:06    [7332675]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
Fire83
Member

Откуда: Гомель-Минск
Сообщений: 474
что-то слабо верится.. Давайте так:
1) Убиваете созданный индекс по stas.id

2) Выполняете скрипт и фиксируете время выполнения:
SET SHOWPLAN_TEXT ON
GO
[Вариант 1]
GO
SET SHOWPLAN_TEXT OFF
GO

3) Аналогично для второго варианта
SET SHOWPLAN_TEXT ON
GO
[Вариант 2]
GO
SET SHOWPLAN_TEXT OFF
GO
Результаты выполнения и время выполнения обоих скриптов выкладываете
23 июн 09, 13:15    [7332721]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
Zum-Zum
Member

Откуда: Ukraine
Сообщений: 160
DDL
CREATE TABLE [dbo].[stas](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[tar] [int] NULL,
	[date1] [datetime] NULL,
	[stas_n] [int] NULL,
	[sog] [int] NULL,
	[datdif] [int] NULL
) ON [PRIMARY]

Индексов сейчас нет
Всего в таблице 71,5 тысяч строк

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Вариант, где запрос висит (отменен после 10 минут ожидания)
if (select count(*) from stas p1 join stas p2 on p1.id=p2.id+1 
  where (p1.tar<p2.tar or p1.tar=p2.tar and p1.date1 <=p2.date1) 
)>0

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1008]=CASE WHEN [Expr1009] THEN (1) ELSE (0) END))
       |--Nested Loops(Left Semi Join, DEFINE:([Expr1009] = [PROBE VALUE]))
            |--Constant Scan
            |--Nested Loops(Inner Join, WHERE:([SH_REM].[dbo].[stas].[id] as [p1].[id]=[Expr1011] AND ([SH_REM].[dbo].[stas].[tar] as [p1].[tar]<[SH_REM].[dbo].[stas].[tar] as [p2].[tar] OR [SH_REM].[dbo].[stas].[tar] as [p1].[tar]=[SH_REM].[dbo].[stas].[t
                 |--Compute Scalar(DEFINE:([Expr1011]=[SH_REM].[dbo].[stas].[id] as [p2].[id]+(1)))
                 |    |--Table Scan(OBJECT:([SH_REM].[dbo].[stas] AS [p2]))
                 |--Table Spool
                      |--Table Scan(OBJECT:([SH_REM].[dbo].[stas] AS [p1]))

(8 row(s) affected)

StmtText
-----------------
  
print 1
   else print 2



(3 row(s) affected)


StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Этот отрабатывает за 0 секунд
declare @k int
select @k = count(*) from stas p1 join stas p2 on p1.id=p2.id+1 
  where (p1.tar<p2.tar or p1.tar=p2.tar and p1.date1 <=p2.date1)

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[Expr1010],0)))
       |--Stream Aggregate(DEFINE:([Expr1010]=Count(*)))
            |--Hash Match(Inner Join, HASH:([p1].[id])=([Expr1007]), RESIDUAL:([SH_REM].[dbo].[stas].[id] as [p1].[id]=[Expr1007] AND ([SH_REM].[dbo].[stas].[tar] as [p1].[tar]<[SH_REM].[dbo].[stas].[tar] as [p2].[tar] OR [SH_REM].[dbo].[stas].[tar] as [p1
                 |--Table Scan(OBJECT:([SH_REM].[dbo].[stas] AS [p1]))
                 |--Compute Scalar(DEFINE:([Expr1007]=[SH_REM].[dbo].[stas].[id] as [p2].[id]+(1)))
                      |--Table Scan(OBJECT:([SH_REM].[dbo].[stas] AS [p2]))

(6 row(s) affected)

StmtText
-----------------

if @k >0
  
print 1
   else print 2



(4 row(s) affected)

М-да, только что выяснил, что если вместо if (select ...) > 0 написать if (select ...) > 1, и даже if (select ...) > @n, то тормозов нет, план идентичный второму из приведенных. Посмеялся...
23 июн 09, 13:46    [7332971]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
Mishka999
Member

Откуда:
Сообщений: 35
Nitchevo strannovo:

V 1-om variante: ( NESTED LOOP JOIN )

Na kazduyu stroku v STAS P2 ( 70,000 strok ) proishodit FULL TABLE SCAN of STAS P1 ( te ze 70,000 strok). Vsego prosmatrivautsa 70,000 * 70,000 = 4,900,000,000 strok.
23 июн 09, 16:19    [7334050]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
АнонимкиН
Guest
Сделайте UPDATE STATISTICS на данной табличке
ибо у меня и первый случай за миллисекунды выполняется при 70000 строк
23 июн 09, 16:50    [7334283]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
Zum-Zum
Member

Откуда: Ukraine
Сообщений: 160
UPDATE STATISTICS stas
Не помогает.
Что-то странное в этом целом нуле, вот если написать ...)>0.0, то так без томозов.
23 июн 09, 18:12    [7334718]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Zum-Zum
UPDATE STATISTICS stas
Не помогает.
Что-то странное в этом целом нуле, вот если написать ...)>0.0, то так без томозов.
Ну, дык, опять же план посмотрите...
24 июн 09, 06:51    [7335775]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
Zum-Zum
Member

Откуда: Ukraine
Сообщений: 160
С целым нулем план я привел выше (первый).

С нулем в виде действительного числа нет никаких Nested Loops:
StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Вариант, где запрос висит
if (select count(*) from stas p1 join stas p2 on p1.id=p2.id+1 
  where (p1.tar<p2.tar or p1.tar=p2.tar and p1.date1 <=p2.date1) 
)>0.0

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1008]=CASE WHEN CONVERT_IMPLICIT(numeric(11,1),[Expr1006],0)>(0.0) THEN (1) ELSE (0) END))
       |--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[Expr1014],0)))
            |--Stream Aggregate(DEFINE:([Expr1014]=Count(*)))
                 |--Hash Match(Inner Join, HASH:([p1].[id])=([Expr1010]), RESIDUAL:([SH_REM].[dbo].[stas].[id] as [p1].[id]=[Expr1010] AND ([SH_REM].[dbo].[stas].[tar] as [p1].[tar]<[SH_REM].[dbo].[stas].[tar] as [p2].[tar] OR [SH_REM].[dbo].[stas].[tar] a
                      |--Table Scan(OBJECT:([SH_REM].[dbo].[stas] AS [p1]))
                      |--Compute Scalar(DEFINE:([Expr1010]=[SH_REM].[dbo].[stas].[id] as [p2].[id]+(1)))
                           |--Table Scan(OBJECT:([SH_REM].[dbo].[stas] AS [p2]))

(7 row(s) affected)

StmtText
-----------------
  
print 1
   else print 2



(3 row(s) affected)
24 июн 09, 09:00    [7335865]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
Zum-Zum
Member

Откуда: Ukraine
Сообщений: 160
В предыдущем посте комментарий конечно же должен быть
-- запрос выполняется за доли секунды
24 июн 09, 09:11    [7335881]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
АнонимкиН
Guest
А приведите скрин Options Вашей базы данных
24 июн 09, 11:04    [7336281]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Zum-Zum,

а почему написали так:
p1.tar<p2.tar or p1.tar=p2.tar
а не так
p1.tar<=p2.tar
?
24 июн 09, 11:42    [7336549]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
Zum-Zum
Member

Откуда: Ukraine
Сообщений: 160
Скопировал таблицу в другую базу, оказывается в другой базе тормоза также и на (select...)>0.0, но без тормозов на >1, >0.01, а также на >=0
Options этой другой базы в аттаче.
Options базы, где изначально была таблица stas, отличается от приведенноого только в двух строках:
Recovery model: Simple
Recovery > Page Verify = NONE

tpg
Кроме "or" там есть и "and" с бОльшим приоритетом, но, наверное, к описанному фокусу это не относится.

К сообщению приложен файл. Размер - 0Kb
24 июн 09, 16:36    [7339035]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
АнонимкиН
Guest
Ммм. Вообщем, похоже остается только сослаться на некую внутреннюю "кухню" оптимизатора.
Через десяток попыток я тоже повторил Ваш запрос с полным зависанием))) Ну то есть запрос в первом сообщении привел к полной полке в 100% загрузки CPU и очень большим логическим чтениям судя по sys.dm_exec_requests
При этом процесс был с последним статусом wait'ов = sos_scheduler_yield - то есть постоянно отдавал процессорное время
Вывод: использовать временную переменную или же exists при условном операторе if
Как это часто бывает в sql server'e - прямой метод получается самым неэффективным))) (note: искать 10 Things I Hate About SQL Server в гугле) и надо использовать более хитрый)
24 июн 09, 17:07    [7339331]     Ответить | Цитировать Сообщить модератору
 Re: Странное зависание запроса  [new]
АнонимкиН
Guest
А знаете, я тут подумал, что это возможно еще и из-за того, что у Вас в запросе идет размытая логика:
джоин по p1.id = p2.id + 1

и если анализировать логически, то для максимального в идеале значения p1.id должно быть еще большее число на 1, что конечно же быть не может. Но, у Вас таблица без индексов, то есть хранится кучей и стало быть ее надо читать всю и много раз. Вообщем, хочется сказать, что в некоторых случаях, оптимизатор принимает решение исходя из нормальных результатов - здесь же нормального результата быть не может, так как присутствует исключение.
Нужно явно указать выборку без максимального p1.id
25 июн 09, 13:59    [7342927]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить