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

Откуда: Россия, Москва
Сообщений: 116
Доброго времени суток.
Не могу понять почему при добавлении order by вывод уходит в ступор (не дождался > 5 мин)
Если без order by, то вывод за 9 сек.
Если есть мысли, буду рад услышать их.
Также буду признателен за литературу по оптимизации БД и запросов.
select *
from tempdb.dbo.t1 sc
inner join t2 sbc on sbc.name = sc.Supplier collate SQL_Latin1_General_CP1251_CI_AS
inner join t3 sdp on sdp.nbrandid = sbc.nbrandid and sdp.code = sc.material collate SQL_Latin1_General_CP1251_CI_AS
inner join t4 sgg on sgg.Name = sc.PriceGroup collate SQL_Latin1_General_CP1251_CI_AS
order by sdp.nBrandID, sdp.Code
t1 - таблица содержит порядка 140 000 записей (кластерный индекс по полую ID и некластерный по Supplier и Material)
t2 - порядка 300 записей
t3 - порядка 500 000 позиций (кластерный индекс по nBrandID и некластерный по nBrandID и Code)
t4 - 4 строчки
10 ноя 09, 13:35    [7907558]     Ответить | Цитировать Сообщить модератору
 Re: select + дальнейший order by  [new]
sPaul
Member

Откуда: Moscow
Сообщений: 387
Сравните планы запросов, без и с order by.
10 ноя 09, 13:47    [7907652]     Ответить | Цитировать Сообщить модератору
 Re: select + дальнейший order by  [new]
_Виктор_
Member

Откуда: Россия, Москва
Сообщений: 116
отличия основные, что у плана без order by hash match 23%, с order by там nested loops 18%
10 ноя 09, 14:04    [7907790]     Ответить | Цитировать Сообщить модератору
 Re: select + дальнейший order by  [new]
iljy
Member

Откуда:
Сообщений: 8711
_Виктор_,
попробуйте option(hash join). Но вообще это может говорить о плохой статистике и отсутствии нужных индексов.
10 ноя 09, 14:09    [7907815]     Ответить | Цитировать Сообщить модератору
 Re: select + дальнейший order by  [new]
_Виктор_
Member

Откуда: Россия, Москва
Сообщений: 116
опция hash join помогла (28 сек), спасибо.
Но хотелось бы понять каких индексов не хватает... прогонял через Engine Tuning Advisor - он молчит.
10 ноя 09, 14:32    [7907996]     Ответить | Цитировать Сообщить модератору
 Re: select + дальнейший order by  [new]
iljy
Member

Откуда:
Сообщений: 8711
_Виктор_,
план покажите
10 ноя 09, 14:36    [7908032]     Ответить | Цитировать Сообщить модератору
 Re: select + дальнейший order by  [new]
_Виктор_
Member

Откуда: Россия, Москва
Сообщений: 116
в каком виде лучше выложить?
10 ноя 09, 14:44    [7908104]     Ответить | Цитировать Сообщить модератору
 Re: select + дальнейший order by  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
_Виктор_
в каком виде лучше выложить?
В текстовом, конечно - https://www.sql.ru/faq/faq_topic.aspx?fid=393
10 ноя 09, 14:53    [7908170]     Ответить | Цитировать Сообщить модератору
 Re: select + дальнейший order by  [new]
_Виктор_
Member

Откуда: Россия, Москва
Сообщений: 116
без order by
 select *   from (select sdp.nBrandID, sdp.Code, sc.GMPart, sc.DescriptionRussian, sc.DescriptionEngish, sc.[DealerPrice RUB], sgg.nGroupID     from tempdb.dbo.gmdat_20091106 sc     inner join spares_brand_catalog sbc on sbc.name = sc.Supplier collate SQL_Latin1_General_CP1251_CI_AS     inner join Spares_Dealers_Price sdp on sdp.nbrandid = sbc.nbrandid and sdp.code = sc.material collate SQL_Latin1_General_CP1251_CI_AS     inner join Spares_Goods_Group_From_Supplier sgg on sgg.Name = sc.PriceGroup collate SQL_Latin1_General_CP1251_CI_AS     ) T   inner join spares_dealers_price sdp2 on t.nBrandID = sdp2.nBrandID and t.Code = sdp2.Code
  |--Hash Match(Inner Join, HASH:([Expr1010])=([sgg].[Name]), RESIDUAL:([Public_Net].[dbo].[Spares_Goods_Group_From_Supplier].[Name] as [sgg].[Name]=[Expr1010]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([sdp].[nBrandID], [Expr1011]) OPTIMIZED)
       |    |--Hash Match(Inner Join, HASH:([sdp].[code], [sbc].[Name])=([Expr1011], [Expr1012]), RESIDUAL:([Public_Net].[dbo].[Spares_Dealers_Price].[code] as [sdp].[code]=[Expr1011] AND [Public_Net].[dbo].[Spares_Brand_Catalog].[Name] as [sbc].[Name]=[Expr1012]))
       |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([sbc].[nBrandID], [Expr1013]) OPTIMIZED WITH UNORDERED PREFETCH)
       |    |    |    |--Index Scan(OBJECT:([Public_Net].[dbo].[Spares_Brand_Catalog].[IX_Spares_Brand_Catalog] AS [sbc]))
       |    |    |    |--Index Seek(OBJECT:([Public_Net].[dbo].[Spares_Dealers_Price].[_dta_index_Spares_Dealers_Price_7_192117358__K1_K2] AS [sdp]), SEEK:([sdp].[nBrandID]=[Public_Net].[dbo].[Spares_Brand_Catalog].[nBrandID] as [sbc].[nBrandID]) ORDERED FORWARD)
       |    |    |--Compute Scalar(DEFINE:([Expr1010]=CONVERT(nvarchar(255),[tempdb].[dbo].[gmdat_20091106].[PriceGroup] as [sc].[PriceGroup],0), [Expr1011]=CONVERT(nvarchar(50),[tempdb].[dbo].[gmdat_20091106].[Material] as [sc].[Material],0), [Expr1012]=CONVERT(nvarchar(50),[tempdb].[dbo].[gmdat_20091106].[Supplier] as [sc].[Supplier],0)))
       |    |         |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[gmdat_20091106].[PK_gmdat_20091106] AS [sc]))
       |    |--Clustered Index Seek(OBJECT:([Public_Net].[dbo].[Spares_Dealers_Price].[PK_Spares_Dealers_Price] AS [sdp2]), SEEK:([sdp2].[nBrandID]=[Public_Net].[dbo].[Spares_Dealers_Price].[nBrandID] as [sdp].[nBrandID] AND [sdp2].[code]=[Expr1011]) ORDERED FORWARD)
       |--Clustered Index Scan(OBJECT:([Public_Net].[dbo].[Spares_Goods_Group_From_Supplier].[PK_Spares_Goods_Group_From_Supplier] AS [sgg]))

с order by
 select *   from (select sdp.nBrandID, sdp.Code, sc.GMPart, sc.DescriptionRussian, sc.DescriptionEngish, sc.[DealerPrice RUB], sgg.nGroupID     from tempdb.dbo.gmdat_20091106 sc     inner join spares_brand_catalog sbc on sbc.name = sc.Supplier collate SQL_Latin1_General_CP1251_CI_AS     inner join Spares_Dealers_Price sdp on sdp.nbrandid = sbc.nbrandid and sdp.code = sc.material collate SQL_Latin1_General_CP1251_CI_AS     inner join Spares_Goods_Group_From_Supplier sgg on sgg.Name = sc.PriceGroup collate SQL_Latin1_General_CP1251_CI_AS     ) T   inner join spares_dealers_price sdp2 on t.nBrandID = sdp2.nBrandID and t.Code = sdp2.Code   order by t.nBrandID, t.code
  |--Nested Loops(Inner Join, OUTER REFERENCES:([sdp2].[nBrandID], [Expr1011]))
       |--Nested Loops(Inner Join, WHERE:([Public_Net].[dbo].[Spares_Goods_Group_From_Supplier].[Name] as [sgg].[Name]=[Expr1010]))
       |    |--Nested Loops(Inner Join, WHERE:([Public_Net].[dbo].[Spares_Dealers_Price].[code] as [sdp2].[code]=[Expr1011] AND [Public_Net].[dbo].[Spares_Brand_Catalog].[Name] as [sbc].[Name]=[Expr1012]))
       |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([sbc].[nBrandID], [Expr1013]) WITH ORDERED PREFETCH)
       |    |    |    |--Clustered Index Scan(OBJECT:([Public_Net].[dbo].[Spares_Brand_Catalog].[PK_Spares_Brand_Catalog] AS [sbc]), ORDERED FORWARD)
       |    |    |    |--Clustered Index Seek(OBJECT:([Public_Net].[dbo].[Spares_Dealers_Price].[PK_Spares_Dealers_Price] AS [sdp2]), SEEK:([sdp2].[nBrandID]=[Public_Net].[dbo].[Spares_Brand_Catalog].[nBrandID] as [sbc].[nBrandID]) ORDERED FORWARD)
       |    |    |--Compute Scalar(DEFINE:([Expr1010]=CONVERT(nvarchar(255),[tempdb].[dbo].[gmdat_20091106].[PriceGroup] as [sc].[PriceGroup],0), [Expr1011]=CONVERT(nvarchar(50),[tempdb].[dbo].[gmdat_20091106].[Material] as [sc].[Material],0), [Expr1012]=CONVERT(nvarchar(50),[tempdb].[dbo].[gmdat_20091106].[Supplier] as [sc].[Supplier],0)))
       |    |         |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[gmdat_20091106].[PK_gmdat_20091106] AS [sc]))
       |    |--Clustered Index Scan(OBJECT:([Public_Net].[dbo].[Spares_Goods_Group_From_Supplier].[PK_Spares_Goods_Group_From_Supplier] AS [sgg]))
       |--Index Seek(OBJECT:([Public_Net].[dbo].[Spares_Dealers_Price].[_dta_index_Spares_Dealers_Price_7_192117358__K1_K2] AS [sdp]), SEEK:([sdp].[nBrandID]=[Public_Net].[dbo].[Spares_Dealers_Price].[nBrandID] as [sdp2].[nBrandID] AND [sdp].[code]=[Expr1011]) ORDERED FORWARD)
10 ноя 09, 15:42    [7908536]     Ответить | Цитировать Сообщить модератору
 Re: select + дальнейший order by  [new]
iljy
Member

Откуда:
Сообщений: 8711
_Виктор_,

и чему вы удивляетесь?
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[gmdat_20091106].[PK_gmdat_20091106] AS [sc]))

таблицы из 140000 записей в цикле! Делайте на t1 индекс по material, Supplier, PriceGroup, авось поможет.
и еще. зачем нужен вот этот
 inner join spares_dealers_price sdp2 on t.nBrandID = sdp2.nBrandID and t.Code = sdp2.Code
join? Если nBrandID, Code уникальный ключ - вы получите ту же запись, если нет - получите комбинацию все со всеми для равных значений, зачем вам это?
10 ноя 09, 16:01    [7908689]     Ответить | Цитировать Сообщить модератору
 Re: select + дальнейший order by  [new]
_Виктор_
Member

Откуда: Россия, Москва
Сообщений: 116
iljy,
inner join spares_dealers_price sdp2 on t.nBrandID = sdp2.nBrandID and t.Code = sdp2.Code
это на самом деле для оператора Update, который я не указал... так как начал копаться почему долго апдейт идет, дорылся вот до order by... (Update перед обновлением как раз делает сортировку).
Так вроде
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[gmdat_20091106].[PK_gmdat_20091106] AS [sc]))
и там, и там есть... но результат по времени очень разный.
10 ноя 09, 16:24    [7908925]     Ответить | Цитировать Сообщить модератору
 Re: select + дальнейший order by  [new]
iljy
Member

Откуда:
Сообщений: 8711
_Виктор_
iljy,
inner join spares_dealers_price sdp2 on t.nBrandID = sdp2.nBrandID and t.Code = sdp2.Code
это на самом деле для оператора Update, который я не указал... так как начал копаться почему долго апдейт идет, дорылся вот до order by... (Update перед обновлением как раз делает сортировку).

Не понял, но здорово. Почему Update не может работать с таблицей sdp и зачем UPDATE сортировка?
_Виктор_

Так вроде
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[gmdat_20091106].[PK_gmdat_20091106] AS [sc]))
и там, и там есть... но результат по времени очень разный.


Hash Match и Nested Loop - сильно разные операции. Посмотрите на реальном плане счетчики строк.
10 ноя 09, 16:35    [7909031]     Ответить | Цитировать Сообщить модератору
 Re: select + дальнейший order by  [new]
_Виктор_
Member

Откуда: Россия, Москва
Сообщений: 116
iljy,
update'у может и не нужна предварительная сортировка, но факт остается фактом, что запрос сильно задумывается при update и без update но с order by по nBrandID и Code

update я делаю так:
update xxx
set col1 = t.col1, col2 = t.col2
from (select x.x_id, y.col1, y.col2
        from yyy y
        inner join xxx x on x.col3 = y.col3) T
inner join xxx x2 on x2.x_id = t.x_id
10 ноя 09, 16:50    [7909168]     Ответить | Цитировать Сообщить модератору
 Re: select + дальнейший order by  [new]
iljy
Member

Откуда:
Сообщений: 8711
_Виктор_
iljy,
update'у может и не нужна предварительная сортировка, но факт остается фактом, что запрос сильно задумывается при update и без update но с order by по nBrandID и Code

я уже сказал вам почему он задумывается.
_Виктор_

update я делаю так:
update xxx
set col1 = t.col1, col2 = t.col2
from (select x.x_id, y.col1, y.col2
        from yyy y
        inner join xxx x on x.col3 = y.col3) T
inner join xxx x2 on x2.x_id = t.x_id


а так
update x1
set col1 = y.col1, col2 = y.col2
from yyy y inner join xxx x1 on x.col3 = y.col3
сделать религия не позволяет?
10 ноя 09, 17:01    [7909252]     Ответить | Цитировать Сообщить модератору
 Re: select + дальнейший order by  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
А где сказано, что отношение между yyy и xxx 1:1?
Или я что-то пропустил?
10 ноя 09, 17:08    [7909316]     Ответить | Цитировать Сообщить модератору
 Re: select + дальнейший order by  [new]
_Виктор_
Member

Откуда: Россия, Москва
Сообщений: 116
iljy, позволяет, но привычней как написал.
iap, Ваш вопрос кому?
11 ноя 09, 11:51    [7912726]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить