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

Откуда: Бердск, НСО
Сообщений: 499
Есть таблица заказов Orders, примерно, такая
OrderId uniqueidentifier,
CustomerId uniqueidentifier,
GoodId uniqueidentifier
Amount money,
PayMethod int

И таблица состояния заказов OrderStates, вот такая:
OrderId uniqueidentifier,
Status int,
OperatorId uniqueidentifier,
Changed datetime

Каждый заказ может иметь несколько состояний: новый, принят, обслужен, отклонён, отменён клиентом и оплачен. По мере перехода заказа в другое состояние, в таблице состояний заказов добавляется новая запись с номером состояния, датой изменения и кодом оператора, изменившего состояние.
Связаны они между собой через OrderId. Есть таблица клиентов Customers и таблица операторов Users. Таблица Users содержит базовую информацию по пользователям, Customers - дополнительную, связанную непосредственно с клиентами. Связаны между собой через UserId.
Задача: получить таблицу содержащую все заказы с их состояниями и информацией о клиенте и операторе, переведшем заказ в определённое состояние.
Сейчас это делаетcz, примерно, таким запросом:
SELECT DISTINCT 
                      TOP (100) PERCENT o.OrderId, o.OrderNo, o.Deleted, o.Note, o.CustomerId, o.CustomerAmount, 
                      cs.EMail AS CustomerEMail, cs.Nick AS CustomerNick,  cs.FirstName AS CustomerFirstName, cs.LastName AS CustomerLastName,
                      sn.DateTimeStatusChanged AS Created, un.EMail AS CreatedBy, un.Id AS CreatedById, 
                      sa.DateTimeStatusChanged AS Accepted, opa.EMail AS AcceptedBy, opa.Id AS AcceptedById, 
                      sc.DateTimeStatusChanged AS Completed, opc.EMail AS CompletedBy, opc.Id AS CompletedById,
                      sd.DateTimeStatusChanged AS Declined, opd.EMail AS DeclinedBy, opd.Id AS DeclinedById,
                      scc.DateTimeStatusChanged AS CancelledByCustomer, sp.DateTimeStatusChanged AS Paid, opp.EMail AS PaidBy, 
                      opp.Id AS PaidById
FROM         dbo.Orders AS o LEFT OUTER JOIN
                      dbo.Users AS cs ON o.CustomerId = cs.Id LEFT OUTER JOIN
                      dbo.OrderStates AS sn ON sn.OrderId = o.OrderId AND sn.OrderStatus = 0 LEFT OUTER JOIN
                      dbo.Users AS un ON sn.OperatorId = un.Id LEFT OUTER JOIN
                      dbo.OrderStates AS sa ON o.OrderId = sa.OrderId AND sa.OrderStatus = 1 LEFT OUTER JOIN
                      dbo.Users AS opa ON sa.OperatorId = opa.Id LEFT OUTER JOIN
                      dbo.OrderStates AS sc ON o.OrderId = sc.OrderId AND sc.OrderStatus = 2 LEFT OUTER JOIN
                      dbo.Users AS opc ON sc.OperatorId = opc.Id LEFT OUTER JOIN
                      dbo.OrderStates AS sd ON o.OrderId = sd.OrderId AND sd.OrderStatus = 3 LEFT OUTER JOIN
                      dbo.Users AS opd ON sd.OperatorId = opd.Id LEFT OUTER JOIN
                      dbo.OrderStates AS scc ON o.OrderId = scc.OrderId AND scc.OrderStatus = 4 LEFT OUTER JOIN
                      dbo.OrderStates AS sp ON o.OrderId = sp.OrderId AND sp.OrderStatus = 5 LEFT OUTER JOIN
                      dbo.Users AS opp ON sp.OperatorId = opp.Id
ORDER BY o.OrderNo

Но этот запрос исполняется очень медленно: на 3 тыс. заказов - 12-15 сек. Можно ли его как-то ускорить, подскажите, пожалуйста.
10 дек 14, 14:38    [16977635]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
Glory
Member

Откуда:
Сообщений: 104751
А зачем столько одинковых join-ов с dbo.OrderStates?
Разве одного join и CASE для проверки OrderStatus недостаточно ?
10 дек 14, 14:46    [16977712]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
maxapet
Member

Откуда: Бердск, НСО
Сообщений: 499
А как в данном случает можно использовать CASE?
10 дек 14, 14:51    [16977750]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
Glory
Member

Откуда:
Сообщений: 104751
maxapet
А как в данном случает можно использовать CASE?

Написать его столько же раз, сколько вы написали join-ов
10 дек 14, 14:54    [16977770]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
maxapet
Member

Откуда: Бердск, НСО
Сообщений: 499
Простите, не понимаю. Напишите код, пожалуйста.
10 дек 14, 15:05    [16977885]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
Glory
Member

Откуда:
Сообщений: 104751
select case when OrderStatus = 0 than ... end as f1, case when OrderStatus = 1 than ... end as f2, ...
10 дек 14, 15:11    [16977951]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
maxapet
Простите, не понимаю. Напишите код, пожалуйста.

SELECT DISTINCT TOP (100) PERCENT 
	o.OrderId, o.OrderNo, o.Deleted, o.Note, o.CustomerId, o.CustomerAmount, 
	cs.EMail AS CustomerEMail, cs.Nick AS CustomerNick,  cs.FirstName AS CustomerFirstName, cs.LastName AS CustomerLastName,
	CASE WHEN sn.OrderStatus = 0 THEN sn.DateTimeStatusChanged ELSE NULL END AS Created, CASE WHEN sn.OrderStatus = 0 THEN un.EMail ELSE NULL END AS CreatedBy, CASE WHEN sn.OrderStatus = 0 THEN un.Id ELSE NULL END AS CreatedById, 
	CASE WHEN sn.OrderStatus = 1 THEN sn.DateTimeStatusChanged ELSE NULL END AS Accepted, CASE WHEN sn.OrderStatus = 1 THEN un.EMail ELSE NULL END AS AcceptedBy, CASE WHEN sn.OrderStatus = 1 THEN un.Id ELSE NULL END AS AcceptedById, 
	... и т.д.
FROM	dbo.Orders AS o 
	LEFT OUTER JOIN dbo.Users AS cs 
		ON o.CustomerId = cs.Id 
	LEFT OUTER JOIN dbo.OrderStates AS sn 
		ON sn.OrderId = o.OrderId AND sn.OrderStatus in (0 ,1,2,3,4,5)
	LEFT OUTER JOIN dbo.Users AS un 
		ON sn.OperatorId = un.Id 
ORDER BY o.OrderNo
10 дек 14, 15:21    [16978024]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
Wlr-l
Member

Откуда:
Сообщений: 606
Запрос
...
FROM            dbo.Orders AS o
LEFT OUTER JOIN dbo.OrderStates AS sn ON sn.OrderId = o.OrderId AND sn.OrderStatus = 0
LEFT OUTER JOIN dbo.OrderStates AS sa ON o.OrderId = sa.OrderId AND sa.OrderStatus = 1
LEFT OUTER JOIN dbo.OrderStates AS sc ON o.OrderId = sc.OrderId AND sc.OrderStatus = 2 
...

не эквивалентен запросу
...
	CASE WHEN sn.OrderStatus = 0 THEN sn.DateTimeStatusChanged ELSE NULL END AS Created, CASE WHEN sn.OrderStatus = 0 THEN un.EMail ELSE NULL END AS CreatedBy, CASE WHEN sn.OrderStatus = 0 THEN un.Id ELSE NULL END AS CreatedById, 
	CASE WHEN sn.OrderStatus = 1 THEN sn.DateTimeStatusChanged ELSE NULL END AS Accepted, CASE WHEN sn.OrderStatus = 1 THEN un.EMail ELSE NULL END AS AcceptedBy, CASE WHEN sn.OrderStatus = 1 THEN un.Id ELSE NULL END AS AcceptedById, 
	... и т.д.
FROM            dbo.Orders AS o 
LEFT OUTER JOIN dbo.OrderStates AS sn ON sn.OrderId = o.OrderId AND sn.OrderStatus in (0,1,2,3,4,5)
...
10 дек 14, 15:47    [16978291]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
maxapet
Member

Откуда: Бердск, НСО
Сообщений: 499
Увы, быстрее запрос не стал. Более того, чем больше состояний, тем больше время исполнения запроса и в итоге он получается медленнее исходного. -(
10 дек 14, 16:13    [16978498]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
Konst_One
Member

Откуда:
Сообщений: 11625
maxapet
Увы, быстрее запрос не стал. Более того, чем больше состояний, тем больше время исполнения запроса и в итоге он получается медленнее исходного. -(


вам надо перевернуть таблицу состояний, чтобы повязки делать оптимальней и чтобы быстрей работало.
а так вы из EAV пытаетесь реляционную схему вытащить, а это всегда не быстро
10 дек 14, 16:17    [16978524]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
Wlr-l
Member

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

Таблица заказов Orders и таблица состояния заказов OrderStates находятся в отношении 1:М, ни какого отношения к EAV не имеют. Все реляционно!
10 дек 14, 16:23    [16978567]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
Konst_One
Member

Откуда:
Сообщений: 11625
Wlr-l
Konst_One,

Таблица заказов Orders и таблица состояния заказов OrderStates находятся в отношении 1:М, ни какого отношения к EAV не имеют. Все реляционно!


это так, но отчёт, что ТС хочет получить , пытается работать с данными таблицы состояний, как с EAV
10 дек 14, 16:25    [16978584]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
Wlr-l
Member

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

ТС хочет все состояния Заказа вывести в одну строку. Его запрос правильный, только смущает DISTINCT TOP (100) PERCENT. Скорее всего не хватает индекса.
10 дек 14, 16:33    [16978664]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
Wlr-l
не эквивалентен запросу
Почему это???

А, или в OrderStates поле OrderId не уникально, а уникально сочетание OrderId и OrderStatus? Да, тогда не эквивалентен.

Но тогда вы быстрее не сделаете, исходный запрос выбирает данные максимально быстро, что можно придумать быстрее, чем тупой джойн таблиц по уникальному ключу?
10 дек 14, 16:36    [16978699]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
Konst_One
Member

Откуда:
Сообщений: 11625
Wlr-l
Konst_One,

ТС хочет все состояния Заказа вывести в одну строку. Его запрос правильный, только смущает DISTINCT TOP (100) PERCENT. Скорее всего не хватает индекса.


ну так я про это же и говорю, он отчёт делает, разворачивая состояния как PIVOT
может ему проще подготовить другую таблицу состояний , уже в развёрнутом виде, а её уже привязывать к заказам
10 дек 14, 16:38    [16978721]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
Wlr-l
Member

Откуда:
Сообщений: 606
alexeyvg
Wlr-l
не эквивалентен запросу
Почему это???



Потому что у ТС будет одна строка на один заказ, а для CASE - столько, сколько состояний зафиксировано.

И условие AND sn.OrderStatus in (0,1,2,3,4,5) лишнее, так как там других значений быть не может.
10 дек 14, 16:39    [16978732]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
Wlr-l
Member

Откуда:
Сообщений: 606
Konst_One
ну так я про это же и говорю, он отчёт делает, разворачивая состояния как PIVOT
может ему проще подготовить другую таблицу состояний , уже в развёрнутом виде, а её уже привязывать к заказам


Согласен, ТС нужно попробовать.
10 дек 14, 16:42    [16978757]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
maxapet
Member

Откуда: Бердск, НСО
Сообщений: 499
Да, на OrderStates PK на OrderId и OrderStatus.
На Orders PK - OrderId.
10 дек 14, 17:07    [16978953]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
zasandator
Member [скрыт] [заблокирован]

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

pivot
10 дек 14, 17:24    [16979082]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
Wlr-l
Member

Откуда:
Сообщений: 606
zasandator
maxapet,

pivot


Бесполезный совет.

Думаю есть два варианта уменьшить число соединений в исходном запросе:

1.Соединить таблицы OrderStates и Users, развернуть их (pivot или case ... group by) и соединить таблицу Orders с полученной.

2.Соединить таблицы Orders, OrderStates и Users и уже потом развернуть их.
10 дек 14, 17:36    [16979171]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8839
Такое количество джойнов смехотворно, я встречал AD Hoc запросы с порядка 150 джойнов, которые при наличии структуры плана вполне прилично работали, не более пары секунд при размере таблиц в пределах 1 Гб.

Смотрите план запроса, стойте индексы.
10 дек 14, 18:00    [16979310]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
maxapet
Member

Откуда: Бердск, НСО
Сообщений: 499
Да там, вроде бы, и разгуляться негде с индексами-то. :-)
10 дек 14, 18:32    [16979479]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
zasandator
Member [скрыт] [заблокирован]

Откуда:
Сообщений: 4887
Wlr-l
zasandator
maxapet,

pivot


Бесполезный совет.
1....развернуть их (pivot или .....)....


Ты не тоже самое повторил, неудачнег? ))))
10 дек 14, 18:32    [16979480]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
NickAlex66
Member

Откуда:
Сообщений: 319
maxapet
Да там, вроде бы, и разгуляться негде с индексами-то. :-)

Вы актуальный план исправленного запроса приведите, тогда и про индексы можно говорить.
10 дек 14, 18:52    [16979593]     Ответить | Цитировать Сообщить модератору
 Re: Помтгите оптимизировать запросоптимизировать запрос?  [new]
maxapet
Member

Откуда: Бердск, НСО
Сообщений: 499
В каком виде план привести?
10 дек 14, 19:39    [16979875]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить