Оптимизация SQL-запросов

Раньше этот отчет занимал около 16 секунд при обработке 8000 строк. Теперь есть 50000 строк, и отчет занимает 2:30 минут.

Это был мой первый проход, и клиенту он понадобился вчера, поэтому я написал этот код в логическом порядке, в котором необходимо было сделать, но без учета оптимизации.

Теперь, когда отчет занимает больше времени по мере увеличения данных, мне нужно еще раз взглянуть на это и оптимизировать его. Я думаю об индексированных представлениях, табличных функциях и т. Д.

Я думаю, что самым большим узким местом является цикл по временной таблице, выполнение 4 операторов выбора и обновление временной таблицы ... 50 000 раз.

Я думаю, что могу объединить ВСЕ это в один большой SELECT с помощью (a) 4 присоединений к одной и той же таблице, чтобы получить 4 статуса, но тогда я не уверен, как получить ТОП 1 там, или я могу попробовать (b ) с использованием вложенных подзапросов, но оба кажутся действительно беспорядочными по сравнению с текущим кодом.

Я не ожидаю, что кто-то напишет для меня код, но если некоторые эксперты по SQL могут просмотреть этот код и рассказать мне о любых очевидных недостатках и альтернативных методах, или способах ускорить это, или методах, которые я должен использовать вместо этого, это будет оценен.

PS: Предположим, что эта БД по большей части нормализована, но плохо спроектирована, и что я не могу добавлять индексы. Мне в основном приходится с этим работать, как есть.

Там, где в коде указано (меньше), мне пришлось заменить символ «меньше», потому что он обрезал часть моего кода.

Спасибо!

CREATE PROCEDURE RptCollectionAccountStatusReport AS

SET NOCOUNT ON;

DECLARE @Accounts TABLE
(
  [AccountKey] INT IDENTITY(1,1) NOT NULL,
  [ManagementCompany] NVARCHAR(50),
  [Association] NVARCHAR(100),
  [AccountNo] INT UNIQUE,
  [StreetAddress] NVARCHAR(65),
  [State] NVARCHAR(50),
  [PrimaryStatus] NVARCHAR(100),
  [PrimaryStatusDate] SMALLDATETIME,
  [PrimaryDaysRemaining] INT,
  [SecondaryStatus] NVARCHAR(100),
  [SecondaryStatusDate] SMALLDATETIME,
  [SecondaryDaysRemaining] INT,
  [TertiaryStatus] NVARCHAR(100),
  [TertiaryStatusDate] SMALLDATETIME,
  [TertiaryDaysRemaining] INT,
  [ExternalStatus] NVARCHAR(100),
  [ExternalStatusDate] SMALLDATETIME,
  [ExternalDaysRemaining] INT
);

INSERT INTO
  @Accounts (
    [ManagementCompany],
    [Association],
    [AccountNo],
    [StreetAddress],
    [State])
SELECT
  mc.Name AS [ManagementCompany],
  a.LegalName AS [Association],
  c.CollectionKey AS [AccountNo],
  u.StreetNumber + ' ' + u.StreetName AS [StreetAddress],
  CASE WHEN c.InheritedAccount = 1 THEN 'ZZ' ELSE u.State END AS [State]
FROM
  ManagementCompany mc WITH (NOLOCK)
JOIN
  Association a WITH (NOLOCK) ON a.ManagementCompanyKey = mc.ManagementCompanyKey
JOIN
  Unit u WITH (NOLOCK) ON u.AssociationKey = a.AssociationKey
JOIN
  Collection c WITH (NOLOCK) ON c.UnitKey = u.UnitKey
WHERE
  c.Closed IS NULL;

DECLARE @MaxAccountKey INT;
SELECT @MaxAccountKey = MAX([AccountKey]) FROM @Accounts;

DECLARE @index INT;
SET @index = 1;

WHILE @index (less than) @MaxAccountKey BEGIN

DECLARE @CollectionKey INT;
SELECT @CollectionKey = [AccountNo] FROM @Accounts WHERE [AccountKey] = @index;

DECLARE @PrimaryStatus NVARCHAR(100) = NULL;
DECLARE @PrimaryStatusDate SMALLDATETIME = NULL;
DECLARE @PrimaryDaysRemaining INT = NULL;
DECLARE @SecondaryStatus NVARCHAR(100) = NULL;
DECLARE @SecondaryStatusDate SMALLDATETIME = NULL;
DECLARE @SecondaryDaysRemaining INT = NULL;
DECLARE @TertiaryStatus NVARCHAR(100) = NULL;
DECLARE @TertiaryStatusDate SMALLDATETIME = NULL;
DECLARE @TertiaryDaysRemaining INT = NULL;
DECLARE @ExternalStatus NVARCHAR(100) = NULL;
DECLARE @ExternalStatusDate SMALLDATETIME = NULL;
DECLARE @ExternalDaysRemaining INT = NULL;

SELECT TOP 1
@PrimaryStatus = a.StatusName, @PrimaryStatusDate = c.StatusDate, @PrimaryDaysRemaining = c.DaysRemaining
FROM CollectionAccountStatus c WITH (NOLOCK) JOIN AccountStatus a WITH (NOLOCK) ON c.AccountStatusKey = a.AccountStatusKey
WHERE c.CollectionKey = @CollectionKey AND a.StatusType = 'Primary Status' AND a.StatusName  'Cleared'
ORDER BY c.sysCreated DESC;

SELECT TOP 1
@SecondaryStatus = a.StatusName, @SecondaryStatusDate = c.StatusDate, @SecondaryDaysRemaining = c.DaysRemaining
FROM CollectionAccountStatus c WITH (NOLOCK) JOIN AccountStatus a WITH (NOLOCK) ON c.AccountStatusKey = a.AccountStatusKey
WHERE c.CollectionKey = @CollectionKey AND a.StatusType = 'Secondary Status' AND a.StatusName  'Cleared'
ORDER BY c.sysCreated DESC;

SELECT TOP 1
@TertiaryStatus = a.StatusName, @TertiaryStatusDate = c.StatusDate, @TertiaryDaysRemaining = c.DaysRemaining
FROM CollectionAccountStatus c WITH (NOLOCK) JOIN AccountStatus a WITH (NOLOCK) ON c.AccountStatusKey = a.AccountStatusKey
WHERE c.CollectionKey = @CollectionKey AND a.StatusType = 'Tertiary Status' AND a.StatusName  'Cleared'
ORDER BY c.sysCreated DESC;

SELECT TOP 1
@ExternalStatus = a.StatusName, @ExternalStatusDate = c.StatusDate, @ExternalDaysRemaining = c.DaysRemaining
FROM CollectionAccountStatus c WITH (NOLOCK) JOIN AccountStatus a WITH (NOLOCK) ON c.AccountStatusKey = a.AccountStatusKey
WHERE c.CollectionKey = @CollectionKey AND a.StatusType = 'External Status' AND a.StatusName  'Cleared'
ORDER BY c.sysCreated DESC;

UPDATE
  @Accounts
SET
  [PrimaryStatus] = @PrimaryStatus,
  [PrimaryStatusDate] = @PrimaryStatusDate,
  [PrimaryDaysRemaining] = @PrimaryDaysRemaining,
  [SecondaryStatus] = @SecondaryStatus,
  [SecondaryStatusDate] = @SecondaryStatusDate,
  [SecondaryDaysRemaining] = @SecondaryDaysRemaining,
  [TertiaryStatus] = @TertiaryStatus,
  [TertiaryStatusDate] = @TertiaryStatusDate,
  [TertiaryDaysRemaining] = @TertiaryDaysRemaining,
  [ExternalStatus] = @ExternalStatus,
  [ExternalStatusDate] = @ExternalStatusDate,
  [ExternalDaysRemaining] = @ExternalDaysRemaining
WHERE
  [AccountNo] = @CollectionKey;

SET @index = @index + 1;

END;

SELECT
  [ManagementCompany],
  [Association],
  [AccountNo],
  [StreetAddress],
  [State],
  [PrimaryStatus],
  CONVERT(VARCHAR, [PrimaryStatusDate], 101) AS [PrimaryStatusDate],
  [PrimaryDaysRemaining],
  [SecondaryStatus],
  CONVERT(VARCHAR, [SecondaryStatusDate], 101) AS [SecondaryStatusDate],
  [SecondaryDaysRemaining],
  [TertiaryStatus],
  CONVERT(VARCHAR, [TertiaryStatusDate], 101) AS [TertiaryStatusDate],
  [TertiaryDaysRemaining],
  [ExternalStatus],
  CONVERT(VARCHAR, [ExternalStatusDate], 101) AS [ExternalStatusDate],
  [ExternalDaysRemaining]
FROM
 @Accounts
ORDER BY
  [ManagementCompany],
  [Association],
  [StreetAddress]
ASC;

sql sql-server tsql query-optimization
person Blackcoil    schedule 07.02.2011    source источник
comment
Какую версию SQL Server вы используете?   -  person Lamak    schedule 07.02.2011
comment
ПОЖАЛУЙСТА, НЕ ИСПОЛЬЗУЙТЕ NOLOCK - это приведет к неверным результатам!   -  person Milney    schedule 16.03.2017

Ответы (3)


arrow_upward
3
arrow_downward

Сначала используйте временную таблицу вместо переменной таблицы. Их можно проиндексировать.

Далее не зацикливайтесь! Зацикливание плохо сказывается на производительности практически во всех случаях. Этот цикл выполнялся 50000 раз, а не один раз для 50000 записей, это будет ужасно, когда у вас будет миллион записей! Вот ссылка, которая поможет вам понять, как вместо этого выполнять обработку на основе наборов. Он написан, чтобы избежать использования курсоров, но циклы похожи на курсоры, поэтому они должны помочь. http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them

И (nolock) даст чтение грязных данных, что может быть очень плохим для отчета. Если вы используете версию SQl Server выше 2000, есть варианты получше.

person HLGEM    schedule 07.02.2011

arrow_upward
4
arrow_downward

  1. Не пытайтесь угадать, в чем ошибка запроса - посмотрите на план выполнения. Он скажет вам, что пожирает ваши ресурсы.

  2. Вы можете обновлять непосредственно из другой таблицы, даже из переменной таблицы: Обновление SQL из одной таблицы в другую на основе совпадения идентификатора

Это позволит вам объединить все в вашем цикле в один (массивный) оператор. Вы можете присоединиться к одним и тем же таблицам для вторичного и третичного статусов, используя разные псевдонимы, например,

JOIN AccountStatus As TertiaryAccountStatus...AND a.StatusType = 'Tertiary Status'
JOIN AccountStatus AS SecondaryAccountStatus...AND a.StatusType = 'Secondary Status'
  1. Готов поспорить, у вас нет индекса в поле AccountStatus.StatusType. Вместо этого вы можете попробовать использовать PK этой таблицы.

HTH.

person Chris B. Behrens    schedule 07.02.2011

arrow_upward
2
arrow_downward 

SELECT @CollectionKey = [AccountNo] FROM @Accounts WHERE [AccountKey] = @index;

Этот запрос выиграет от объявления PRIMARY KEY в переменной таблицы.

  • Когда вы говорите IDENTITY, вы просите базу данных автоматически заполнить столбец.
  • Когда вы говорите PRIMARY KEY, вы просите базу данных организовать данные в кластерный индекс.

Эти две концепции очень разные. Как правило, следует использовать их оба.

DECLARE @Accounts TABLE
(
  [AccountKey] INT IDENTITY(1,1) PRIMARY KEY,

Я не могу добавлять индексы.

В этом случае скопируйте данные в базу данных, где вы можете добавить индексы. И используйте: SET STATISTICS IO ON

person Amy B    schedule 07.02.2011
comment
Этот отчет создается 4–5 раз в день руководителями компании и должен указывать на производственную базу данных. Это не разовый отчет, поэтому копирование данных в другую БД не вариант. - person Blackcoil; 07.02.2011
comment
Если вы используете временную таблицу, вы можете индексировать. - person HLGEM; 07.02.2011