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

Откуда: Москва
Сообщений: 606
Вопрос уже раннее обсуждался.
DECLARE @datein datetime
set dateformat dmy
set @datein = '1.09.2011'

SELECT 
    o.identifier,
	MAX(1000000000*convert(bigint,o.docID)+(case when o.motion=1 
			then o.masterID else 0 end))-1000000000*convert(bigint,MAX(o.docID)) as masterid 
from 
	Main.dbo.ostatki as o with(nolock) 
	INNER JOIN Main.dbo.izdelia as izd with(nolock) on izd.identifier=o.identifier
	INNER join Attrib.dbo.firmname as f with(nolock) on o.masterid=f.firmid 
	Inner join Mainex.dbo.docsdate with(nolock) on o.docid=docsdate.docid
where 
	o.deleted=0 and o.stateid<>6 and o.masterid in (99999,88888)
	and docsdate.docexecutedate < @datein and izd.proba in (585,750) and izd.makerID=1273
group by o.identifier, f.globalid
having 
	sum(o.motion)=1 
	and MAX(1000000000*convert(bigint,o.docID)+(case when o.motion=1 then o.masterID else 0 end))
		-1000000000*convert(bigint,MAX(o.docID)) <> 0


Этот запрос возвращает 290 тысяч записей.

Таблицы:
CREATE TABLE [dbo].[ostatki](
	[code] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[identifier] [nvarchar](20) NOT NULL,
	[docID] [int] NULL,
	[motion] [smallint] NULL,
	[lineID] [smallint] NULL,
	[skladID] [smallint] NULL,
	[masterID] [int] NULL,
	[stateID] [smallint] NULL,
	[dateOut] [smalldatetime] NULL,
	[deleted] [smallint] NULL,
 CONSTRAINT [PK_ostatki] PRIMARY KEY NONCLUSTERED 
(
	[code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 99) ON [PRIMARY],
 CONSTRAINT [IX_ostatki_1] UNIQUE NONCLUSTERED 
(
	[docID] ASC,
	[identifier] ASC,
	[lineID] ASC,
	[motion] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY]

Индексы:

<IX_izd_for_sklad> non-clustered non-unique: [masterID]; include: identifier,docid,motion,stateID,deleted
IX_ostatki clustered non-unique: [identifier]
IX_ostatki_1 non-clustered,unique: docid,identifier,lineID,motion
PK_ostatki non-clustered,unique: code

в этой таблице 57 млн записей.


CREATE TABLE [dbo].[izdelia](
	[code] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[dateOut] [smalldatetime] NULL,
	[identifier] [nvarchar](20) NOT NULL,
	[artikul] [nvarchar](50) NULL,
	[proba] [smallint] NULL,
	[vstavki] [smallint] NULL,
	[vesVstavki] [real] NULL,
	[razmer] [real] NULL,
	[ves] [real] NULL,
	[vesOkrugl] [real] NULL,
	[vozvrat] [smallint] NULL,
	[docID] [int] NULL,
	[lineID] [smallint] NULL,
	[isScanCode] [smallint] NULL,
	[innerartikul] [nvarchar](50) NULL,
 CONSTRAINT [PK_izdelia] PRIMARY KEY NONCLUSTERED 
(
	[code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY]

Индексы
IX_index_ark_price non-clustered non-unique: Artikul, Proba; include:identifier, ves
IX_izdeliaclustered,unique: identifier
IX_izdelia_makerID non-clustered,non-unique: masterID,Proba; include: identifier
IX_izdelia_proba_artikuls non-clustered,non-unique: OwnerID; include:identifier,Artikul,Proba
PK_izdelia non-clustered,unique: code

в Этой таблице 11 млн записей


CREATE TABLE [dbo].[firmName](
	[code] [int] IDENTITY(1,1) NOT NULL,
	[FirmName] [nvarchar](50) NOT NULL,
	[inn] [nvarchar](15) NULL,
	[manager] [nvarchar](50) NULL,
	[id_manager] [smallint] NULL,
	[FirmID] [int] NOT NULL,
	[CityID] [int] NULL,
	[RegionID] [int] NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [MSmerge_df_rowguid_BD8D48BF821A49AE8966796611B4373A]  DEFAULT (newsequentialid()),
 CONSTRAINT [PK_firmName] PRIMARY KEY NONCLUSTERED 
(
	[code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 99) ON [PRIMARY],
 CONSTRAINT [IX_firmName_1] UNIQUE NONCLUSTERED 
(
	[FirmName] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY]

Индексы:
IX_firmName clustered unique:        [FirmID]
IX_firmName_1 non-clustered unique:  [FirmName]
PK_firmName non-clustered unique: [code]

в этой таблице 8 тыщ записей


CREATE TABLE [dbo].[docsDate](
	[docCreateDate] [smalldatetime] NULL,
	[docExecuteDate] [smalldatetime] NULL,
	[docPrintDate] [smalldatetime] NULL,
	[docID] [int] NULL,
	[code] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	
 CONSTRAINT [PK_docsDate] PRIMARY KEY NONCLUSTERED 
(
	[code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY]

индексы:
IX_doc_date non-clustered non-unique docExecuteDate; include: [docID]      
IX_docsDate clustered unique: [docid]
PK_docsDate non-clustered unique: code


В этой таблице 1 млн записей.


Вот план выполнения запроса в текстовом виде:
  |--Compute Scalar(DEFINE:([Expr1013]=[Expr1011]-(1000000000)*CONVERT(bigint,[Expr1012],0)))
       |--Parallelism(Gather Streams)
            |--Filter(WHERE:(([Expr1011]-(1000000000)*CONVERT(bigint,[Expr1012],0))<>(0) AND [Expr1010]=(1)))
                 |--Compute Scalar(DEFINE:([Expr1010]=CASE WHEN [Expr1034]=(0) THEN NULL ELSE [Expr1035] END))
                      |--Hash Match(Aggregate, HASH:([o].[identifier], [f].[globalID]), RESIDUAL:([Main].[dbo].[ostatki].[identifier] as [o].[identifier] = [Main].[dbo].[ostatki].[identifier] as [o].[identifier] AND [Attrib].[dbo].[firmName].[globalID] as [f].[globalID] = [Attrib].[dbo].[firmName].[globalID] as [f].[globalID]) DEFINE:([Expr1034]=COUNT_BIG([Main].[dbo].[ostatki].[motion] as [o].[motion]), [Expr1035]=SUM([Main].[dbo].[ostatki].[motion] as [o].[motion]), [Expr1011]=MAX([Expr1014]), [Expr1012]=MAX([Main].[dbo].[ostatki].[docID] as [o].[docID])))
                           |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([o].[identifier], [f].[globalID]))
                                |--Hash Match(Inner Join, HASH:([o].[identifier])=([izd].[identifier]), RESIDUAL:([Main].[dbo].[ostatki].[identifier] as [o].[identifier]=[Main].[dbo].[izdelia].[identifier] as [izd].[identifier]))
                                     |--Bitmap(HASH:([o].[identifier]), DEFINE:([Bitmap1033]))
                                     |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([o].[identifier]))
                                     |         |--Hash Match(Inner Join, HASH:([f].[FirmID])=([o].[masterID]))
                                     |              |--Parallelism(Distribute Streams, Broadcast Partitioning)
                                     |              |    |--Clustered Index Seek(OBJECT:([Attrib].[dbo].[firmName].[IX_firmName] AS [f]), SEEK:([f].[FirmID] >= (88888) AND [f].[FirmID] <= (99999)) ORDERED FORWARD)
                                     |              |--Hash Match(Inner Join, HASH:([Mainex].[dbo].[docsDate].[docID])=([o].[docID]), RESIDUAL:([Main].[dbo].[ostatki].[docID] as [o].[docID]=[Mainex].[dbo].[docsDate].[docID]))
                                     |                   |--Bitmap(HASH:([Mainex].[dbo].[docsDate].[docID]), DEFINE:([Opt_Bitmap1021]))
                                     |                   |    |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([Mainex].[dbo].[docsDate].[docID]))
                                     |                   |         |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1030], [Expr1031], [Expr1029]))
                                     |                   |              |--Compute Scalar(DEFINE:(([Expr1030],[Expr1031],[Expr1029])=GetRangeWithMismatchedTypes(NULL,[@datein],(10))))
                                     |                   |              |    |--Constant Scan
                                     |                   |              |--Index Seek(OBJECT:([Mainex].[dbo].[docsDate].[IX_doc_date]), SEEK:([Mainex].[dbo].[docsDate].[docExecuteDate] > [Expr1030] AND [Mainex].[dbo].[docsDate].[docExecuteDate] < [Expr1031]) ORDERED FORWARD)
                                     |                   |--Compute Scalar(DEFINE:([Expr1014]=(1000000000)*CONVERT(bigint,[Main].[dbo].[ostatki].[docID] as [o].[docID],0)+CONVERT_IMPLICIT(bigint,CASE WHEN [Main].[dbo].[ostatki].[motion] as [o].[motion]=(1) THEN [Main].[dbo].[ostatki].[masterID] as [o].[masterID] ELSE (0) END,0)))
                                     |                        |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([o].[docID]))
                                     |                             |--Index Seek(OBJECT:([Main].[dbo].[ostatki].[<IX_izd_for_sklad>] AS [o]), SEEK:([o].[masterID]=(88888) OR [o].[masterID]=(99999)),  WHERE:([Main].[dbo].[ostatki].[deleted] as [o].[deleted]=(0) AND ([Main].[dbo].[ostatki].[stateID] as [o].[stateID]<(6) OR [Main].[dbo].[ostatki].[stateID] as [o].[stateID]>(6)) AND PROBE([Opt_Bitmap1021],[Main].[dbo].[ostatki].[docID] as [o].[docID],N'[IN ROW]')) ORDERED FORWARD)
                                     |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([izd].[identifier]))
                                          |--Index Seek(OBJECT:([Main].[dbo].[izdelia].[IX_izdelia_makerID] AS [izd]), SEEK:([izd].[proba]=(585) AND [izd].[makerID]=(1273) OR [izd].[proba]=(750) AND [izd].[makerID]=(1273)),  WHERE:(PROBE([Bitmap1033],[Main].[dbo].[izdelia].[identifier] as [izd].[identifier])) ORDERED FORWARD)




Этот запрос Выполняется около 2-х минут. Это очень долго. Это только для одного MakerID=1273, а если пользователь задаст несколько MakerID, то запрос выполняется и того дольше. Подскажите пожалуйста, может индексы не правильно проставлены проставлены. В плане Аналайзер не говорит что какие-то индексы пропущены.Если смотреть по плану, то 46% времени идет на Index Seek <IX_izd_for_sklad> в таблице остатки, может оттуда надо исключить Include? Или что посоветуете?
26 дек 11, 10:46    [11824614]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
minya13_85
Member

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

К сообщению приложен файл (Execution plan.rar - 8Kb) cкачать
26 дек 11, 10:48    [11824633]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
minya13_85
Этот запрос возвращает 290 тысяч записей.


Может причина на поверхности?
26 дек 11, 13:37    [11826258]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 606
aleks2
minya13_85
Этот запрос возвращает 290 тысяч записей.


Может причина на поверхности?

Если смотреть по плану, то 46% времени идет на Index Seek <IX_izd_for_sklad>

Это нормально, что половину времени забирает этот Индекс?
26 дек 11, 13:51    [11826366]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4539
minya13_85
aleks2
пропущено...


Может причина на поверхности?

Если смотреть по плану, то 46% времени идет на Index Seek <IX_izd_for_sklad>

Это нормально, что половину времени забирает этот Индекс?

А кто сказал что это время?
26 дек 11, 14:45    [11826881]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 606
buser
minya13_85
пропущено...

Если смотреть по плану, то 46% времени идет на Index Seek <IX_izd_for_sklad>

Это нормально, что половину времени забирает этот Индекс?

А кто сказал что это время?

А что это?? я думал это 46% времени занимает Index Seek. По-моему это время.
26 дек 11, 15:30    [11827316]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Jovanny
Member

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

А посмотреть бы вывод при SET STATISTICS TIME ON и SET STATISTICS IO ON.
26 дек 11, 16:26    [11827799]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
minya13_85
А что это?? я думал это 46% времени занимает Index Seek. По-моему это время.
В плане всю жизнь была стоимость.
26 дек 11, 16:44    [11827993]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
minya13_85,

1. Значение @datein = '1.09.2011' серверу неизвестно, поэтому он делает предположение пальцем в небо и определяет что будет 300 тыщ строк вместо почти миллиона. Что даёт 3-х кратную ошибку. Поменяйте переменную на константу, или OPTIMIZE FOR или сделайте как параметр в процедуре (но тогда возможен побочный эффект от parameter sniffing).
2. Таблица ostatki, вместо 22 предполагаемых получаем 10 миллионов скорее всего это следствие неправильных estimates с docsDate.
3. Дальше еще круче, после JOIN с firmname выходит 21 миллион vs 360 тыщ. Скорее всего старая или плохая статистика, или неравномерное распределение. Поскольку фильтров по firmname я не вижу можно попробовать заменить INNER JOIN с firmname на LEFT JOIN, при условии что есть FK. Результат должен получится одинаковый, а оценки лучше. И вообще не совсем понятно зачем нужна эта таблица и почему в GROUP BY есть f.globalid если его нет в SELECT?
4. Как результат имеем как минимум два HASH MATCH-а с жуткими оценками => выделенной памяти не хватает => spill to tempdb 1.5 гигабайт с последующими сортировками. Посмотрите на internal_objects_alloc_page_count из sys.dm_db_task_space_usage, я думаю там все будет видно или hash warnings в профайлере или в дефолтном трейсе.

5.
minya13_85
Это только для одного MakerID=1273,

А вы видели какая селективность у этого MakerID?? 5.5 мильёнов из 10 в таблице izdelia. И вы хотите чтобы оно за 5 сек работало? Ни одного селективного условия и жуткие estimates, как следствие читается по 50% из каждой таблицы и потом все это сортируется на диске в tempdb, я думаю 2 минуты это еще хорошо.
27 дек 11, 15:27    [11833698]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить