SQL查询优化

问题描述：

当有8000行要处理时，此报告大约需要16秒。现在有50000行，报告需要2:30分钟。SQL查询优化

这是我第一次通过这个，客户需要它，所以我按照需要完成的逻辑顺序编写了这段代码，但没有考虑优化。

现在随着数据的增加，报告花费的时间越来越长，我需要再看看这个并优化它。我正在考虑索引视图，表函数等

我认为最大的瓶颈是循环通过临时表，使4选择语句，并更新临时表... 50,000次。

我想我可以将所有这一切压缩成一个大的SELECT，或者（a）4个连接到同一个表以获得4个状态，但是我不知道如何获得TOP 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 Server？ – Lamak 2011-02-07 17:32:03

SQL 2008 R2是该版本。 – Blackcoil 2011-02-07 17:35:16

答

首先使用临时表而不是可变表。这些可以被索引。

接下来，不要循环！几乎在所有情况下，循环对性能都不利。对于50000条记录，这个循环运行50000次而不是一次，当你有一百万条记录时，这将会是可怕的！这是一个链接，可以帮助您了解如何进行基于集合的处理。它的写法是为了避免cursos，但循环与光标相似，所以它应该有所帮助。 http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them

而且（nolock）会给出肮脏的数据读取，这可能对报告非常不利。如果您的SQl Server版本高于2000，则有更好的选择。

答

不要试图去猜测其中查询是哪里错了 - 看看执行计划。它会告诉你什么是咀嚼你的资源。
您可以直接从另一个表更新，即使从表变量：SQL update from one Table to another based on a ID match

这将使你在你的循环都合并成一个单一的（块状）语句。您可以使用不同的别名，如加入为二级和三级状态相同的表，

JOIN AccountStatus As TertiaryAccountStatus...AND a.StatusType = 'Tertiary Status' 
JOIN AccountStatus AS SecondaryAccountStatus...AND a.StatusType = 'Secondary Status'

我敢打赌，你没有对AccountStatus的索引。 StatusType字段。您可以尝试使用该表的PK。

HTH。

开始=“3”>

答

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

相关推荐