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

Откуда: Магадан
Сообщений: 205
Добрый день.


Есть простой view, тупо джойнящий несколько таблиц.
К нему полагается обращаться для поиска строк по штрихкоду или по номеру заказа.

Если по штрихкоду:
select * from FindProductsByCarrierLabelBarcode_All
where barcode = '01022043718906124567002'
, то план выполнения такой:
  |--Compute Scalar(DEFINE:([Expr1008]=isnull([PS_BoxesContents].[picking_code], [STK_STOCK].[STK_SORT_KEY]), [Expr1009]=isnull([BB_Labels].[stock_code], [PS_BoxesContents].[stock_code]), [Expr1010]=isnull([PS_BoxesContents].[quantity], If ([BB_Labels].[stock_code]=NULL) then NULL else ([BB_Labels].[cases_count]*isnull(If ([dbo].[GetPacketsPerCaseFromDescr]([STK_STOCK].[STKNAME])=0) then NULL else [dbo].[GetPacketsPerCaseFromDescr]([STK_STOCK].[STKNAME]), 1)))))
|--Bookmark Lookup(BOOKMARK:([Bmk1006]), OBJECT:([GompelsHealthcareLtd].[dbo].[STK_STOCK]))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([BB_Labels].[stock_code], [PS_BoxesContents].[stock_code]))
|--Bookmark Lookup(BOOKMARK:([Bmk1004]), OBJECT:([Adapter].[dbo].[PS_BoxesContents]))
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([BB_Labels].[box_row_id]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([BB_Labels].[consignment_row_id]))
| | |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Adapter].[dbo].[BB_Labels]))
| | | |--Index Seek(OBJECT:([Adapter].[dbo].[BB_Labels].[BB_BB_Barcode]), SEEK:([BB_Labels].[barcode]='01022043718906124567002') ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT:([Adapter].[dbo].[BB_Consignments].[PK__BB_Consignments__7BE56230]), SEEK:([BB_Consignments].[row_id]=[BB_Labels].[consignment_row_id]) ORDERED FORWARD)
| |--Index Seek(OBJECT:([Adapter].[dbo].[PS_BoxesContents].[PS_BoxesContents_BoxRowID]), SEEK:([PS_BoxesContents].[box_row_id]=[BB_Labels].[box_row_id]) ORDERED FORWARD)
|--Index Seek(OBJECT:([GompelsHealthcareLtd].[dbo].[STK_STOCK].[STKCODE]), SEEK:([STK_STOCK].[STKCODE]=isnull([BB_Labels].[stock_code], [PS_BoxesContents].[stock_code])) ORDERED FORWARD)
Он прекрасен.

Если по номеру заказа:
select * from FindProductsByCarrierLabelBarcode_All
where OrderNumber = 123456
то аналогично:
  |--Compute Scalar(DEFINE:([Expr1008]=isnull([PS_BoxesContents].[picking_code], [STK_STOCK].[STK_SORT_KEY]), [Expr1009]=isnull([BB_Labels].[stock_code], [PS_BoxesContents].[stock_code]), [Expr1010]=isnull([PS_BoxesContents].[quantity], If ([BB_Labels].[stock_code]=NULL) then NULL else ([BB_Labels].[cases_count]*isnull(If ([dbo].[GetPacketsPerCaseFromDescr]([STK_STOCK].[STKNAME])=0) then NULL else [dbo].[GetPacketsPerCaseFromDescr]([STK_STOCK].[STKNAME]), 1)))))
|--Bookmark Lookup(BOOKMARK:([Bmk1006]), OBJECT:([GompelsHealthcareLtd].[dbo].[STK_STOCK]) WITH PREFETCH)
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([BB_Labels].[stock_code], [PS_BoxesContents].[stock_code]) WITH PREFETCH)
|--Bookmark Lookup(BOOKMARK:([Bmk1004]), OBJECT:([Adapter].[dbo].[PS_BoxesContents]) WITH PREFETCH)
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([BB_Labels].[box_row_id]) WITH PREFETCH)
| |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Adapter].[dbo].[BB_Labels]) WITH PREFETCH)
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([BB_Consignments].[row_id]))
| | |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([Adapter].[dbo].[BB_Consignments]))
| | | |--Index Seek(OBJECT:([Adapter].[dbo].[BB_Consignments].[BB_BB_OrderNumber]), SEEK:([BB_Consignments].[order_number]=123456) ORDERED FORWARD)
| | |--Index Seek(OBJECT:([Adapter].[dbo].[BB_Labels].[UQ__BB_Labels__056ECC6A]), SEEK:([BB_Labels].[consignment_row_id]=[BB_Consignments].[row_id]) ORDERED FORWARD)
| |--Index Seek(OBJECT:([Adapter].[dbo].[PS_BoxesContents].[PS_BoxesContents_BoxRowID]), SEEK:([PS_BoxesContents].[box_row_id]=[BB_Labels].[box_row_id]) ORDERED FORWARD)
|--Index Seek(OBJECT:([GompelsHealthcareLtd].[dbo].[STK_STOCK].[STKCODE]), SEEK:([STK_STOCK].[STKCODE]=isnull([BB_Labels].[stock_code], [PS_BoxesContents].[stock_code])) ORDERED FORWARD)
Что тоже прекрасно.


