SQL查询优化

当有8000行要处理时，此报告过去大约需要16秒 . 现在有50000行，报告需要2:30分钟 .

这是我的第一次传递，客户端昨天需要它，所以我按照需要完成的逻辑顺序编写了这段代码，但没有考虑优化 .

现在随着数据的增加，报告需要更长的时间，我需要再看一下并优化它 . 我正在考虑索引视图，表函数等 .

我认为最大的瓶颈是循环访问临时表，制作4个select语句，并更新临时表... 50,000次 .

我想我可以将所有这些压缩成一个大的SELECT，并且（a）4个连接到同一个表以获得4个状态，但是后来我不知道如何在那里获得TOP 1，或者我可以尝试（b ）使用嵌套子查询，但与当前代码相比，两者看起来都很混乱 .

我不希望任何人为我编写代码，但是如果一些SQL专家可以仔细阅读这些代码并告诉我任何明显的低效率和替代方法，或者加快速度的方法，或者我应该使用的技术，那就是赞赏 .

PS: Assume that this DB is for the most part normalized, but poorly designed, and that I am not able to add indexes. I basically have to work with it, as is.

Where the code says (less than) I had to replace a "less than" symbol because it was cropping some of my code.

谢谢！

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;