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

Откуда:
Сообщений: 90
Добрый день. Есть такой запрос:

 SELECT DO.id, CON.oid as oid, DO.start, DO.stop, DO.outSms15 as sms15, DO.outSms30 as sms30, DO.outSms60 as sms60
   FROM Terminal.dbo.Connection  as CON
   LEFT JOIN Terminal.dbo.Downtime  as DO ON CON.OID = DO.oid
   LEFT JOIN Terminal.dbo.Downtime  as DDO ON CON.OID = DDO.oid
	  AND DO.start < DDO.start
   WHERE DDO.id is NULL


Вот примерный результат выполнения:

id oid start stop sms15 sms30 sms60
E822C3A7-89A2-4A16-B918-E7A31DAD44BB 93E8FECB-8782-4357-B0FC-75C147594F50 2019-09-01 15:53:56.000 2019-09-01 16:21:10.000 NULL NULL NULL
6A3917C6-4C9E-4759-B147-02DB544E7FE5 E0472205-8C90-4C66-A5EF-F36BE9399875 2019-07-04 15:00:31.000 NULL 1 1 1
F99E87F1-C206-4428-BA0C-203BE44A171C 5B22FE0E-36A5-42AE-A280-93E6D47B4044 2019-07-08 10:32:51.000 NULL 1 1 1
5997C60C-3524-46DF-BB7D-2CE7725B7DCB 0ADA876A-4C54-4EFB-8D72-62CE5CEE1A3E 2019-09-01 09:00:51.000 2019-09-01 09:06:43.000 NULL NULL NULL


Суть вопроса. Чтобы получить последнюю дату столбца start пишется два одинаковых запроса LEFT JOIN . Далее сравнивается большее значение даты DO.start < DDO.start.

Вопрос, можно ли как то упростить запрос. Чтобы он выполнялся быстрее?
3 сен 19, 09:01    [21962231]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли как то оптимизировать запрос?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6498
Hopfen_Artur,

[R] = ROW_NUMBER(PARTITION BY DO.id ORDER BY DO.stop DESC) + WHERE R = 1
или TOP 1 WITH TIES
3 сен 19, 09:17    [21962234]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли как то оптимизировать запрос?  [new]
Focha
Member

Откуда: Москва
Сообщений: 445
TaPaK
Hopfen_Artur,

[R] = ROW_NUMBER(PARTITION BY DO.id ORDER BY DO.stop DESC) + WHERE R = 1
или TOP 1 WITH TIES

оконные функции
https://docs.microsoft.com/ru-RU/sql/t-sql/queries/select-over-clause-transact-sql?view=aps-pdw-2016
3 сен 19, 09:31    [21962244]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли как то оптимизировать запрос?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6498
Focha
TaPaK
Hopfen_Artur,

[R] = ROW_NUMBER(PARTITION BY DO.id ORDER BY DO.stop DESC) + WHERE R = 1
или TOP 1 WITH TIES

оконные функции
https://docs.microsoft.com/ru-RU/sql/t-sql/queries/select-over-clause-transact-sql?view=aps-pdw-2016

ухты
3 сен 19, 09:33    [21962246]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли как то оптимизировать запрос?  [new]
Focha
Member

Откуда: Москва
Сообщений: 445
TaPaK
Focha
пропущено...

оконные функции
https://docs.microsoft.com/ru-RU/sql/t-sql/queries/select-over-clause-transact-sql?view=aps-pdw-2016

ухты

ну вдруг он не поймет
3 сен 19, 09:34    [21962247]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли как то оптимизировать запрос?  [new]
Hopfen_Artur
Member

Откуда:
Сообщений: 90
Вот так написал, пока не работает, пишет недопустимое имя столбца, разбираюсь

 SELECT ROW_NUMBER() OVER(PARTITION BY DO.oid ORDER BY DO.stop DESC) as R, DO.id, CON.oid as oid, DO.start, DO.stop, DO.outSms15 as sms15, DO.outSms30 as sms30, DO.outSms60 as sms60
   FROM Terminal.dbo.Connection  as CON
   LEFT JOIN Terminal.dbo.Downtime  as DO ON CON.OID = DO.oid
   WHERE R = '1'
3 сен 19, 09:35    [21962248]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли как то оптимизировать запрос?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6498
Hopfen_Artur
Вот так написал, пока не работает, пишет недопустимое имя столбца, разбираюсь

 SELECT ROW_NUMBER() OVER(PARTITION BY DO.oid ORDER BY DO.stop DESC) as R, DO.id, CON.oid as oid, DO.start, DO.stop, DO.outSms15 as sms15, DO.outSms30 as sms30, DO.outSms60 as sms60
   FROM Terminal.dbo.Connection  as CON
   LEFT JOIN Terminal.dbo.Downtime  as DO ON CON.OID = DO.oid
   WHERE R = '1'

обурнуть в WITH x as (SELECT ...) SELECT .. FROM x WHERE R = 1
3 сен 19, 09:37    [21962249]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли как то оптимизировать запрос?  [new]
Focha
Member

Откуда: Москва
Сообщений: 445
TaPaK
Hopfen_Artur
Вот так написал, пока не работает, пишет недопустимое имя столбца, разбираюсь

 SELECT ROW_NUMBER() OVER(PARTITION BY DO.oid ORDER BY DO.stop DESC) as R, DO.id, CON.oid as oid, DO.start, DO.stop, DO.outSms15 as sms15, DO.outSms30 as sms30, DO.outSms60 as sms60
   FROM Terminal.dbo.Connection  as CON
   LEFT JOIN Terminal.dbo.Downtime  as DO ON CON.OID = DO.oid
   WHERE R = '1'

обурнуть в WITH x as (SELECT ...) SELECT .. FROM x WHERE R = 1



а тогда зачем left join если потом мы убираем где нет даты?
Может
SELECT max(DO.stop) OVER(PARTITION BY DO.oid) as R
		 , DO.id
		 , CON.oid as oid
		 , DO.start
		 , DO.stop
		 , DO.outSms15 as sms15
		 , DO.outSms30 as sms30
		 , DO.outSms60 as sms60
   FROM Terminal.dbo.Connection  as CON
   LEFT JOIN Terminal.dbo.Downtime  as DO ON CON.OID = DO.oid
3 сен 19, 09:50    [21962256]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли как то оптимизировать запрос?  [new]
Hopfen_Artur
Member

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

очень быстро получилось, спасибо

 SET STATISTICS TIME ON;
 WITH tb (R, id, oid, start, stop, sms15, sms30, sms60)  
AS  
(  SELECT ROW_NUMBER() OVER(PARTITION BY DO.oid ORDER BY DO.start DESC) as R, DO.id, CON.oid as oid, DO.start, DO.stop, DO.outSms15 as sms15, DO.outSms30 as sms30, DO.outSms60 as sms60
   FROM Terminal.dbo.Connection  as CON
   LEFT JOIN Terminal.dbo.Downtime  as DO ON CON.OID = DO.oid
  
)  
SELECT id, oid, start, stop, sms15, sms30, sms60   
FROM tb
WHERE R = 1
SET STATISTICS TIME OFF; 
3 сен 19, 09:59    [21962260]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли как то оптимизировать запрос?  [new]
Hopfen_Artur
Member

Откуда:
Сообщений: 90
Было

(затронуто строк: 4)

Время работы SQL Server:
Время ЦП = 47 мс, затраченное время = 40 мс.

Стало

(затронуто строк: 4)

Время работы SQL Server:
Время ЦП = 0 мс, затраченное время = 4 мс.
3 сен 19, 10:01    [21962261]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли как то оптимизировать запрос?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 2985
Hopfen_Artur
Было

(затронуто строк: 4)

Время работы SQL Server:
Время ЦП = 47 мс, затраченное время = 40 мс.

Стало

(затронуто строк: 4)

Время работы SQL Server:
Время ЦП = 0 мс, затраченное время = 4 мс.


это вообще-то подозрительно
3 сен 19, 11:20    [21962322]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли как то оптимизировать запрос?  [new]
Hopfen_Artur
Member

Откуда:
Сообщений: 90
Ролг Хупин,

полный запрос выглядит так, оптимизирую сижу вот:

SET STATISTICS TIME ON; 
with tb (R, id, oid, start, stop, sms15, sms30, sms60)  
AS  
(  SELECT ROW_NUMBER() OVER(PARTITION BY DO.oid ORDER BY DO.start DESC) as R, DO.id, CON.oid as oid, DO.start, DO.stop, DO.outSms15 as sms15, DO.outSms30 as sms30, DO.outSms60 as sms60
   FROM Terminal.dbo.Connection  as CON
   LEFT JOIN Terminal.dbo.Downtime  as DO ON CON.OID = DO.oid
  
)

SELECT MAX(RF.Date) AS date
   ,CON.OID as oid
   ,TC.ProductCycle as cycl
   ,tb.id as downtime
   ,TC.Product as product
   ,tb.sms15
   ,tb.sms30
   ,tb.sms60
FROM EAM_test.dbo.RFIDData AS RF
LEFT JOIN EAM_test.dbo.RFIDReader AS RR ON RF.Reader = RR.Oid AND RR.Active = 1
LEFT JOIN Terminal.dbo.Connection AS CON ON CON.OID = RR.Asset
LEFT JOIN Terminal.dbo.Terminal1C AS TC ON CON.OID = TC.OID
   AND TC.Day = 'День'
   AND TC.status = 1
LEFT JOIN tb ON CON.OID = tb.oid and tb.stop is null AND tb.R = 1
WHERE TC.Date = '2019-03-09'
GROUP BY CON.OID, TC.ProductCycle, tb.id, TC.Product, tb.sms15, tb.sms30, tb.sms60

SET STATISTICS TIME OFF; 


пока полное время такое:

(затронуто строк: 3)

Время работы SQL Server:
Время ЦП = 1606 мс, затраченное время = 1155 мс.
3 сен 19, 11:24    [21962325]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли как то оптимизировать запрос?  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34486
Hopfen_Artur,

Это же сплошной table scan....
Тут даже нечего оптимизировать
3 сен 19, 14:27    [21962540]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли как то оптимизировать запрос?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6498
MasterZiv
Hopfen_Artur,

Это же сплошной table scan....
Тут даже нечего оптимизировать

авторитетненько, ещё тезисы будут? :)
3 сен 19, 14:31    [21962544]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли как то оптимизировать запрос?  [new]
Hopfen_Artur
Member

Откуда:
Сообщений: 90
Получилось уменьшить, поэксперементировал с порядком JION

(затронуто строк: 4)

Время работы SQL Server:
Время ЦП = 730 мс, затраченное время = 206 мс.
4 сен 19, 08:43    [21962973]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли как то оптимизировать запрос?  [new]
aleks222
Member

Откуда:
Сообщений: 730
Hopfen_Artur
Получилось уменьшить, поэксперементировал с порядком JION

(затронуто строк: 4)

Время работы SQL Server:
Время ЦП = 730 мс, затраченное время = 206 мс.


Икспериментатор, видно по нему.

У тя, страдалец, под группировкой, как минимум, две лишние таблицы торчат.
А группировка, страдалец, это тяжело.
4 сен 19, 08:57    [21962984]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли как то оптимизировать запрос?  [new]
PizzaPizza
Member

Откуда:
Сообщений: 280
интересно бы увидеть план: справился ли оптимизатор с таким оригинальным представлением хотелок автора
4 сен 19, 21:52    [21964006]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить