Оптимизация запроса с помощью самосоединений и групп bys в предложении where

ProgramBox

Оптимизация запроса с помощью самосоединений и групп bys в предложении where

Post author:admin
Запись опубликована:27 мая, 2023
Post category:Вопросы по программированию

#sql-server

#sql-сервер

Вопрос:

Это мой старый запрос:

 Select AccountID,AccountDesc,
    CASE 
    WHEN NOT EXISTS (select AccountID from #B where #B.AccountID = P1.AccountID) THEN cast(cast(GETDATE() as date) as varchar)     
    ELSE cast(cast(CreateDate as date) as varchar)
    END 
    FROM #A P1
    WHERE P1.EndDate = (Select max(EndDate) from #A P2 where P1.AccountID = P2.AccountID and P2.Categ1 = 'F'
    group by P2.AccountID) 
    and P1.StartDate = (Select max(StartDate) from #A P2 where P1.AccountID = P2.AccountID and P2.Categ1 = 'F'
    group by P2.AccountID) 
    and P1.CreateDate = (Select max(CreateDate ) from #A P2 where P1.AccountID = P2.AccountID and P2.Categ1 = 'F'
    group by P2.AccountID) 
    and P1.NewStartDate= (Select max(NewStartDate) from #A P2 where P1.AccountID = P2.AccountID and P2.Categ1 = 'F'
    group by P2.AccountID)

Я хотел избавиться от повторяющихся самосоединений и групповых операций в предложении where, поэтому я избавился от части «Category1 = F» и поместил ее вместо этого в предложение where рабочей таблицы #A, когда я вставляю в нее. И я полагал, что самосоединение предназначено только для получения максимальных значений, поэтому, поскольку я создал рабочую таблицу, я не думал, что мне больше понадобится это самосоединение. Итак, я заканчиваю с этим новым запросом:

 Select AccountID,AccountDesc,
    CASE 
    WHEN NOT EXISTS (select AccountID from #B where #B.AccountID = P1.AccountID) THEN cast(cast(GETDATE() as date) as varchar)     
    ELSE cast(cast(CreateDate as date) as varchar)
    END 
    FROM #A P1
    WHERE P1.EndDate = (Select max(EndDate) from #A)
    and P1.StartDate = (Select max(StartDate) from #A) 
    and P1.CreateDate = (Select max(CreateDate) from #A) 
    and P1.NewStartDate= (Select max(NewStartDate) from #A)

Но это не возвращает то же количество записей, я полагаю, потому что я также удалил предложения «Group By» в условиях where?

Может ли кто-нибудь помочь мне ускорить этот исходный запрос, но при этом вернуть те же результаты?

1. Все эти подзапросы вообще не помогают. Примеры данных и ожидаемые результаты также помогли бы здесь.

2. Ларну, спасибо за comment…it мне потребуется некоторое время, чтобы разобраться, как предоставить образцы данных (некоторые причины — очистка личной информации, очень большой набор данных, а также я никогда не добавлял данные в stack overflow)… Я попытаюсь посмотреть, как это как-то работает.

3. Какую версию SQL Server вы используете? Этот запрос можно переписать для использования оконных функций ( learn.microsoft.com/en-us/sql/t-sql/queries /… ).

Ответ №1:

Для начала: все эти GROUP BY AccountID предложения в вашем запросе излишни, поскольку подзапросы в любом случае всегда выбирают данные только для одного идентификатора учетной записи.

Что было опробовано в WHERE предложении, так это:

 WHERE (AccountID, EndDate, StartDate, CreateDate, NewStartDate) IN
(
  SELECT AccountID, MAX(EndDate), MAX(StartDate), MAX(CreateDate), MAX(NewStartDate)
  FROM #A
  WHERE Categ1 = 'F'
  GROUP BY AccountID
)

Однако SQL Server не поддерживает этот синтаксис. Итак, преобразуйте это в EXISTS :

 WHERE EXISTS
(
  SELECT 1
  FROM #A P2
  WHERE P2.AccountID = P1.AccountID AND P2.Categ1 = 'F'
  HAVING P1.EndDate = MAX(P2.EndDate)
     AND P1.StartDate = MAX(P2.StartDate)
     AND P1.CreateDate = MAX(P2.CreateDate)
     AND P1.NewStartDate = MAX(P2.NewStartDate) 
)

1. Хорошо, это проницательно — я уже принял другой ответ, но ваше объяснение имеет смысл, и я тоже попробую его использовать. Приятно видеть несколько способов решения проблемы. Спасибо!

Ответ №2:

вы можете создать подзапрос и объединить его, чтобы вычисления выполнялись один раз.

 ;with cte as
(
select Account,max(EndDate) ED ,max(StartDate) SD,max(CreateDate ) CD ,max(NewStartDate) NSD 
from #a
where Categ1 = 'F'
group by Account
)
Select AccountID,AccountDesc,
    CASE 
    WHEN NOT EXISTS (select AccountID from #B where #B.AccountID = P1.AccountID) THEN cast(cast(GETDATE() as date) as varchar)     
    ELSE cast(cast(CreateDate as date) as varchar)
    END 
 FROM #A p1
     join cte on p1.Account=cte.Account
           and p1.StartDate=cte.SD
           and p1.EndDate = cte.ED
           and p1.CreateDate = cte.CD 
           and p1.NewStartDate=cte.NSD

вот еще одно усовершенствование:

 CASE 
    WHEN NOT EXISTS (select AccountID from #B where #B.AccountID = P1.AccountID) THEN cast(cast(GETDATE() as date) as varchar)     
    ELSE cast(cast(CreateDate as date) as varchar)

может быть изменено путем объединения слева #B

 ;with cte as
(
select Account,max(EndDate) ED ,max(StartDate) SD,max(CreateDate ) CD ,max(NewStartDate) NSD 
from #a
where Categ1 = 'F'
group by Account
)
Select AccountID,AccountDesc,
    case when p2.Account is not null 
         then cast(CreateDate as varchar(10)) 
         else cast(cast(getdate() as date) as varchar(10)) end
 FROM #A p1
     join cte on p1.Account=cte.Account
           and p1.StartDate=cte.SD
           and p1.EndDate = cte.ED
           and p1.CreateDate = cte.CD 
           and p1.NewStartDate=cte.NSD
     left join #B p2 on p1.Account=p2.Account

1. Это хорошо работает! Это действительно быстрее и возвращает то же количество записей. Я пытался использовать CTE, но потом почему-то подумал, что это бесполезно …. но теперь я вижу причину, по которой это полезно, заключается в применении функции group-by к каждому условию where, но при этом механизм запросов должен был вычислять ее только один раз. Спасибо!

2. Помните, что всегда лучше (по крайней мере, с DBS) пытаться выполнить анализ набора по одной строке.

3. В качестве последующего вопроса, допустим, теперь я хочу выбрать другой столбец, например ‘EmployeeID’, в конечном запросе из #A …. или я хочу выбрать больше дополнительных столбцов. Будет ли этот метод неэффективным, потому что, если # A — это очень большой набор данных, мне кажется, что я запрашиваю его дважды, что замедлилось бы при запросе многих столбцов?

4. На самом деле нет никакой дополнительной нагрузки по добавлению столбцов из #A в окончательный выбор. Работа уже выполнена.

5. Когда я выбираю только эти 3 столбца AccountId и AccountDesc и CreateDate, как показано здесь, мой запрос занимает около 30 секунд. Когда я выбираю еще кучу столбцов, таких как «Выберите AccountId, AccountDesc, ColA, ColB, ColC, ColD, ColE, ColF, ColG …», запрос выполняется в течение 3 минут, и новое изменение с присоединением # B ничего не изменило.

Ответ №3:

Я верю, что это поможет вам

 Select AccountID,AccountDesc,
     CASE WHEN NOT EXISTS (select AccountID from #B where #B.AccountID = P1.AccountID) THEN cast(cast(GETDATE() as date) as varchar)     
     ELSE cast(cast(CreateDate as date) as varchar) END 
  FROM #A P1
    CROSS APPLY (
                  SELECT max(EndDate) EndDate, max(StartDate) StartDate, max(CreateDate ) CreateDate ,max(NewStartDate)NewStartDate 
                  FROM #A P2
                  WHERE P1.AccountID = P2.AccountID and P2.Categ1 = 'F'
                 ) AS P3
    WHERE P1.EndDate = P3.EndDate AND P1.StartDate = P3.StartDate
    AND P1.CreateDate = P3.CreateDate AND P1.NewStartDate = p3.NewStartDate