Теперь же, если объединить оба условия по OR:
select * from FindProductsByCarrierLabelBarcode_All
where barcode = '01022043718906124567002' or OrderNumber = 123456
Получается такая фигня:
  |--Compute Scalar(DEFINE:([Expr1008]=isnull([PS_BoxesContents].[picking_code], [STK_STOCK].[STK_SORT_KEY]), [Expr1009]=isnull([BB_Labels].[stock_code], [PS_BoxesContents].[stock_code]), [Expr1010]=isnull([PS_BoxesContents].[quantity], If ([BB_Labels].[stock_code]=NULL) then NULL else ([BB_Labels].[cases_count]*isnull(If ([dbo].[GetPacketsPerCaseFromDescr]([STK_STOCK].[STKNAME])=0) then NULL else [dbo].[GetPacketsPerCaseFromDescr]([STK_STOCK].[STKNAME]), 1)))))
|--Bookmark Lookup(BOOKMARK:([Bmk1006]), OBJECT:([GompelsHealthcareLtd].[dbo].[STK_STOCK]))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([BB_Labels].[stock_code], [PS_BoxesContents].[stock_code]))
|--Bookmark Lookup(BOOKMARK:([Bmk1004]), OBJECT:([Adapter].[dbo].[PS_BoxesContents]))
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([BB_Labels].[box_row_id]))
| |--Hash Match(Inner Join, HASH:([BB_Consignments].[row_id])=([BB_Labels].[consignment_row_id]), RESIDUAL:([BB_Labels].[barcode]='01022043718906124567002' OR [BB_Consignments].[order_number]=123456))
| | |--Clustered Index Scan(OBJECT:([Adapter].[dbo].[BB_Consignments].[PK__BB_Consignments__7BE56230]))
| | |--Clustered Index Scan(OBJECT:([Adapter].[dbo].[BB_Labels].[PK__BB_Labels__047AA831]))
| |--Index Seek(OBJECT:([Adapter].[dbo].[PS_BoxesContents].[PS_BoxesContents_BoxRowID]), SEEK:([PS_BoxesContents].[box_row_id]=[BB_Labels].[box_row_id]) ORDERED FORWARD)
|--Index Seek(OBJECT:([GompelsHealthcareLtd].[dbo].[STK_STOCK].[STKCODE]), SEEK:([STK_STOCK].[STKCODE]=isnull([BB_Labels].[stock_code], [PS_BoxesContents].[stock_code])) ORDERED FORWARD)

Запрос сваливается в скан по BB_Consignments и начинает выполняться долго (а не мгновенно).

Индексы по искомым полям очень селективные, запрос возвращает максимум 2 записи для штрихкода и максимум два десятка для заказа (из сотен тысяч).


На клиенте можем сделать селект по баркоду union селект по заказу, но будет неудобно.
Непонятно, почему сервер не может сделать этого сам, раз в случае поиска по одному любому из этих полей он всё понимает.
Как его направить на путь истинный?

Проверяли на серверах
Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) 
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
и
 Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
Результат однофигственный.
3 июл 09, 17:47    [7375268]     Ответить | Цитировать Сообщить модератору
 Re: OR сваливает запрос в скан  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36808
select * from FindProductsByCarrierLabelBarcode_All
where barcode = '01022043718906124567002' 
union all 
select * from FindProductsByCarrierLabelBarcode_All
where OrderNumber = 123456
3 июл 09, 17:52    [7375294]     Ответить | Цитировать Сообщить модератору
 Re: OR сваливает запрос в скан  [new]
Crimean
Member

Откуда:
Сообщений: 13148
да, OR -> UNION. де-то в 2000 как набажили так до сих пор и клинит оптимизатор :(
3 июл 09, 17:58    [7375328]     Ответить | Цитировать Сообщить модератору
 Re: OR сваливает запрос в скан  [new]
GSerg
Member

Откуда: Магадан
Сообщений: 205
Дык,
GSerg
На клиенте можем сделать селект по баркоду union селект по заказу, но будет неудобно.


С учётом особенностей клиента, очень неудобно, я бы сказал.

А так можем, да. Но хотелось красиво.
3 июл 09, 17:59    [7375330]     Ответить | Цитировать Сообщить модератору
 Re: OR сваливает запрос в скан  [new]
Crimean
Member

Откуда:
Сообщений: 13148
CTE очень здорово с UNION работают в этих случаях и писать мало
3 июл 09, 18:58    [7375525]     Ответить | Цитировать Сообщить модератору
 Re: OR сваливает запрос в скан  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5499
Блог
GSerg
Непонятно, почему сервер не может сделать этого сам, раз в случае поиска по одному любому из этих полей он всё понимает.
Хм... сервер глупый - как ему оптимизатор написали, так он и работает. ;)
2000 этого не умел вообще, а вот 2005 прекрасно справляется и делает UNION сам (Merge Concat в плане.)
По крайней мере, на примере, когда в OR оба условия - поиск значения по уникальному индексу.

Другой вопрос, что там с селективностью полей, сложностью представления и количества индексов....
3 июл 09, 19:04    [7375544]     Ответить | Цитировать Сообщить модератору
 Re: OR сваливает запрос в скан  [new]
GSerg
Member

Откуда: Магадан
Сообщений: 205
DeColo®es
2000 этого не умел вообще, а вот 2005 прекрасно справляется и делает UNION сам (Merge Concat в плане.)
По крайней мере, на примере, когда в OR оба условия - поиск значения по уникальному индексу.

Другой вопрос, что там с селективностью полей, сложностью представления и количества индексов....

Так вот же ж.
Индексы не уникальные.
А про селективность оных написано уже выше.

Crimean
CTE очень здорово с UNION работают в этих случаях и писать мало

Не соображу, как здесь написать с CTE.
В идеале надо, чтобы клиент видел единственный view, который мог бы запросить с одним или двумя параметрами. Т.е. чтобы union был на сервере и был совершенно прозрачен для клиента.

Если так не получится, сделаем union на клиенте, но это очень неудобно получится, как я уже говорил.
3 июл 09, 19:15    [7375574]     Ответить | Цитировать Сообщить модератору
 Re: OR сваливает запрос в скан  [new]
Crimean
Member

Откуда:
Сообщений: 13148
CTE тут надо тогда, когда запрос сложный. CASE есть, условий ДРУГИХ много. тогда все это - в CTE. и 2 запроса из CTE в UNION. получается понятно, компактно и быстро
3 июл 09, 19:25    [7375600]     Ответить | Цитировать Сообщить модератору
 Re: OR сваливает запрос в скан  [new]
GSerg
Member

Откуда: Магадан
Сообщений: 205
Crimean
CTE тут надо тогда, когда запрос сложный. CASE есть, условий ДРУГИХ много. тогда все это - в CTE. и 2 запроса из CTE в UNION. получается понятно, компактно и быстро

А, понял.
Нет, других условий нет никаких.

Плюс к тому, так всё равно не получится, т.к. если CTE будет внутри view, то никак нельзя будет подставить передаваемое извне условие where в два union'а после CTE.
3 июл 09, 20:20    [7375718]     Ответить | Цитировать Сообщить модератору
 Re: OR сваливает запрос в скан  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
GSerg
Crimean
CTE тут надо тогда, когда запрос сложный. CASE есть, условий ДРУГИХ много. тогда все это - в CTE. и 2 запроса из CTE в UNION. получается понятно, компактно и быстро

А, понял.
Нет, других условий нет никаких.

Плюс к тому, так всё равно не получится, т.к. если CTE будет внутри view, то никак нельзя будет подставить передаваемое извне условие where в два union'а после CTE.
Условие WHERE по-любому можно использовать только в динамическом SQL.
А если Вы имеете в виду параметризированный запрос, то лучше всего сделать inline табличную функцию, внутри которой и CTE можно применить.
3 июл 09, 20:25    [7375730]     Ответить | Цитировать Сообщить модератору
 Re: OR сваливает запрос в скан  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iap
Условие WHERE по-любому можно использовать только в динамическом SQL.
Я имел в виду "передаваемые извне условие where", конечно.
3 июл 09, 20:28    [7375739]     Ответить | Цитировать Сообщить модератору
 Re: OR сваливает запрос в скан  [new]
GSerg
Member

Откуда: Магадан
Сообщений: 205
iap
А если Вы имеете в виду параметризированный запрос, то лучше всего сделать inline табличную функцию, внутри которой и CTE можно применить.

Внутри view -- джойн без всяких условий. Если просто запросить view, оно вернёт несколько сотен тысяч строк.
Если запросить его с where barcode = , оно вернёт одну строку, при этом план выполнения будет правильный, как будто это where стояло внутри view.

Я не могу сделать параметрическую функцию, потому что клиент не способен её вызвать. Только таблица или представление.

Я знаю, как это обойти и таки сделать псевдопараметры, но мне хотелось "сделать красиво" и заставить сервер видеть то, что он и так видит, когда поиск только по одному параметру.
4 июл 09, 02:53    [7376329]     Ответить | Цитировать Сообщить модератору
 Re: OR сваливает запрос в скан  [new]
ChA
Member

Откуда: Москва
Сообщений: 10989
GSerg
Я знаю, как это обойти и таки сделать псевдопараметры, но мне хотелось "сделать красиво" и заставить сервер видеть то, что он и так видит, когда поиск только по одному параметру.
Не совсем понятно, какие есть ограничения в исходной задаче. Например:
1.Можно ли менять view (неплохо, кстати, было бы показать его текст, а то восстанавливать по плану лениво) ?
2.Можно ли применять при вызове view опции(option()) ?
3.Что с исходными индексами(опять же по плану всё не восстановишь) ? Нелохо бы полный DDL-скрипт на всех участников, включая все индексы и ограничения, включая view.
4.Обязательна ли "*" в качестве списка полей ?
5.Почему нужно использовать при выборке одновременно оба фильтра, а не по очереди, скажем ?
6.Пробовали ли обновить статистику ?
И т.д. и т.п.
4 июл 09, 03:40    [7376344]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить