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

Откуда: Москва
Сообщений: 1016
Наблюдаю следующую ситуацию:
При прогоне SQL скрипта взятого из процедуры - все проходит нормально 30 сек. время отработки запроса.
При прогоне самой процедуры - запрос выполняется по часу!!!

Причем на сервере используется паралелизм.
При сравнении планов - паралелизм должен давать даже прирост в скорости, так как он используется при выборки из индексов и т.д.
Планы нормальные и практически одинаковы.
Так что по логике - все должно быть пучком.

При MAXDOP 1 процедура конечно работает быстро, но на одном проце.

Причем если компилиш запрос без MAXDOP 1, то и процедура начинает тормозить - похоже, что у запроса и процедуры планы одинаковые (Разве эти планы не должны хранится паралельно? То ведь запрос, а то же процедура. Есть же отдельно процедурный кеш!!?)

Как заставить процедуру работать с паралелизмом так же так же быстро как и простой запрос???

Хотелось бы понять в чем соль.
Так как просто бегать вокгур сервера с бубнами не хочется.
12 окт 04, 16:18    [1027325]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
Breakneck
Member

Откуда: Kiev
Сообщений: 2454
А план запроса и его текст не хотите привести?
12 окт 04, 16:29    [1027381]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1016
Привожу:
----------------

Процедура без паралелилизма:

StmtText                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
exec KOGetPricesLastDate_test '10/12/2004'
  CREATE PROCEDURE [dbo].[KOGetPricesLastDate_test] 
@DateTo datetime
--with recompile
AS
BEGIN

set nocount on
  	create table #MaxPrDate
	(WareCode int,RegionID int,MaxDate smalldatetime)
  	create table #NullPrDate
	(WareCode int,RegionID int,NullDate smalldatetime)
  	insert into #MaxPrDate
	select P.WareCode,P.RegionID,max(P.PriceHistoryDate) from 
	PricesRegionHistory P(NOLOCK)
	inner join #WL WL on WL.WareCode=P.WareCode
	inner join NamesList NL(NOLOCK) on WL.WareCode=NL.WareCode and NL.Deleted = 0
	where 
	

(5 row(s) affected)

StmtText                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
       |--Table Insert(OBJECT:([tempdb].[dbo].[#MaxPrDate__________________________________________________________________________________________________________0000001EA72C]), SET:([#MaxPrDate].[MaxDate]=[Expr1004], [#MaxPrDate].[RegionID]=[P].[RegionID
            |--Top(ROWCOUNT est 0)
                 |--Compute Scalar(DEFINE:([Expr1004]=Convert([Expr1003])))
                      |--Filter(WHERE:([Expr1003]<=[@DateTo]))
                           |--Hash Match(Inner Join, HASH:([NL].[WareCode])=([P].[WareCode]))
                                |--Merge Join(Inner Join, MERGE:([NL].[WareCode])=([WL].[WareCode]), RESIDUAL:([WL].[WareCode]=[NL].[WareCode]))
                                |    |--Clustered Index Scan(OBJECT:([CashOffice].[dbo].[NamesList].[PK__NamesList__0E79DF0E] AS [NL]),  WHERE:(Convert([NL].[Deleted])=0) ORDERED FORWARD)
                                |    |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#WL_________________________________________________________________________________________________________________0000001EA72C].[PK__#WL__29944F1D] AS [WL]), ORDERED FOR
                                |--Stream Aggregate(GROUP BY:([P].[RegionID], [P].[WareCode]) DEFINE:([Expr1003]=MAX([P].[PriceHistoryDate])))
                                     |--Index Seek(OBJECT:([CashOffice].[dbo].[PricesRegionHistory].[IX_PricesRegionHistory_G_PriceUSD] AS [P]), SEEK:([P].[RegionID] < 3000 OR [P].[RegionID] > 3000),  WHERE:([P].[PriceUSD]<0.00 OR [P].[PriceUSD]>0.00) ORDE

(10 row(s) affected)

StmtText                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  	insert into #NullPrDate
	select P.WareCode,P.RegionID,min(P.PriceHistoryDate) from 
	PricesRegionHistory P(NOLOCK)
	inner join #WL WL on WL.WareCode=P.WareCode
	inner join NamesList NL(NOLOCK) on WL.WareCode=NL.WareCode and NL.Deleted = 0
	inner jo

(1 row(s) affected)

StmtText                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
       |--Table Insert(OBJECT:([tempdb].[dbo].[#NullPrDate_________________________________________________________________________________________________________0000001EA72C]), SET:([#NullPrDate].[NullDate]=[Expr1005], [#NullPrDate].[RegionID]=[P].[Regio
            |--Top(ROWCOUNT est 0)
                 |--Compute Scalar(DEFINE:([Expr1005]=Convert([Expr1004])))
                      |--Filter(WHERE:([Expr1004]<=[@DateTo]))
                           |--Stream Aggregate(GROUP BY:([P].[RegionID], [P].[WareCode]) DEFINE:([Expr1004]=MIN([P].[PriceHistoryDate])))
                                |--Nested Loops(Inner Join, OUTER REFERENCES:([M].[RegionID], [M].[WareCode], [M].[MaxDate]))
                                     |--Nested Loops(Inner Join, OUTER REFERENCES:([M].[WareCode]))
                                     |    |--Nested Loops(Inner Join, OUTER REFERENCES:([M].[WareCode]))
                                     |    |    |--Sort(ORDER BY:([M].[RegionID] ASC, [M].[WareCode] ASC))
                                     |    |    |    |--Table Scan(OBJECT:([tempdb].[dbo].[#MaxPrDate__________________________________________________________________________________________________________0000001EA72C] AS [M]))
                                     |    |    |--Clustered Index Seek(OBJECT:([CashOffice].[dbo].[NamesList].[PK__NamesList__0E79DF0E] AS [NL]), SEEK:([NL].[WareCode]=[M].[WareCode]),  WHERE:(Convert([NL].[Deleted])=0) ORDERED FORWARD)
                                     |    |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#WL_________________________________________________________________________________________________________________0000001EA72C].[PK__#WL__29944F1D] AS [WL]), SEEK:(
                                     |--Index Seek(OBJECT:([CashOffice].[dbo].[PricesRegionHistory].[IX_PricesRegionHistory_G_PriceUSD] AS [P]), SEEK:([P].[RegionID]=[M].[RegionID] AND [P].[WareCode]=[M].[WareCode]),  WHERE:([P].[PriceHistoryDate]>Convert(

(13 row(s) affected)

StmtText                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  	insert into #TmpStock
	select M.WareCode, max( dateadd(d,-1,N.NullDate))
	from #MaxPrDate M
	inner join #NullPrDate N on M.WareCode = N.WareCode and M.RegionID=N.RegionID
	--where N.NullDate>M.MaxDate
	group by M.WareCode
	
/*
	insert into #TmpS

(1 row(s) affected)

StmtText                                                                                                                                                                                                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
       |--Table Insert(OBJECT:([tempdb].[dbo].[#TmpStock___________________________________________________________________________________________________________0000001EA72C]), SET:([#TmpStock].[dt]=[Expr1003], [#TmpStock].[WareCode]=[M].[WareCode]))
            |--Top(ROWCOUNT est 0)
                 |--Compute Scalar(DEFINE:([Expr1003]=Convert([Expr1002])))
                      |--Stream Aggregate(GROUP BY:([M].[WareCode]) DEFINE:([Expr1002]=MAX(dateadd(day, -1, [N].[NullDate]))))
                           |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([M].[WareCode])=([N].[WareCode]), RESIDUAL:([N].[WareCode]=[M].[WareCode] AND [N].[RegionID]=[M].[RegionID]))
                                |--Sort(ORDER BY:([M].[WareCode] ASC))
                                |    |--Table Scan(OBJECT:([tempdb].[dbo].[#MaxPrDate__________________________________________________________________________________________________________0000001EA72C] AS [M]))
                                |--Sort(ORDER BY:([N].[WareCode] ASC))
                                     |--Table Scan(OBJECT:([tempdb].[dbo].[#NullPrDate_________________________________________________________________________________________________________0000001EA72C] AS [N]))

(9 row(s) affected)

StmtText                    
--------------------------- 
  
drop table #MaxPrDate
  
drop table #NullPrDate

(2 row(s) affected)





Вот с паралелилизмом:
StmtText                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
exec KOGetPricesLastDate_test '10/12/2004'
  CREATE PROCEDURE [dbo].[KOGetPricesLastDate_test] 
@DateTo datetime
--with recompile
AS
BEGIN

set nocount on
  	create table #MaxPrDate
	(WareCode int,RegionID int,MaxDate smalldatetime)
  	create table #NullPrDate
	(WareCode int,RegionID int,NullDate smalldatetime)
  	insert into #MaxPrDate
	select P.WareCode,P.RegionID,max(P.PriceHistoryDate) from 
	PricesRegionHistory P(NOLOCK)
	inner join #WL WL on WL.WareCode=P.WareCode
	inner join NamesList NL(NOLOCK) on WL.WareCode=NL.WareCode and NL.Deleted = 0
	where 
	

(5 row(s) affected)

StmtText                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
       |--Table Insert(OBJECT:([tempdb].[dbo].[#MaxPrDate__________________________________________________________________________________________________________0000001EA72C]), SET:([#MaxPrDate].[MaxDate]=[Expr1004], [#MaxPrDate].[RegionID]=[P].[RegionID
            |--Top(ROWCOUNT est 0)
                 |--Compute Scalar(DEFINE:([Expr1004]=Convert([Expr1003])))
                      |--Parallelism(Gather Streams)
                           |--Filter(WHERE:([Expr1003]<=[@DateTo]))
                                |--Hash Match(Inner Join, HASH:([NL].[WareCode])=([P].[WareCode]))
                                     |--Hash Match(Inner Join, HASH:([NL].[WareCode])=([WL].[WareCode]))
                                     |    |--Bitmap(HASH:([NL].[WareCode]), DEFINE:([Bitmap1008]))
                                     |    |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([NL].[WareCode]))
                                     |    |         |--Clustered Index Scan(OBJECT:([CashOffice].[dbo].[NamesList].[PK__NamesList__0E79DF0E] AS [NL]), WHERE:(Convert([NL].[Deleted])=0))
                                     |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([WL].[WareCode]), WHERE:(PROBE([Bitmap1008])=TRUE))
                                     |         |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#WL_________________________________________________________________________________________________________________0000001EA72C].[PK__#WL__29944F1D] AS [WL]))
                                     |--Parallelism(Repartition Streams, PARTITION COLUMNS:([P].[WareCode]))
                                          |--Stream Aggregate(GROUP BY:([P].[RegionID], [P].[WareCode]) DEFINE:([Expr1003]=MAX([P].[PriceHistoryDate])))
                                               |--Parallelism(Repartition Streams, PARTITION COLUMNS:([P].[RegionID], [P].[WareCode]), ORDER BY:([P].[RegionID] ASC, [P].[WareCode] ASC))
                                                    |--Index Seek(OBJECT:([CashOffice].[dbo].[PricesRegionHistory].[IX_PricesRegionHistory_G_PriceUSD] AS [P]), SEEK:([P].[RegionID] < 3000 OR [P].[RegionID] > 3000),  WHERE:([P].[PriceUSD]<0.00 OR [P].[Price

(16 row(s) affected)

StmtText                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  	insert into #NullPrDate
	select P.WareCode,P.RegionID,min(P.PriceHistoryDate) from 
	PricesRegionHistory P(NOLOCK)
	inner join #WL WL on WL.WareCode=P.WareCode
	inner join NamesList NL(NOLOCK) on WL.WareCode=NL.WareCode and NL.Deleted = 0
	inner jo

(1 row(s) affected)

StmtText                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
       |--Table Insert(OBJECT:([tempdb].[dbo].[#NullPrDate_________________________________________________________________________________________________________0000001EA72C]), SET:([#NullPrDate].[NullDate]=[Expr1005], [#NullPrDate].[RegionID]=[P].[Regio
            |--Top(ROWCOUNT est 0)
                 |--Compute Scalar(DEFINE:([Expr1005]=Convert([Expr1004])))
                      |--Filter(WHERE:([Expr1004]<=[@DateTo]))
                           |--Stream Aggregate(GROUP BY:([P].[RegionID], [P].[WareCode]) DEFINE:([Expr1004]=MIN([P].[PriceHistoryDate])))
                                |--Nested Loops(Inner Join, OUTER REFERENCES:([M].[RegionID], [M].[WareCode], [M].[MaxDate]))
                                     |--Nested Loops(Inner Join, OUTER REFERENCES:([M].[WareCode]))
                                     |    |--Nested Loops(Inner Join, OUTER REFERENCES:([M].[WareCode]))
                                     |    |    |--Sort(ORDER BY:([M].[RegionID] ASC, [M].[WareCode] ASC))
                                     |    |    |    |--Table Scan(OBJECT:([tempdb].[dbo].[#MaxPrDate__________________________________________________________________________________________________________0000001EA72C] AS [M]))
                                     |    |    |--Clustered Index Seek(OBJECT:([CashOffice].[dbo].[NamesList].[PK__NamesList__0E79DF0E] AS [NL]), SEEK:([NL].[WareCode]=[M].[WareCode]),  WHERE:(Convert([NL].[Deleted])=0) ORDERED FORWARD)
                                     |    |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#WL_________________________________________________________________________________________________________________0000001EA72C].[PK__#WL__29944F1D] AS [WL]), SEEK:(
                                     |--Index Seek(OBJECT:([CashOffice].[dbo].[PricesRegionHistory].[IX_PricesRegionHistory_G_PriceUSD] AS [P]), SEEK:([P].[RegionID]=[M].[RegionID] AND [P].[WareCode]=[M].[WareCode]),  WHERE:([P].[PriceHistoryDate]>Convert(

(13 row(s) affected)

StmtText                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  	insert into #TmpStock
	select M.WareCode, max( dateadd(d,-1,N.NullDate))
	from #MaxPrDate M
	inner join #NullPrDate N on M.WareCode = N.WareCode and M.RegionID=N.RegionID
	--where N.NullDate>M.MaxDate
	group by M.WareCode
	
/*
	insert into #TmpS

(1 row(s) affected)

StmtText                                                                                                                                                                                                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
       |--Table Insert(OBJECT:([tempdb].[dbo].[#TmpStock___________________________________________________________________________________________________________0000001EA72C]), SET:([#TmpStock].[dt]=[Expr1003], [#TmpStock].[WareCode]=[M].[WareCode]))
            |--Top(ROWCOUNT est 0)
                 |--Compute Scalar(DEFINE:([Expr1003]=Convert([Expr1002])))
                      |--Stream Aggregate(GROUP BY:([M].[WareCode]) DEFINE:([Expr1002]=MAX(dateadd(day, -1, [N].[NullDate]))))
                           |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([M].[WareCode])=([N].[WareCode]), RESIDUAL:([N].[WareCode]=[M].[WareCode] AND [N].[RegionID]=[M].[RegionID]))
                                |--Sort(ORDER BY:([M].[WareCode] ASC))
                                |    |--Table Scan(OBJECT:([tempdb].[dbo].[#MaxPrDate__________________________________________________________________________________________________________0000001EA72C] AS [M]))
                                |--Sort(ORDER BY:([N].[WareCode] ASC))
                                     |--Table Scan(OBJECT:([tempdb].[dbo].[#NullPrDate_________________________________________________________________________________________________________0000001EA72C] AS [N]))

(9 row(s) affected)

StmtText                    
--------------------------- 
  
drop table #MaxPrDate
  
drop table #NullPrDate

(2 row(s) affected)
12 окт 04, 16:41    [1027450]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
Glory
Member

Откуда:
Сообщений: 104764
И это называется "Планы практически одинаковы." ???
С каких это пор Hash Match и Merge Join стали "почти одинаковым" с Nested Loops ??
12 окт 04, 16:51    [1027519]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1016
Ну, согласен, если в текстовом виде - то сильно разнятся.

Я просматриваю то их в графическом представлении, ап там что то не было видно.

Так как же бороть?
12 окт 04, 16:54    [1027537]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Заменить временные таблицы табличными переменными и указать в них ПК.
12 окт 04, 17:00    [1027564]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1016
to Glory
Ну так тут только план для заполнения первой таблицы отличается.
Он же его паралелить хочет!
12 окт 04, 17:18    [1027647]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1016
Так что различий в
Hash Match и Merge Join стали  с Nested Loops
что то не видно!
12 окт 04, 17:21    [1027660]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1016
Ну, кто нибуть подскажет - почему же параллелизм так тормознуто связывает таблицы???
Как можно хинтами указать трубуемый метод связывания???

Наблюдение:
после применения maxdup 1 скрипт вне процедуры тоже стал медленно выполнятся.
13 окт 04, 18:57    [1031448]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
А сами запросы можно полностью увидеть? И описания таблиц, а то в примере только обрывки запросов.

Сколько процессоров на машине, какие?
13 окт 04, 19:07    [1031468]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1016
Вот текст скрипта:

set nocount on
	create table #MaxPrDate
	(WareCode int,RegionID int,MaxDate smalldatetime)

	create table #NullPrDate
	(WareCode int,RegionID int,NullDate smalldatetime)

	insert into #MaxPrDate
	select P.WareCode,P.RegionID,max(P.PriceHistoryDate) from 
	PricesRegionHistory P(NOLOCK)
	inner MERGE join NamesList WL(NOLOCK) on WL.WareCode=P.WareCode
	inner MERGE join NamesList NL(NOLOCK) on WL.WareCode=NL.WareCode and NL.Deleted = 0
	where 
	P.PriceUSD<>0 and P.RegionID<>3000
	group by P.WareCode,P.RegionID
	having max(P.PriceHistoryDate)<= '10/10/2004'

	insert into #NullPrDate
	select P.WareCode,P.RegionID,min(P.PriceHistoryDate) from 
	PricesRegionHistory P(NOLOCK)
	inner join NamesList WL(NOLOCK) on WL.WareCode=P.WareCode
	inner join NamesList NL(NOLOCK) on WL.WareCode=NL.WareCode and NL.Deleted = 0
	inner join #MaxPrDate M on P.WareCode=M.WareCode and  P.RegionID=M.RegionID
	where 
	P.PriceUSD=0 and P.PriceHistoryDate>M.MaxDate
	group by P.WareCode,P.RegionID
	having min(P.PriceHistoryDate)<= '10/10/2004'

	insert into #TmpStock
	select M.WareCode, max( dateadd(d,-1,N.NullDate))
	from #MaxPrDate M
	inner join #NullPrDate N on M.WareCode = N.WareCode and M.RegionID=N.RegionID
	--where N.NullDate>M.MaxDate
	group by M.WareCode
	
/*
	insert into #TmpStock
	select P.WareCode,max(P.PriceHistoryDate) from 
	PricesRegionHistory P(NOLOCK)
	inner join #WL WL on WL.WareCode=P.WareCode
	inner join NamesList NL(NOLOCK) on WL.WareCode=NL.WareCode and NL.Deleted = 0
	where 
	P.PriceUSD<>0
	group by P.WareCode
	having max(P.PriceHistoryDate)<= @DateTo
*/

drop table #MaxPrDate
drop table #NullPrDate


Процедура:

CREATE PROCEDURE [dbo].[KOGetPricesLastDate_test] 
@DateTo datetime

AS
BEGIN
set nocount on
	create table #MaxPrDate
	(WareCode int,RegionID int,MaxDate smalldatetime)

	create table #NullPrDate
	(WareCode int,RegionID int,NullDate smalldatetime)

	insert into #MaxPrDate
	select P.WareCode,P.RegionID,max(P.PriceHistoryDate) from 
	PricesRegionHistory P(NOLOCK)
	inner join NamesList WL on WL.WareCode=P.WareCode
	inner join NamesList NL(NOLOCK) on WL.WareCode=NL.WareCode and NL.Deleted = 0
	where 
	P.PriceUSD<>0 and P.RegionID<>3000
	group by P.WareCode,P.RegionID
	having max(P.PriceHistoryDate)<= @DateTo

	insert into #NullPrDate
	select P.WareCode,P.RegionID,min(P.PriceHistoryDate) from 
	PricesRegionHistory P(NOLOCK)
	inner join NamesList WL on WL.WareCode=P.WareCode
	inner join NamesList NL(NOLOCK) on WL.WareCode=NL.WareCode and NL.Deleted = 0
	inner join #MaxPrDate M on P.WareCode=M.WareCode and  P.RegionID=M.RegionID
	where 
	P.PriceUSD=0 and P.PriceHistoryDate>M.MaxDate
	group by P.WareCode,P.RegionID
	having min(P.PriceHistoryDate)<= @DateTo

	insert into #TmpStock
	select M.WareCode, max( dateadd(d,-1,N.NullDate))
	from #MaxPrDate M
	inner join #NullPrDate N on M.WareCode = N.WareCode and M.RegionID=N.RegionID
	--where N.NullDate>M.MaxDate
	group by M.WareCode
	
/*
	insert into #TmpStock
	select P.WareCode,max(P.PriceHistoryDate) from 
	PricesRegionHistory P(NOLOCK)
	inner join #WL WL on WL.WareCode=P.WareCode
	inner join NamesList NL(NOLOCK) on WL.WareCode=NL.WareCode and NL.Deleted = 0
	where 
	P.PriceUSD<>0
	group by P.WareCode
	having max(P.PriceHistoryDate)<= @DateTo
*/

drop table #MaxPrDate
drop table #NullPrDate
END


GO
13 окт 04, 19:16    [1031487]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
Glory
Member

Откуда:
Сообщений: 104764
having max(P.PriceHistoryDate)<= '10/10/2004'
having max(P.PriceHistoryDate)<= @DateTo


Ну вроде бы уже много раз говорили что при константах и при переменных в запросе планы могут быть совершенно разные. Потому что по значению константы оптимизатор может сделать точный прогноз на число выбираемых записей. Для переменной же оптимизатор исходит из того что ее значение может быть любым.
13 окт 04, 19:37    [1031542]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
PricesRegionHistory P(NOLOCK)
	inner join NamesList WL on WL.WareCode=P.WareCode
	inner join NamesList NL(NOLOCK) on WL.WareCode=NL.WareCode and NL.Deleted = 0

А что означает эта конструкция? В приведенных выше планах использовалась временная таблица #WL.

Поэтому, имхо, планы будут разными.
14 окт 04, 13:34    [1033737]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
Александр Гладченко
Member

Откуда:
Сообщений: 10713
Блог
В тему: https://www.sql.ru/articles/mssql/2004/04101401SQLServerLargeDataOperations.shtml
14 окт 04, 15:07    [1034272]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1016
В этом конкретном случае от связываемых переменных ничего не меняется.
План остается таким же - медленным.
Причем только на первом запросе.

#WL - это тоже самое что и NamesList, который фигурирует во втором примере. Просто не хотелось предварительно делать временную таблицу, а так данные в ней такие же.

Может какими то хинтами можно пользоваться?
14 окт 04, 16:45    [1034708]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1016
Вопрос остается открытым - почему следующий запрос при параллельной обработке работает очень медленно???
План запроса приведен ниже.

	select P.WareCode,P.RegionID,max(P.PriceHistoryDate) from 
	PricesRegionHistory P(NOLOCK)
	inner join NamesList WL on WL.WareCode=P.WareCode
	inner join NamesList NL(NOLOCK) on WL.WareCode=NL.WareCode and NL.Deleted = 0
	where 
	P.PriceUSD<>0 and P.RegionID<>3000
	group by P.WareCode,P.RegionID
	having max(P.PriceHistoryDate)<= @DateTo



План выполнения запроса при использовании параллелизма замедляется в 200 раз (выполнение длится по 20 минут), а без использовании параллелизма или указывая MAXDOP 1 исполниется за 30 секунд!
       |--Table Insert(OBJECT:([tempdb].[dbo].[#MaxPrDate__________________________________________________________________________________________________________0000001EA72C]), SET:([#MaxPrDate].[MaxDate]=[Expr1004], [#MaxPrDate].[RegionID]=[P].[RegionID
            |--Top(ROWCOUNT est 0)
                 |--Compute Scalar(DEFINE:([Expr1004]=Convert([Expr1003])))
                      |--Parallelism(Gather Streams)
                           |--Filter(WHERE:([Expr1003]<=[@DateTo]))
                                |--Hash Match(Inner Join, HASH:([NL].[WareCode])=([P].[WareCode]))
                                     |--Hash Match(Inner Join, HASH:([NL].[WareCode])=([WL].[WareCode]))
                                     |    |--Bitmap(HASH:([NL].[WareCode]), DEFINE:([Bitmap1008]))
                                     |    |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([NL].[WareCode]))
                                     |    |         |--Clustered Index Scan(OBJECT:([CashOffice].[dbo].[NamesList].[PK__NamesList__0E79DF0E] AS [NL]), WHERE:(Convert([NL].[Deleted])=0))
                                     |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([WL].[WareCode]), WHERE:(PROBE([Bitmap1008])=TRUE))
                                     |         |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#WL_________________________________________________________________________________________________________________0000001EA72C].[PK__#WL__29944F1D] AS [WL]))
                                     |--Parallelism(Repartition Streams, PARTITION COLUMNS:([P].[WareCode]))
                                          |--Stream Aggregate(GROUP BY:([P].[RegionID], [P].[WareCode]) DEFINE:([Expr1003]=MAX([P].[PriceHistoryDate])))
                                               |--Parallelism(Repartition Streams, PARTITION COLUMNS:([P].[RegionID], [P].[WareCode]), ORDER BY:([P].[RegionID] ASC, [P].[WareCode] ASC))
                                                    |--Index Seek(OBJECT:([CashOffice].[dbo].[PricesRegionHistory].[IX_PricesRegionHistory_G_PriceUSD] AS [P]), SEEK:([P].[RegionID] < 3000 OR [P].[RegionID] > 3000),  WHERE:([P].[PriceUSD]<0.00 OR [P].[Price

(16 row(s) affected)


Если у кого либо есть соображения как это побороть - пишите.
15 окт 04, 11:41    [1036549]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
а косты наконец-то привести слабо?
В костах ведь можно найти на каком именно участке происходит падение производительности.

какая величина стоит в "cost threshold for parallelism"?

Визуально в плане следующие отличия (без параллелизма)

                                |--Merge Join(Inner Join, MERGE:([NL].[WareCode])=([WL].[WareCode]), RESIDUAL:([WL].[WareCode]=[NL].[WareCode]))
                                |    |--Clustered Index Scan(OBJECT:([CashOffice].[dbo].[NamesList].[PK__NamesList__0E79DF0E] AS [NL]),  WHERE:(Convert([NL].[Deleted])=0) ORDERED FORWARD)
                                |    |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#WL_________________________________________________________________________________________________________________0000001EA72C].[PK__#WL__29944F1D] AS [WL]), ORDERED FOR

С параллелизмом
                                     |--Hash Match(Inner Join, HASH:([NL].[WareCode])=([WL].[WareCode]))
                                     |    |--Bitmap(HASH:([NL].[WareCode]), DEFINE:([Bitmap1008]))
                                     |    |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([NL].[WareCode]))
                                     |    |         |--Clustered Index Scan(OBJECT:([CashOffice].[dbo].[NamesList].[PK__NamesList__0E79DF0E] AS [NL]), WHERE:(Convert([NL].[Deleted])=0))
                                     |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([WL].[WareCode]), WHERE:(PROBE([Bitmap1008])=TRUE))
                                     |         |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#WL_________________________________________________________________________________________________________________0000001EA72C].[PK__#WL__29944F1D] AS [WL]))

Опять же, надо смотреть на косты. Но мне кажется странным, что выборка из временной таблицы "стОит" больше 5 (дефолтное значение для разделения на потоки).

Какая структура таблицы "PricesRegionHistory"? Точнее, какие индексы на ней?
15 окт 04, 19:11    [1038718]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
aleks2
Guest
Писать и пИсать можно запросы...

select P.WareCode,P.RegionID,max(P.PriceHistoryDate) from 
	PricesRegionHistory P(NOLOCK)
	inner join NamesList WL on WL.WareCode=P.WareCode
	inner join NamesList NL(NOLOCK) on WL.WareCode=NL.WareCode and NL.Deleted = 0
	where 
	P.PriceUSD<>0 and P.RegionID<>3000
	group by P.WareCode,P.RegionID
	having max(P.PriceHistoryDate)<= @DateTo

Эквивалентен
select P.WareCode,P.RegionID,max(P.PriceHistoryDate) from 
	PricesRegionHistory P (NOLOCK)
	inner join NamesList WL on WL.WareCode=P.WareCode
	where 
             WL.Deleted = 0 AND
	P.PriceUSD<>0 and P.RegionID<>3000
             P.PriceHistoryDate<= @DateTo
	group by P.WareCode,P.RegionID

Это УСКОРИТ...
-----------
(NOLOCK) я бы тоже выбросил.

и даже написал бы так

select P.WareCode,P.RegionID,max(P.PriceHistoryDate) from 
	PricesRegionHistory P LEFT OUTER JOIN
	NamesList WL on WL.WareCode=P.WareCode
	where 
             WL.Deleted = 0 AND
	P.PriceUSD<>0 and P.RegionID<>3000
             P.PriceHistoryDate<= @DateTo
	group by P.WareCode,P.RegionID
16 окт 04, 11:39    [1039277]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
aleks2
Guest
Впрочем с having я погорячился

select P.WareCode,P.RegionID,max(P.PriceHistoryDate) from 
	PricesRegionHistory P (NOLOCK)
	inner join NamesList WL on WL.WareCode=P.WareCode
	where 
             WL.Deleted = 0 AND
	P.PriceUSD<>0 and P.RegionID<>3000
	group by P.WareCode,P.RegionID
             having max(P.PriceHistoryDate)<= @DateTo
16 окт 04, 11:47    [1039282]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
Gena G.
Member

Откуда: Oz
Сообщений: 977
Crimean
Заменить временные таблицы табличными переменными и указать в них ПК.


А в чем смысл и практическая разница временной таблицы и табличной переменной?

PS BOL прочитал: Use table variables instead of temporary tables, whenever possible. Почему?
16 окт 04, 15:23    [1039418]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1016
to AAron
Акак в текстовом виде выгрузить КОСТЫ?
Скажи - я выгружу.
18 окт 04, 12:15    [1041183]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36695
Gena G.
Crimean
Заменить временные таблицы табличными переменными и указать в них ПК.


А в чем смысл и практическая разница временной таблицы и табличной переменной?

PS BOL прочитал: Use table variables instead of temporary tables, whenever possible. Почему?
Операции с табличной переменной не пишутся в лог. И не откатываются после rollback.
18 окт 04, 12:17    [1041193]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1016
Вот образ таблицы PricesRegionHistory, которой вы спрашивали:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PricesRegionHistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PricesRegionHistory]
GO

CREATE TABLE [dbo].[PricesRegionHistory] (
	[RegionID] [int] NOT NULL ,
	[PriceHistoryDate] [datetime] NOT NULL ,
	[WareCode] [int] NOT NULL ,
	[TimeStamp] [datetime] NOT NULL ,
	[PriceRUB] [money] NULL ,
	[PriceUSD] [money] NULL ,
	[Discount] [bit] NOT NULL ,
	[Expected] [bit] NOT NULL ,
	[BasePrice] [char] (1) COLLATE Cyrillic_General_BIN NOT NULL ,
	[Status] [char] (1) COLLATE Cyrillic_General_BIN NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PricesRegionHistory] WITH NOCHECK ADD 
	CONSTRAINT [PK__PricesHistory__2E90DD8E] PRIMARY KEY  CLUSTERED 
	(
		[RegionID],
		[PriceHistoryDate] DESC ,
		[WareCode]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[PricesRegionHistory] ADD 
	CONSTRAINT [DF_PricesHistory_RegionID] DEFAULT (0) FOR [RegionID],
	CONSTRAINT [DF_PricesHistory_Discount] DEFAULT (1) FOR [Discount],
	CONSTRAINT [DF_PricesRegionHistory_Expected] DEFAULT (0) FOR [Expected],
	CONSTRAINT [DF_PricesRegionHistory_Base] DEFAULT ('Р') FOR [BasePrice],
	CONSTRAINT [DF_PricesRegionHistory_Status] DEFAULT ('@') FOR [Status]
GO

 CREATE  INDEX [IX_PricesHistory_WareCode] ON [dbo].[PricesRegionHistory]([WareCode]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_PricesHistory_PriceHistoryDate] ON [dbo].[PricesRegionHistory]([PriceHistoryDate] DESC ) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_PricesRegionHistory_G_PriceUSD] ON [dbo].[PricesRegionHistory]([RegionID], [WareCode], [PriceHistoryDate], [PriceUSD]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_PriceRegionHistory_RegionWareCode] ON [dbo].[PricesRegionHistory]([RegionID], [WareCode] DESC ) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO
18 окт 04, 12:20    [1041203]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
felix_tm
Member

Откуда:
Сообщений: 4
При небольших испытаниях хранимых процедур было выявлена такая закономерность:

при написании хранимки так:

 create proc xxx @datetime smalldatetime
            as
              ..................

время работы очень большое!!!

а если так

create proc xxx @datetime smalldatetime
            as
          declare @v_datetime smalldatetime
          set @v_datetime=@datetime
              ..................

Время работы хранимки равно или меньше времени работы запроса изьятого из нее .
В теле процедуры использовать только переменную @v_datetime !!!!
18 окт 04, 15:35    [1042313]     Ответить | Цитировать Сообщить модератору
 Re: Шаманство при Paralellism в процедурах.  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1016
И что бы это значило?
18 окт 04, 15:51    [1042407]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить