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

Откуда:
Сообщений: 27
Запускаю два запроса:

Запрос №1
declare		@date_start	datetime, @date_end	datetime
select		@date_start = '11/26/2012',	@date_end = '11/28/2012'

select		t.owner_id,
		t.work_date,
		t.currency,
		sum(isnull(t.amount_total, 0)) as amount_total
from		ESTransactionCore.dbo.tb_transactions_m as t
where		t.status_id = '2' and
		t.work_date >= @date_start and
		t.work_date <= @date_end
group by	t.work_date, t.owner_id, t.currency


Запрос №2
select		t.owner_id,
		t.work_date,
		t.currency,
		sum(isnull(t.amount_total, 0)) as amount_total
from		ESTransactionCore.dbo.tb_transactions_m as t
where		t.status_id = '2' and
		t.work_date >= '11/26/2012' and
		t.work_date <= '11/28/2012'
group by	t.work_date, t.owner_id, t.currency


Получил следующий Execution Plan:
Зпрос


Детали первого запроса
Зпрос 1

Детали второго запроса
Зпрос 2

Теперь возникает вопрос, почему если в "запросе 2" я использую фиксированную дату, запрос выполняеться быстрее чем если в "запросе 1" я использую переменные?

Как "запрос 1" переписать , чтобы он выполнялся как "запрос 2" ?

К сообщению приложен файл. Размер - 92Kb
28 ноя 12, 15:49    [13543834]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
попробуй хинт optimize for для тех значений параметров которые тебе нужны
28 ноя 12, 17:18    [13544741]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Мистер Хенки
попробуй хинт optimize for для тех значений параметров которые тебе нужны


наверное, все же, option( recompile ), будет более общим решением. хотя, если применить "optimize for" - можно идти за попкорном - следующий вопрос будет почему оно теперь тормозит при других значениях :)
28 ноя 12, 17:26    [13544793]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Dukhota 77
Member

Откуда:
Сообщений: 27
Забыл написать, у меня MSSQL 2005.
OPTIMIZE FOR данной версией сиквела не подерживаеться
28 ноя 12, 17:30    [13544820]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Crimean
Мистер Хенки
попробуй хинт optimize for для тех значений параметров которые тебе нужны


наверное, все же, option( recompile ), будет более общим решением. хотя, если применить "optimize for" - можно идти за попкорном - следующий вопрос будет почему оно теперь тормозит при других значениях :)

за что купил, за то и продал
автор
Как "запрос 1" переписать , чтобы он выполнялся как "запрос 2" ?
28 ноя 12, 17:31    [13544827]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Dukhota 77,

option( recompile )
28 ноя 12, 17:38    [13544883]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Dukhota 77
Member

Откуда:
Сообщений: 27
Crimean
Dukhota 77,
option( recompile )


Не помогло...(((
28 ноя 12, 18:45    [13545351]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Dukhota 77
Member

Откуда:
Сообщений: 27
Я вообще не могу понять, какая разница сиквелу, как я передаю дату в запросе.
В первом и втором запросе два скалярных значения... чего ему не хватает?
28 ноя 12, 18:47    [13545359]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Crimean
Member

Откуда:
Сообщений: 13148
а для 2005, возможно, и не поможет, кстати
разница - поверьте - есть
28 ноя 12, 18:54    [13545401]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Dukhota 77
Забыл написать, у меня MSSQL 2005.
OPTIMIZE FOR данной версией сиквела не подерживаеться

Поддерживается: http://msdn.microsoft.com/en-us/library/ms181714(v=sql.90).aspx

По теме:
Dukhota 77
почему если в "запросе 2" я использую фиксированную дату, запрос выполняеться быстрее

потому что после выполнения запроса 1 данные закэшировались и уже не считывались с диска при выполнении запроса 2?
28 ноя 12, 21:45    [13546210]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Dukhota 77
Теперь возникает вопрос, почему если в "запросе 2" я использую фиксированную дату, запрос выполняеться быстрее чем если в "запросе 1" я использую переменные?
Простите, но где видно что он выполняется быстрее/медленне? Может вы покажите таки время выполнения запросов? А то о чем речь - не понятно.

Да, не выкладывайте пожалуйста больше планы картинками, здесь не сообщество дизайнеров.
29 ноя 12, 00:59    [13546773]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Alexander Titkin
Member

Откуда: Москва
Сообщений: 91
Dukhota 77,

Будьте добры скрипт таблицы и , как уже сказали, план запросов в текстовом виде
29 ноя 12, 09:26    [13547338]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Dukhota 77,

Если описанные выше советы вам не помогли, то выполните запросы в режиме сбора статистики и реальных планов
set statistics xml, time, io on
go
--запрос 1
go
--запрос 2
go
set statistics xml, time, io off
go

Это будет отправной точкой.

Еще можно посмотреть на следующий аспект. В случае, когда оптимизатор не может сделать точную оценку из-за локальных переменных, он делает догадку, которая, судя по вашим картинкам, оказывается примерно вдвое больше по количеству строк и значит, требуемой памяти. А т.к. hash agg потребляет память, то возникает вопрос.
Все нормально у вас с памятью, запрос всегда может получить требуемую память? Что будет если создать индекс по полям группировки со включенной колонкой суммы (тогда по идее в плане должен появиться не потребляющий память stream agg). Впрочем, это догадки.
29 ноя 12, 11:22    [13547840]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Crimean
Member

Откуда:
Сообщений: 13148
по-моему, нас где-то обманывают
нашел вот "Microsoft SQL Server 2005 - 9.00.4340.00 (Intel X86)"

К сообщению приложен файл. Размер - 60Kb
29 ноя 12, 11:39    [13547965]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Crimean
Member

Откуда:
Сообщений: 13148
P.S.

то есть разное поведение есть и оно более чем объяснимо, но оно не всегда, а только тогда, когда оценка имеет значение и влияние на план. и при этом указанная опция действительно меняет план, фактически приводя его к варианту с константами
29 ноя 12, 11:41    [13547975]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Поведение option(recompile) сильно зависимо от версии. Вот тут есть подробности A Tale of Service Packs and Cumulative Updates. Так что вполне возможно, что у ТС та версия, где option(recompile) не приводит к замене переменных на runtime constants.
29 ноя 12, 11:57    [13548104]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Crimean
Member

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

у меня, к сожалению, только эта версия "пятого" на руках, а тс не указал точно свою версию с одной стороны, с другой - ему никто не мешает вытянуть и поставить последние фиксы. результат-то "налицо"
29 ноя 12, 12:06    [13548168]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Dukhota 77
Member

Откуда:
Сообщений: 27
У меня на боевом сервере такая версия:
Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Сегодня ночью поставлю
Microsoft SQL Server 2005 Service Pack 4 RTM
Version: 9.00.5000.00


сп4
29 ноя 12, 12:47    [13548586]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Dukhota 77
Member

Откуда:
Сообщений: 27
Попробовал я запрос с очищением кэша и с OPTIMIZE FOR и получил следующий план:

К сообщению приложен файл. Размер - 89Kb
29 ноя 12, 13:58    [13549391]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Добрый Э - Эх
Guest
Dukhota 77,

может уже настало время научиться получать текстовое представление плана выполнения запроса, а не рвать мониторы такими портянками?
29 ноя 12, 14:00    [13549421]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Dukhota 77
Member

Откуда:
Сообщений: 27
Добрый Э - Эх,

Я сделал скрин, чтобы видно было текс запроса и результат одновременно!
29 ноя 12, 14:04    [13549466]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Dukhota 77
Member

Откуда:
Сообщений: 27
Запрос №1 (с OPTIMIZE FOR)

declare		@date_start	datetime, @date_end	datetime
select		@date_start = '11/01/2012',	@date_end = '11/28/2012'
DBCC FREEPROCCACHE

select		t.owner_id, t.work_date, t.currency, sum(isnull(t.amount_total, 0)) as amount_total from		ESTransactionCore.dbo.tb_transactions_m as t
where		t.status_id = '2' and t.work_date >= @date_start and t.work_date <= @date_end group by	t.work_date, t.owner_id, t.currency
OPTION ( OPTIMIZE FOR (@date_start = '11/01/2012', @date_end = '11/28/2012'));


  |--Parallelism(Gather Streams)
       |--Hash Match(Aggregate, HASH:([t].[work_date], [t].[owner_id], [t].[currency]), RESIDUAL:([ESTransactionCore].[dbo].[tb_transactions_m].[work_date] as [t].[work_date] = [ESTransactionCore].[dbo].[tb_transactions_m].[work_date] as [t].[work_date] AND [ESTransactionCore].[dbo].[tb_transactions_m].[owner_id] as [t].[owner_id] = [ESTransactionCore].[dbo].[tb_transactions_m].[owner_id] as [t].[owner_id] AND [ESTransactionCore].[dbo].[tb_transactions_m].[currency] as [t].[currency] = [ESTransactionCore].[dbo].[tb_transactions_m].[currency] as [t].[currency]) DEFINE:([Expr1002]=SUM([partialagg1004])))
            |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([t].[work_date], [t].[owner_id], [t].[currency]))
                 |--Hash Match(Partial Aggregate, HASH:([t].[work_date], [t].[owner_id], [t].[currency]), RESIDUAL:([ESTransactionCore].[dbo].[tb_transactions_m].[work_date] as [t].[work_date] = [ESTransactionCore].[dbo].[tb_transactions_m].[work_date] as [t].[work_date] AND [ESTransactionCore].[dbo].[tb_transactions_m].[owner_id] as [t].[owner_id] = [ESTransactionCore].[dbo].[tb_transactions_m].[owner_id] as [t].[owner_id] AND [ESTransactionCore].[dbo].[tb_transactions_m].[currency] as [t].[currency] = [ESTransactionCore].[dbo].[tb_transactions_m].[currency] as [t].[currency]) DEFINE:([partialagg1004]=SUM([Expr1003])))
                      |--Compute Scalar(DEFINE:([Expr1003]=isnull([ESTransactionCore].[dbo].[tb_transactions_m].[amount_total] as [t].[amount_total],(0.00))))
                           |--Index Seek(OBJECT:([ESTransactionCore].[dbo].[tb_transactions_m].[EX_work_date1] AS [t]), SEEK:(([t].[work_date], [t].[status_id]) >= ([@date_start], (2)) AND ([t].[work_date], [t].[status_id]) <= ([@date_end], (2))),  WHERE:([ESTransactionCore].[dbo].[tb_transactions_m].[status_id] as [t].[status_id]=(2)) ORDERED FORWARD)



Запрос №2 (с константами)
DBCC FREEPROCCACHE
select		t.owner_id, t.work_date, t.currency, sum(isnull(t.amount_total, 0)) as amount_total from		ESTransactionCore.dbo.tb_transactions_m as t
where		t.status_id = '2' and t.work_date >= '11/01/2012' and t.work_date <= '11/28/2012' group by	t.work_date, t.owner_id, t.currency


  |--Parallelism(Gather Streams)
       |--Hash Match(Aggregate, HASH:([t].[work_date], [t].[owner_id], [t].[currency]), RESIDUAL:([ESTransactionCore].[dbo].[tb_transactions_m].[work_date] as [t].[work_date] = [ESTransactionCore].[dbo].[tb_transactions_m].[work_date] as [t].[work_date] AND [ESTransactionCore].[dbo].[tb_transactions_m].[owner_id] as [t].[owner_id] = [ESTransactionCore].[dbo].[tb_transactions_m].[owner_id] as [t].[owner_id] AND [ESTransactionCore].[dbo].[tb_transactions_m].[currency] as [t].[currency] = [ESTransactionCore].[dbo].[tb_transactions_m].[currency] as [t].[currency]) DEFINE:([Expr1002]=SUM([partialagg1004])))
            |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([t].[work_date], [t].[owner_id], [t].[currency]))
                 |--Hash Match(Partial Aggregate, HASH:([t].[work_date], [t].[owner_id], [t].[currency]), RESIDUAL:([ESTransactionCore].[dbo].[tb_transactions_m].[work_date] as [t].[work_date] = [ESTransactionCore].[dbo].[tb_transactions_m].[work_date] as [t].[work_date] AND [ESTransactionCore].[dbo].[tb_transactions_m].[owner_id] as [t].[owner_id] = [ESTransactionCore].[dbo].[tb_transactions_m].[owner_id] as [t].[owner_id] AND [ESTransactionCore].[dbo].[tb_transactions_m].[currency] as [t].[currency] = [ESTransactionCore].[dbo].[tb_transactions_m].[currency] as [t].[currency]) DEFINE:([partialagg1004]=SUM([Expr1003])))
                      |--Compute Scalar(DEFINE:([Expr1003]=isnull([ESTransactionCore].[dbo].[tb_transactions_m].[amount_total] as [t].[amount_total],(0.00))))
                           |--Index Seek(OBJECT:([ESTransactionCore].[dbo].[tb_transactions_m].[EX_work_date1] AS [t]), SEEK:(([t].[work_date], [t].[status_id]) >= ('2012-11-01 00:00:00.000', (2)) AND ([t].[work_date], [t].[status_id]) <= ('2012-11-28 00:00:00.000', (2))),  WHERE:([ESTransactionCore].[dbo].[tb_transactions_m].[status_id] as [t].[status_id]=(2)) ORDERED FORWARD)


Запрос №3 (с переменными)
DBCC FREEPROCCACHE

declare		@date_start	datetime, @date_end	datetime
select		@date_start = '11/01/2012',	@date_end = '11/28/2012'
select		t.owner_id, t.work_date, t.currency, sum(isnull(t.amount_total, 0)) as amount_total from		ESTransactionCore.dbo.tb_transactions_m as t
where		t.status_id = '2' and t.work_date >= @date_start and t.work_date <= @date_end group by	t.work_date, t.owner_id, t.currency


  |--Hash Match(Aggregate, HASH:([t].[work_date], [t].[owner_id], [t].[currency]), RESIDUAL:([ESTransactionCore].[dbo].[tb_transactions_m].[work_date] as [t].[work_date] = [ESTransactionCore].[dbo].[tb_transactions_m].[work_date] as [t].[work_date] AND [ESTransactionCore].[dbo].[tb_transactions_m].[owner_id] as [t].[owner_id] = [ESTransactionCore].[dbo].[tb_transactions_m].[owner_id] as [t].[owner_id] AND [ESTransactionCore].[dbo].[tb_transactions_m].[currency] as [t].[currency] = [ESTransactionCore].[dbo].[tb_transactions_m].[currency] as [t].[currency]) DEFINE:([Expr1002]=SUM([Expr1003])))
       |--Compute Scalar(DEFINE:([Expr1003]=isnull([ESTransactionCore].[dbo].[tb_transactions_m].[amount_total] as [t].[amount_total],(0.00))))
            |--Index Seek(OBJECT:([ESTransactionCore].[dbo].[tb_transactions_m].[EX_work_date1] AS [t]), SEEK:(([t].[work_date], [t].[status_id]) >= ([@date_start], (2)) AND ([t].[work_date], [t].[status_id]) <= ([@date_end], (2))),  WHERE:([ESTransactionCore].[dbo].[tb_transactions_m].[status_id] as [t].[status_id]=(2)) ORDERED FORWARD)


Сообщение было отредактировано: 29 ноя 12, 17:27
29 ноя 12, 14:09    [13549528]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Добрый Э - Эх
Dukhota 77,

может уже настало время научиться получать текстовое представление плана выполнения запроса, а не рвать мониторы такими портянками?

лучше уж в xml
29 ноя 12, 17:19    [13551425]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Dukhota 77
Попробовал я запрос с очищением кэша и с OPTIMIZE FOR и получил следующий план:

ну так сервер включил параллелизм для 1 и 2 случая. Думаю 1 и 2 выполняются быстрее чем 3, но отжирают больше ресурсов
Если параллелизм не желателен ставится хинт option (maxdop 1)
Время выполнения запроса SET STATISTICS TIME ON
и до кучи SET STATISTICS IO ON
29 ноя 12, 17:27    [13551507]     Ответить | Цитировать Сообщить модератору
 Re: Непонятки с запросом  [new]
Crimean
Member

Откуда:
Сообщений: 13148
ладно, отложу попкорн и еще раз намекну. OPTIMIZE FOR тут не совсем к месту. была бы одна дата - возможно, стоило бы применить, указав наиболее "типовое" значение, для которого 101% известно распределение данных и оно "хорошее". а поскольку дат 2, то использование OPTIMIZE FOR даст те же проблемы, но только в профиль. ибо для выборок "за 5 минут" и "за 5 мясяцев" планы должны быть разные для эффективного выполнения
29 ноя 12, 17:30    [13551548]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить