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

Откуда: Москва
Сообщений: 603
 		Вот ссылка на прошлую тему, кому интересно. тут
Также имеются 3 таблицы:

1. Остаток

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]

Индексы:

PK_code: non-clustered unique         [code]
IX_masterID: non-clustered non-unique [masterID]  include columns [identifier],[docid],[motion],[stateID],[deleted]


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


2. Таблица дат

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]

индексы:
PK_docsDate: non-clustered unique     [code]
IX_docExecuteDate: non-clustered non-unique [docExecuteDate] include columns [docID]

1,5 млн записей


3 таблица фирмы:
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_1: non-clustered unique  [FirmName]
PK_firmname: non-clustered unique     [code]

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


Есть запрос вида:

DECLARE  @datein datetime
set dateformat dmy
set @datein='20/9/2013'

DECLARE @tbl TABLE (masterID int)
INSERT INTO @tbl(masterID)
select masterID from dbo.M_SKLAD where OrgID in (1,2) and sklad in ('демзал10','склад10')  



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 
	dbo.ostatki as o with(nolock)
        INNER JOIN dbo.docsdate  with(nolock) on o.docid=docsdate.docid 
	LEFT JOIN dbo.firmname as f with(nolock) on o.masterid=f.firmid 
	LEFT JOIN @tbl as t on t.masterID=o.masterID
where 
	isnull(o.deleted,0)=0 
        and docsdate.docexecutedate < @datein and isnull(o.stateid,-1)<>6 
        and t.masterID is not null
    
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


В временной таблице @tbl содержится ID по которым надо осуществлять поиск. Сделал во временной таблице, т.к. эти ID выбирает пользователь.
Данный запрос выполняется 6 минут, это очень долго.

Если переписать запрос и вместо временной таблицы написать эти ID Как константы т.е.

DECLARE  @datein datetime
set dateformat dmy
set @datein='20/9/2013'

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 
	dbo.ostatki as o with(nolock)
	Inner join dbo.docsdate  with(nolock) on o.docid=docsdate.docid 
	LEFT join dbo.firmname as f with(nolock) on o.masterid=f.firmid 
where 
	isnull(o.deleted,0)=0 
    and docsdate.docexecutedate < @datein and isnull(o.stateid,-1)<>6 
    and o.masterID in (88888,99999,99995)
    
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


то данный запрос выполняется за 19 секунд. Это отличный результат.
Почему когда пытаюсь эти ID Засунуть во временную таблицу а потом их сджойнить, то план выполнения меняется.
Прикрепляю два плана ниже.

К сообщению приложен файл (Планы.rar - 10Kb) cкачать
25 сен 13, 17:22    [14883915]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
 		Как минимум
from 
	dbo.ostatki as o with(nolock)
        INNER JOIN dbo.docsdate  with(nolock) on o.docid=docsdate.docid 
	LEFT JOIN dbo.firmname as f with(nolock) on o.masterid=f.firmid 
	LEFT JOIN @tbl as t on t.masterID=o.masterID
where 
	isnull(o.deleted,0)=0 
        and docsdate.docexecutedate < @datein and isnull(o.stateid,-1)<>6 
        and t.masterID is not null
Заменить на
from
    @tbl t join 
	dbo.ostatki as o with(nolock) on o.masterID = t.masterID
        INNER JOIN dbo.docsdate  with(nolock) on o.docid=docsdate.docid 
	LEFT JOIN dbo.firmname as f with(nolock) on o.masterid=f.firmid 
where 
	isnull(o.deleted,0)=0 
        and docsdate.docexecutedate < @datein and isnull(o.stateid,-1)<>6
25 сен 13, 17:39    [14884000]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
Гость333
Member

Откуда:
Сообщений: 3683
minya13_85
Почему когда пытаюсь эти ID Засунуть во временную таблицу а потом их сджойнить, то план выполнения меняется.

@tbl — это не временная таблица, а табличная переменная.
По табличным переменным, в отличие от временных таблиц, не строится статистика. Количество строк в табличной переменной при построении плана запроса полагается равным 1. Всё это может отрицательно влиять на качество построения плана.
Поэтому попробуйте использовать временную таблицу, т.е. #tbl.
Также неплохо было бы увидеть актуальные планы выполнения запросов.
25 сен 13, 17:52    [14884072]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 603
 		Переделал первый запрос в такой вид:
DECLARE  @datein datetime
set dateformat dmy
set @datein='20/9/2013'

CREATE TABLE #tbl (masterID int)
INSERT INTO #tbl(masterID)
select masterID from Accounting.dbo.M_SKLAD where OrgID in (1,2) and sklad in ('демзал10','склад10')  



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 
    #tbl as t
    LEFT JOIN dbo.ostatki as o with(nolock) on t.masterID=o.masterID
    LEFT join dbo.docsdate  with(nolock) on o.docid=docsdate.docid 
    LEFT join dbo.firmname as f with(nolock) on o.masterid=f.firmid 
	
where 
	isnull(o.deleted,0)=0 
        and docsdate.docexecutedate < @datein 
        and isnull(o.stateid,-1)<>6 
   
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

drop table #tbl


Да, время выполнение запроса уменьшилось. Хотелось бы, чтобы быстрее исполнялось. Может какие индексы еще проставить

Прикрепляю актуальные планы выполнения двух запросов

К сообщению приложен файл (Актуальные планы.rar - 11Kb) cкачать
25 сен 13, 18:13    [14884227]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
minya13_85
Прикрепляю актуальные планы выполнения двух запросов
rar не у всех есть.
25 сен 13, 20:13    [14884590]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
aleks2
Guest
minya13_85
DECLARE @tbl TABLE (masterID int)

Почему когда пытаюсь эти ID Засунуть во временную таблицу а потом их сджойнить, то план выполнения меняется.


Патаму, что оптимизатору надо помогать. А ты гадишь.

Должно так

DECLARE @tbl TABLE (masterID int PRIMARY KEY CLUSTERED)


и наступит щастье...
26 сен 13, 05:57    [14885463]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
aleks2
Guest 		Ну и фигню в запрос не надо лепить

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 
	dbo.ostatki as o with(nolock)
	INNER JOIN @tbl as t on t.masterID=o.masterID
              INNER JOIN dbo.docsdate  with(nolock) on o.docid=docsdate.docid 
	LEFT JOIN dbo.firmname as f with(nolock) on o.masterid=f.firmid 
where 
	isnull(o.deleted,0)=0 
        and docsdate.docexecutedate < @datein and isnull(o.stateid,-1)<>6 
    
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


Я б ишо
	LEFT JOIN dbo.firmname as f with(nolock) on o.masterid=f.firmid
похерил. Т.е. заменил бы 
DECLARE @tbl TABLE (masterID int PRIMARY KEY CLUSTERED, globalid);

update t set globalid = f.globalid
  from @tbl t inner join dbo.firmname as f on t.masterid=f.firmid ;

и убрал бы нафиг лишнее соединение под группировкой.
26 сен 13, 06:06    [14885466]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 603
 		aleks2, Написал запрос как вы сказали:

DECLARE  @datein datetime
set dateformat dmy
set @datein='20/9/2013'

DECLARE @tbl TABLE (masterID int PRIMARY KEY CLUSTERED,globalID int)
INSERT INTO @tbl(masterID,globalID)
select masterID,1 as globalID 
from dbo.M_SKLAD 
where OrgID in (1,2) and sklad in ('демзал10','склад10')  

update t set globalid = f.globalid
from @tbl t inner join TempOst.dbo.firmname as f on t.masterid=f.firmid

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 
    dbo.ostatki as o with(nolock) 
    INNER JOIN @tbl as t on t.masterID=o.masterID
    INNER JOIN dbo.docsdate  with(nolock) on o.docid=docsdate.docid 
where 
	isnull(o.deleted,0)=0 
    and docsdate.docexecutedate < @datein and isnull(o.stateid,-1)<>6 
group by o.identifier, t.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


Выполняется также за 6 минут. Когда вместо табличной переменной, использую именно временную, и меняю местами таблицу dbo.ostatki с этой временной и прописываю везде LEFT JOIN. То запрос выполняется за минуту. Прикрепляю план к выше написанному запросу

К сообщению приложен файл (План.sqlplan - 45Kb) cкачать
26 сен 13, 09:41    [14885859]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
aleks2
Guest 		Хе-хе... знацца чуда не произошло...

Усугубим

DECLARE  @datein datetime
set dateformat dmy
set @datein='20/9/2013'

DECLARE @tbl TABLE (masterID int PRIMARY KEY CLUSTERED, globalID int)
INSERT INTO @tbl(masterID)
select masterID 
from dbo.M_SKLAD 
where OrgID in (1,2) and sklad in ('демзал10','склад10')  

update t set globalid = f.globalid
from @tbl t inner join TempOst.dbo.firmname as f on t.masterid=f.firmid

DECLARE @did TABLE (docid int PRIMARY KEY CLUSTERED);

insert @did
select docid from dbo.docsdate 
where docsdate.docexecutedate < @datein;

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 
    dbo.ostatki as o with(nolock) 
    INNER JOIN @tbl as t on t.masterID = o.masterID
    INNER JOIN @did as did on o.docid = did.docid 
where 
	isnull(o.deleted,0)=0 and o.stateid <> 6 
group by o.identifier, t.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
26 сен 13, 10:40    [14886151]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 603
 		aleks2, Этот запрос вообще не выполняется, уже как 20 минут крутит. Не могу план посмотреть.
Пока Этот запрос быстрее всех предложенных выполняется.
26 сен 13, 11:24    [14886349]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
 		а без having?
26 сен 13, 11:59    [14886547]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
SomewhereSomehow
Member

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

Попробуйте в порядке эксперимента добавить в запрос со временной таблицей такое условие:
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 
    #tbl as t
    LEFT JOIN TempOst.dbo.ostatki as o with(nolock) on t.masterID=o.masterID
	LEFT join TempOst.dbo.docsdate  with(nolock) on o.docid=docsdate.docid 
	LEFT join TempOst.dbo.firmname as f with(nolock) on o.masterid=f.firmid 
	
where 
	isnull(o.deleted,0)=0 
    and docsdate.docexecutedate < @datein and isnull(o.stateid,-1)<>6
    and f.FirmID between 88888 and 99999
    
    
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
26 сен 13, 12:09    [14886610]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 603
 		SomewhereSomehow, когда ставлю условие 
and f.FirmID between 88888 and 99999
выполняется долго, когда ставлю условие 
and t.masterID between 88888 and 99999
, то за 20 секунд. ну видите когда использую константы, он выкручивает очень хорошо.
26 сен 13, 12:31    [14886765]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
SomewhereSomehow
Member

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

Можете актуальные планы и того и другого случая приложить, как в посте выше?
26 сен 13, 12:39    [14886832]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 603
 		SomewhereSomehow, я прилагаю два плана с firmID

К сообщению приложен файл (План c firmID.sqlplan - 83Kb) cкачать
26 сен 13, 12:59    [14886930]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 603
 		SomewhereSomehow, c masterID соответственно

К сообщению приложен файл (План c masterID.sqlplan - 95Kb) cкачать
26 сен 13, 13:00    [14886934]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
pio777
Member

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

А если попробовать добавить индекс на временную таблицу
CREATE TABLE #tbl (masterID int PRIMARY KEY)
26 сен 13, 13:18    [14887053]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
SomewhereSomehow
Member

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

Есть подозрение, что у вас в таблице [ostatki], значения по masterID сильно неравномерные. Возможно это не учитывается в статистике, попробуйте для начала сделать полное обновление статистики с полным сканированием:
update statistics [TempOst].[dbo].[ostatki] with fullscan;

Возможно это не спасет. Тут уже предлагали избавиться от соединения с firmname, только использовалась табличная переменная, можно попробовать сделать тоже самое со временной таблицей.
Типа такого:
CREATE TABLE #tbl (masterID int primary key, globalid int)
INSERT INTO #tbl(masterID)
select distinct
	a.masterID,
	f.globalid
from 
	Accounting.dbo.M_SKLAD a
	LEFT join dbo.firmname as f with(nolock) on a.masterid = f.firmid
where 
	OrgID in (1,2) and 
	sklad in ('демзал10','склад10')


В качестве последней меры, в порядке эксперимента, попробуйте сузить тип соединения до хэш-джойна option(hash join) - но это тоже может не помочь, т.к. может получиться план похожий на планы из первых постов.
В общем, причина примерно понятна, но чтобы добиться желаемого эффекта нужно поэкспериментировать.
26 сен 13, 13:35    [14887195]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
 		SomewhereSomehow,
Опечатался.
INSERT INTO #tbl(masterID, globalid)
26 сен 13, 13:37    [14887216]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 603
 		SomewhereSomehow,
Сделал 
update statistics [TempOst].[dbo].[ostatki] with fullscan
15 минут обновлял он статистику. Потом запустил данный запрос: 
DECLARE  @datein datetime
set dateformat dmy
set @datein='20/9/2013'

CREATE TABLE #tbl (masterID int PRIMARY KEY,globalID int)
INSERT INTO #tbl(masterID,globalID)
select masterID,f.globalID 
from Accounting.dbo.M_SKLAD as s
LEFT join dbo.firmname as f with(nolock) on s.masterid = f.firmid
where s.OrgID in (1,2) and s.sklad in ('демзал10','склад10')  



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 
    #tbl as t
    LEFT JOIN dbo.ostatki as o with(nolock) on t.masterID=o.masterID
    LEFT join dbo.docsdate  with(nolock) on o.docid=docsdate.docid 
    
	
where 
	isnull(o.deleted,0)=0 
    and docsdate.docexecutedate < @datein 
    and isnull(o.stateid,-1)<>6 
    
group by o.identifier, t.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

drop table #tbl


Выполняется 1,5 минуты, чуть дольше чем обычно. Актуальный план прилагаю.

К сообщению приложен файл (План.sqlplan - 72Kb) cкачать
26 сен 13, 14:05    [14887408]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
SomewhereSomehow
Member

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

Ну я так понял уже лучше чем 6 минут?
Теперь, если поменять
DECLARE  @datein datetime 
--на 
DECLARE  @datein smalldatetime --у вас ведь колонка docsDate.docExecuteDate - smalldatetime?

И добавить в конец запроса option(recompile).
И еще выполните все-таки запрос с принудительным hash join, если он сам не догадается, т.е. option(recompile, hash join).
26 сен 13, 14:24    [14887577]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 603
 		SomewhereSomehow,
Вообщем ситуация такая:
Создал я дополнительный индекс не кластерный на таблицу dbo.ostatki:
IX_del non-clustered non-unique [deleted], [stateID] include columns [identifier],[docid],[motion],[masterID]


Убрал из условия where все isnull.
в конце запроса добавил OPTION (HASH JOIN)

в Итоге получил:
DECLARE  @datein smalldatetime
set dateformat dmy
set @datein='20/9/2013'

CREATE TABLE #tbl (masterID int PRIMARY KEY,globalID int)
INSERT INTO #tbl(masterID,globalID)
select masterID,f.globalID 
from Accounting.dbo.M_SKLAD as s
LEFT join dbo.firmname as f with(nolock) on s.masterid = f.firmid
where s.OrgID in (1,2) and s.sklad in ('демзал10','склад10')  

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 
    #tbl as t
    LEFT JOIN dbo.ostatki as o with(nolock) on t.masterID=o.masterID 
    LEFT join dbo.docsdate  with(nolock) on o.docid=docsdate.docid 
where 
	o.deleted=0 
    and docsdate.docexecutedate < @datein 
    and o.stateid<>6 
    
group by o.identifier, t.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
OPTION (HASH JOIN)
drop table #tbl


Запрос выполняется за 40 секунд. Это уже отличный результат. План прилагаю

К сообщению приложен файл (План.sqlplan - 72Kb) cкачать
26 сен 13, 14:43    [14887751]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
SomewhereSomehow
Member

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

А без хинта hash join так и продолжает лепить Nested Loops? Если есть возможность манипулировать индексами, и индекс IX_masterID не нужен каким-то другим запросам, то может быть вообще его убрать? И расходы накладные снизятся и у оптимизатора пропадет резон рассматривать план со вложенными циклами и поиском по masterid, тогда может быть и хинт можно будет убрать?

В любом случае, этот план уже лучше чем был, исчез проблемный оператор и план в части соединения с тяжелой таблицей уже похож на ваш первоначальный план с константами.
26 сен 13, 15:01    [14887923]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 603
 		SomewhereSomehow,
Действительно удалил индекс по masterID, и в конце убрал OPTION (HASH JOIN). Выполняет уже за 35 секунд.
План прилагаю.

К сообщению приложен файл (План.sqlplan - 72Kb) cкачать
26 сен 13, 15:19    [14888050]     Ответить | Цитировать Сообщить модератору
 Re: Хочу продолжить начатую очень давно тему. Оптимизация запроса  [new]
SomewhereSomehow
Member

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

Можете еще в качестве эксперимента добавить option(recompile), чтобы у вас по [TempOst].[dbo].[docsDate].[IX_docExecuteDate] оценка поправилась, а то сейчас там из-за переменной просто 30% от таблицы берется, т.е. оценка 430 832 строк вместо реальных 1 432 959, т.е в три раза. Хотя, обычно проблемы начинаются, когда разница на порядок. Однако, бывают случаи когда достаточно ошибки в сто строк, чтобы сервер начал лить на диск или отказался от локальной-глобальной агрегации (хотя тут она явно не сильно помогла бы) или еще какой оптимизации. В любом случае, попробовать можно, если нет - уберете.
Самое главное, мы избавились от основного тормоза - проблемного оператора параллелизма.
26 сен 13, 15:31    [14888123]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl→      все
Все форумы / Microsoft SQL Server Ответить