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

Откуда: Москва
Сообщений: 598
1. Есть таблица изделия
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]

такие индексы у нее проставлены

index_1: clustered unique         [identifier]
index_2: non-clustered non-unique [docID]
index_3: non-clustered non-unique [artikul]
index_4: non-clustered non-unique [proba]
index_5: non-clustered non-unique [razmer] 
index_6: non-clustered non-unique [ves]
index_7: non-clustered unique     [code]


В этой таблице = 7 млн строк.

2. Есть таблица остаток
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]

Индексы:

index_1: clustered unique         [identifier]
index_2: non-clustered unique [docID], [lineID],[identifier] ,[motion]
index_3: non-clustered non-unique [masterID]
index_4: non-clustered non-unique [stateID]
index_5: non-clustered non-unique [docID]
index_6: non-clustered unique     [code]

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

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]

Индексы:
index_1: clustered unique        [FirmID]
index_2: non-clustered unique  [FirmName]
index_3: non-clustered non-unique [inn]
index_4: non-clustered non-unique [manager] 
index_5: non-clustered unique [rowguid]
index_6: non-clustered unique     [code]

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

4. таблица дат

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]

индексы:
index_1: clustered unique  [docID]      
index_2: non-clustered non-unique [docExecuteDate]
index_3: non-clustered non-unique [docCreateDate],[docExecuteDate],[docPrintDate], [docID]
index_4: non-clustered unique     [code]

В этой таблице 550 тыщ. записей.

Есть запрос:

declare @OrgId int, @sklad nvarchar(20), @datein datetime, @proba int
set dateformat dmy
set @OrgId=1
set @sklad='склад10'
set @datein='1/6/2009'
set @proba=585
SELECT 
	o.identifier,
	MAX(1000000*convert(bigint,o.docID)+(case when o.motion=1 
			then o.masterID else 0 end))-1000000*convert(bigint,MAX(o.docID)) as masterid 
from 
	main.dbo.izdelia as izd with (nolock) 
	inner join main.dbo.ostatki as o 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 as docsdate with (nolock) on o.docid=docsdate.docid 
where 
	izd.proba = @proba 
	and docsdate.docexecutedate<@datein 
	and o.identifier not in (select identifier from  general.dbo.inpos with (nolock)) 
	and isnull(o.deleted,0)=0 
	and isnull(o.stateid,-1)<>6 
	and (	(@OrgId = 0 and @sklad = 'склад10' and o.masterid in (99998,88881)	)	--optest
		 or	(@OrgId = 0 and @sklad = 'склад4' and o.masterid in (33,88882)		)	
		 or (@OrgId = 1 and @sklad = 'склад10' and o.masterid in (99999,88888)	)	--td estet
		 or (@OrgId = 1 and @sklad = 'склад4' and o.masterid in (3383,88889)	)	
		 or	(@OrgId = 1 and @sklad = 'демзал' and o.masterid = 3518		)
		 or	(@OrgId = 1 and @sklad = 'демзал10' and o.masterid = 99995	)
		 or (@OrgId = 2 and @sklad = 'перевал' and o.masterid = 283788	)
		)
group by 
	o.identifier, f.globalid
having 
	sum(o.motion)=1 and MAX(1000000*convert(bigint,o.docID)+(case when o.motion=1 
			then o.masterID else 0 end))-1000000*convert(bigint,MAX(o.docID)) <> 0


Этот запрос исполняется очень долго, более 10 минут.. Может индексы не верно проставлены? Индексы ставил не я. Их ставили очень давно. Что можно изменить в запросе и на какие поля индексы лучше проставить.

Версия
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) 
7 авг 09, 17:36    [7512505]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
ну а план где?

для спящего время бодрствования равносильно сну
7 авг 09, 18:22    [7512752]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Что такое f.globalid??
7 авг 09, 18:42    [7512829]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
aleks2
Guest
>>Что можно изменить в запросе
С такими условиями на фильтрацию - только использование временных таблиц спасет отца русской демократии. Сливаем в четыре времянки все согласно фильтрам - потом делаем join.

И почему бы не заполнить нулями поле o.deleted и -1 поле o.stateid?

Условия
isnull(o.deleted,0)=0
isnull(o.stateid,-1)<>6
очень плохие с точки зрения индексов.
7 авг 09, 19:07    [7512913]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
В 2005-м, как ни странно, мне несколько раз помогало OPTION(FORCE ORDER).
Причём в самых неожиданных случаях, когда и JOINов-то было мало, а в 2000-м запрос "летал".
Попробуйте поменять порядок JOINов (в порядке возрастания количества записей в таблицах).
Может, это всё и не поможет, конечно... Пляски с бубном, как говорится.
7 авг 09, 19:14    [7512936]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
Solo_Mes
Member

Откуда: Минск
Сообщений: 247
Золотишком торгуем.
Force order врядли спасет так как проба 585 наверное у половины изделий, а именно эта таблица стоит первой. Пусть сервер сам выберет лучший план.
по моему блок условий:
(@OrgId = 0 and @sklad = 'склад10' and o.masterid in (99998,88881)	)	--optest
		 or	(@OrgId = 0 and @sklad = 'склад4' and o.masterid in (33,88882)		)	
		 or (@OrgId = 1 and @sklad = 'склад10' and o.masterid in (99999,88888)	)	--td estet
		 or (@OrgId = 1 and @sklad = 'склад4' and o.masterid in (3383,88889)	)	
		 or	(@OrgId = 1 and @sklad = 'демзал' and o.masterid = 3518		)
		 or	(@OrgId = 1 and @sklad = 'демзал10' and o.masterid = 99995	)
		 or (@OrgId = 2 and @sklad = 'перевал' and o.masterid = 283788	)
		)
должен порядочно ограничивать выборку. Конечно всё зависит от уникальности поля masterid.
В общем нужен план.
8 авг 09, 00:56    [7513741]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
так я понял. извините за ламерский вопрос, как план запроса сделать в текстовом виде?
8 авг 09, 12:11    [7513973]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Как получить план выполнения запроса в текстовом виде?
8 авг 09, 12:13    [7513976]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
Solo_Mes
Золотишком торгуем.
Force order врядли спасет так как проба 585 наверное у половины изделий, а именно эта таблица стоит первой. Пусть сервер сам выберет лучший план.
по моему блок условий:
(@OrgId = 0 and @sklad = 'склад10' and o.masterid in (99998,88881)	)	--optest
		 or	(@OrgId = 0 and @sklad = 'склад4' and o.masterid in (33,88882)		)	
		 or (@OrgId = 1 and @sklad = 'склад10' and o.masterid in (99999,88888)	)	--td estet
		 or (@OrgId = 1 and @sklad = 'склад4' and o.masterid in (3383,88889)	)	
		 or	(@OrgId = 1 and @sklad = 'демзал' and o.masterid = 3518		)
		 or	(@OrgId = 1 and @sklad = 'демзал10' and o.masterid = 99995	)
		 or (@OrgId = 2 and @sklad = 'перевал' and o.masterid = 283788	)
		)
должен порядочно ограничивать выборку. Конечно всё зависит от уникальности поля masterid.
В общем нужен план.

  |--Compute Scalar(DEFINE:([Expr1016]=[Expr1014]-(1000000)*CONVERT(bigint,[Expr1015],0)))
       |--Parallelism(Gather Streams)
            |--Filter(WHERE:(([Expr1014]-(1000000)*CONVERT(bigint,[Expr1015],0))<>(0) AND [Expr1013]=(1)))
                 |--Compute Scalar(DEFINE:([Expr1013]=CASE WHEN [Expr1046]=(0) THEN NULL ELSE [Expr1047] 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:([Expr1046]=COUNT_BIG([main].[dbo].[ostatki].[motion] as [o].[motion]), [Expr1047]=SUM([main].[dbo].[ostatki].[motion] as [o].[motion]), [Expr1014]=MAX([Expr1031]), [Expr1015]=MAX([main].[dbo].[ostatki].[docID] as [o].[docID])))
                           |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([o].[identifier], [f].[globalID]))
                                |--Hash Match(Right Anti Semi Join, HASH:([General].[dbo].[InPos].[identifier])=([o].[identifier]), RESIDUAL:([main].[dbo].[ostatki].[identifier] as [o].[identifier]=[General].[dbo].[InPos].[identifier]))
                                     |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([General].[dbo].[InPos].[identifier]))
                                     |    |--Table Scan(OBJECT:([General].[dbo].[InPos]))
                                     |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([o].[identifier]))
                                          |--Hash Match(Inner Join, HASH:([f].[FirmID])=([o].[masterID]))
                                               |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([f].[FirmID]))
                                               |    |--Clustered Index Scan(OBJECT:([attrib].[dbo].[firmName].[IX_firmName] AS [f]))
                                               |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([o].[masterID]))
                                                    |--Hash Match(Inner Join, HASH:([izd].[identifier])=([o].[identifier]), RESIDUAL:([main].[dbo].[izdelia].[identifier] as [izd].[identifier]=[main].[dbo].[ostatki].[identifier] as [o].[identifier]))
                                                         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([izd].[identifier]))
                                                         |    |--Index Seek(OBJECT:([main].[dbo].[izdelia].[IX_izdelia_4] AS [izd]), SEEK:([izd].[proba]=[@proba]) ORDERED FORWARD)
                                                         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([o].[identifier]))
                                                              |--Hash Match(Inner Join, HASH:([docsdate].[docID])=([o].[docID]), RESIDUAL:([main].[dbo].[ostatki].[docID] as [o].[docID]=[Mainex].[dbo].[docsDate].[docID] as [docsdate].[docID]))
                                                                   |--Bitmap(HASH:([docsdate].[docID]), DEFINE:([Bitmap1045]))
                                                                   |    |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([docsdate].[docID]))
                                                                   |         |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1043], [Expr1044], [Expr1042]))
                                                                   |              |--Compute Scalar(DEFINE:(([Expr1043],[Expr1044],[Expr1042])=GetRangeWithMismatchedTypes(NULL,[@datein],(10))))
                                                                   |              |    |--Constant Scan
                                                                   |              |--Index Seek(OBJECT:([Mainex].[dbo].[docsDate].[IX_Docexecute] AS [docsdate]), SEEK:([docsdate].[docExecuteDate] > [Expr1043] AND [docsdate].[docExecuteDate] < [Expr1044]) ORDERED FORWARD)
                                                                   |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([o].[docID]), WHERE:(PROBE([Bitmap1045])=TRUE))
                                                                        |--Filter(WHERE:([@OrgId]=(0) AND [@sklad]=N'склад10' AND ([main].[dbo].[ostatki].[masterID] as [o].[masterID]=(88881) OR [main].[dbo].[ostatki].[masterID] as [o].[masterID]=(99998)) OR [@OrgId]=(0) AND [@sklad]=N'склад4' AND ([main].[dbo].[ostatki].[masterID] as [o].[masterID]=(33) OR [main].[dbo].[ostatki].[masterID] as [o].[masterID]=(88882)) OR [@OrgId]=(1) AND [@sklad]=N'склад10' AND ([main].[dbo].[ostatki].[masterID] as [o].[masterID]=(88888) OR [main].[dbo].[ostatki].[masterID] as [o].[masterID]=(99999)) OR [@OrgId]=(1) AND [@sklad]=N'склад4' AND ([main].[dbo].[ostatki].[masterID] as [o].[masterID]=(3383) OR [main].[dbo].[ostatki].[masterID] as [o].[masterID]=(88889)) OR [@OrgId]=(1) AND [@sklad]=N'демзал' AND [main].[dbo].[ostatki].[masterID] as [o].[masterID]=(3518) OR [@OrgId]=(1) AND [@sklad]=N'демзал10' AND [main].[dbo].[ostatki].[masterID] as [o].[masterID]=(99995) OR [@OrgId]=(2) AND [@sklad]=N'перевал' AND [main].[dbo].[ostatki].[masterID] as [o].[masterID]=(283788)))
                                                                             |--Compute Scalar(DEFINE:([Expr1031]=(1000000)*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)))
                                                                                  |--Clustered Index Scan(OBJECT:([main].[dbo].[ostatki].[IX_ostatki] AS [o]), WHERE:(isnull([main].[dbo].[ostatki].[deleted] as [o].[deleted],(0))=(0) AND isnull([main].[dbo].[ostatki].[stateID] as [o].[stateID],(-1))<>(6)))
10 авг 09, 10:15    [7516124]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
aleks2
Guest
Oооо... ешо и Parallelism...

Ну в деле параллельной обработки иногда спасает

select
...
OPTIONS(MAXDOP 1)

А в остальном - безнадежно. Только логику переделывать.
10 авг 09, 11:12    [7516364]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]

Guest
select identifier from general.dbo.inpos with (nolock) - где индексы?
если записей больше чем 50-100 то наверное лучше заменить на left join
10 авг 09, 11:12    [7516365]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]

Guest
а по поводу параллелизма - смотреть динамику выполнения - не "втыкаются" ли параллельные потоки друг в друга
10 авг 09, 11:14    [7516375]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
если записей больше чем 50-100 то наверное лучше заменить на left join
Поподробнее можно об этом?
10 авг 09, 11:18    [7516392]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
select identifier from general.dbo.inpos with (nolock) - где индексы?
если записей больше чем 50-100 то наверное лучше заменить на left join

в этой таблице 12 тыщ строк, там стоит уникальный некластеризованный индекс на счетчике.
10 авг 09, 11:37    [7516469]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
x-x
Member

Откуда:
Сообщений: 230
minya13_85
1. Есть таблица изделия

Почему бы вам не сделать так :
+ 1. вынести ваш хардкод в tempdb и model
use tempdb
go 
create table filter
(  OrgId  tinyint not null
 , Sklad varchar(127) not null
 , Masterid int not null)
 
go
insert into filter(OrgId, Sklad, Masterid)
select 0 , 'склад10' , 99998 union all
select 0 , 'склад10' , 88881 union all
select 0 , 'склад4' , 33 union all
select 0 , 'склад4' , 88882 union all
select 1 , 'склад4' , 3383 union all
select 1 , 'склад4' , 88889 union all
select 1 , 'демзал' , 3518 union all
select 1 , 'демзал10' , 99995 union all
select 2 , 'перевал' , 283788
гораздо проще будет править этот кусок


2. Джойнить эту табличку к селекту запроса с условиям в предикате on по @OrgId и @sklad
3.
aleks2
И почему бы не заполнить нулями поле o.deleted и -1 поле o.stateid?
10 авг 09, 11:41    [7516501]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
запустил Tuning Advisor, по этому запросу. Он мне рекомендовал
1. в таблице изделия удалить индексы с 2 по 6, оставить только 1 и 7 индекс, и создать статистику
CREATE STATISTICS [stat_izdelia] ON [dbo].[izdelia]([proba], [identifier])

2. в таблице остаток , удалить 3, 4, 5 индексы, оставить 1, 2, 6. и создать статистику
CREATE STATISTICS [stat_ostatok] ON [dbo].[ostatki]([identifier], [masterID], [docID])

3. в таблице фирма. Добавить
CREATE NONCLUSTERED INDEX [IX_firma_1] ON [dbo].[firmName] 
(
	[FirmID] ASC,
	[globalID] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

4. Создать вьюшку по таблице изделия
CREATE VIEW [dbo].[_dta_mv_3] WITH SCHEMABINDING
 AS 
SELECT  [dbo].[izdelia].[identifier] as _col_1,  [dbo].[izdelia].[proba] as _col_2,  count_big(*) as _col_3 FROM  [dbo].[izdelia]  
GROUP BY  [dbo].[izdelia].[identifier],  [dbo].[izdelia].[proba]  
накинуть на него индекс:
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
CREATE UNIQUE CLUSTERED INDEX [_dta_index__dta_mv_3_c_7_1044198770__K2_K1] ON [dbo].[_dta_mv_3] 
(
	[_col_2] ASC,
	[_col_1] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

5.в таблице дат удалить 2 и 3 индексы, и добавить:
CREATE NONCLUSTERED INDEX [IX_docsexecutedate] ON [dbo].[docsDate] 
(
	[docExecuteDate] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

Как вы думаете, это оптимальный план? просто пробовать такое, это будет глобальное перестроение..
10 авг 09, 11:50    [7516553]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
x-x
Member

Откуда:
Сообщений: 230
minya13_85
Есть запрос:

declare @OrgId int, @sklad nvarchar(20), @datein datetime, @proba int
set dateformat dmy
set @OrgId=1
set @sklad='склад10'
set @datein='1/6/2009'
set @proba=585
SELECT 
	o.identifier,
	MAX(1000000*convert(bigint,o.docID)+(case when o.motion=1 
			then o.masterID else 0 end))-1000000*convert(bigint,MAX(o.docID)) as masterid 
from 
	main.dbo.izdelia as izd with (nolock) 
	inner join main.dbo.ostatki as o 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 as docsdate with (nolock) on o.docid=docsdate.docid 
where 
	izd.proba = @proba 
	and docsdate.docexecutedate<@datein 
	and o.identifier not in (select identifier from  general.dbo.inpos with (nolock)) 
	and isnull(o.deleted,0)=0 
	and isnull(o.stateid,-1)<>6 
	and (	(@OrgId = 0 and @sklad = 'склад10' and o.masterid in (99998,88881)	)	--optest
		 or	(@OrgId = 0 and @sklad = 'склад4' and o.masterid in (33,88882)		)	
		 or (@OrgId = 1 and @sklad = 'склад10' and o.masterid in (99999,88888)	)	--td estet
		 or (@OrgId = 1 and @sklad = 'склад4' and o.masterid in (3383,88889)	)	
		 or	(@OrgId = 1 and @sklad = 'демзал' and o.masterid = 3518		)
		 or	(@OrgId = 1 and @sklad = 'демзал10' and o.masterid = 99995	)
		 or (@OrgId = 2 and @sklad = 'перевал' and o.masterid = 283788	)
		)
group by 
	o.identifier, f.globalid
having 
	sum(o.motion)=1 and MAX(1000000*convert(bigint,o.docID)+(case when o.motion=1 
			then o.masterID else 0 end))-1000000*convert(bigint,MAX(o.docID)) <> 0


Что такое f.globalid ?
Что такое general.dbo.inpos ? Сколько записей и есть ли индекс по identifier ?

И, знаете, настолько похабное отношение к использованию имен столбцов и таблиц до добра Вас не доведет.
У меня бинарный коллэйшн и я устал у себя править ваши docexecutedate на docExecuteDate и firmid на firmID.
10 авг 09, 11:52    [7516562]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
x-x
Member

Откуда:
Сообщений: 230
minya13_85
Как вы думаете, это оптимальный план? просто пробовать такое, это будет глобальное перестроение..

Плана то нет
Ничего глобального в этом не будет
Других запросов в БД разве нет?
10 авг 09, 11:55    [7516582]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
x-x
minya13_85
Как вы думаете, это оптимальный план? просто пробовать такое, это будет глобальное перестроение..

Плана то нет
Ничего глобального в этом не будет
Других запросов в БД разве нет?
есть и их много..
10 авг 09, 11:56    [7516598]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598

Что такое f.globalid ?
Что такое general.dbo.inpos ? Сколько записей и есть ли индекс по identifier ?

И, знаете, настолько похабное отношение к использованию имен столбцов и таблиц до добра Вас не доведет.
У меня бинарный коллэйшн и я устал у себя править ваши docexecutedate на docExecuteDate и firmid на firmID.

f.globalid, это влияет на уникальность фирмы. но я его не отображаю.
general.dbo.inpos, там 12 тыщ записей, индекса по identifier Нету. есть индекс по полю код (code) это счетчик, индекс некластеризованный уникальный.
10 авг 09, 12:02    [7516641]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
x-x
minya13_85
1. Есть таблица изделия

Почему бы вам не сделать так :
+
+ 1. вынести ваш хардкод в tempdb и model
use tempdb
go 
create table filter
(  OrgId  tinyint not null
 , Sklad varchar(127) not null
 , Masterid int not null)
 
go
insert into filter(OrgId, Sklad, Masterid)
select 0 , 'склад10' , 99998 union all
select 0 , 'склад10' , 88881 union all
select 0 , 'склад4' , 33 union all
select 0 , 'склад4' , 88882 union all
select 1 , 'склад4' , 3383 union all
select 1 , 'склад4' , 88889 union all
select 1 , 'демзал' , 3518 union all
select 1 , 'демзал10' , 99995 union all
select 2 , 'перевал' , 283788
гораздо проще будет править этот кусок


2. Джойнить эту табличку к селекту запроса с условиям в предикате on по @OrgId и @sklad
3.
aleks2
И почему бы не заполнить нулями поле o.deleted и -1 поле o.stateid?


насчет первого пункта, это типа во временку вогнать? а индекс кидать на это? просто ни разу не вносил код в tempbd.
и насчет 3 пункта, если эти поля заполнить -1 и 0, это сильно спасет ситуацию??
10 авг 09, 12:05    [7516666]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2minya13_85
1. временную таблицу как уже посоветовали
и
2. and o.identifier not in (select identifier from general.dbo.inpos with (nolock)) переделать в LEFT JOIN

спасут отца русской демократии.

для спящего время бодрствования равносильно сну
10 авг 09, 12:06    [7516678]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
Алексей2003
2minya13_85
1. временную таблицу как уже посоветовали
и
2. and o.identifier not in (select identifier from general.dbo.inpos with (nolock)) переделать в LEFT JOIN

спасут отца русской демократии.

для спящего время бодрствования равносильно сну

Насчет первого пункта, что именно вогнать во временную таблицу? а то я уже запутался, тут много чего посоветовали.
10 авг 09, 12:10    [7516716]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
x-x
Member

Откуда:
Сообщений: 230
minya13_85
есть и их много..
Так вот учтите, что Вы оптимизируете только один из этих многа. Не сломайте остальное. :)
minya13_85
general.dbo.inpos, там 12 тыщ записей, индекса по identifier Нету
Записей маловато, правда, да и таблицы всей не видно, но индекс по identifier подзапросу должен помочь.
minya13_85
насчет первого пункта, это типа во временку вогнать? а индекс кидать на это? просто ни разу не вносил код в tempbd.
и насчет 3 пункта, если эти поля заполнить -1 и 0, это сильно спасет ситуацию??

Индексы там никакие не нужны, записей десяток. Просто создайте такую табличку в БД model, заполните её и она сама появится в tempdb после рестарта сервера. А поможет или нет - нудо бы посмотреть, может и зря это будет. Но мне так было бы как минимум удобнее, даже если и запросу не поможет.
10 авг 09, 12:13    [7516730]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос.  [new]
x-x
Member

Откуда:
Сообщений: 230
Алексей2003
2. and o.identifier not in (select identifier from general.dbo.inpos with (nolock)) переделать в LEFT JOIN

Позволю дополнить - с добавлением where is not null :)
10 авг 09, 12:14    [7516739]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить