处理20万条记录的查询非常非常缓慢

Question

处理20万条记录的查询非常非常缓慢

sqlsql-serversql-server-2008sql-server-2005azure-sql-database

3

我有20万行Patient和Person表，查询需要30秒才能执行。

在Person表中，我定义了主键（并聚集索引）为PersonId，在Patient表中则为PatientId。这里还有什么可以做来改善我的程序性能呢？

我是数据库开发方面的新手。我只知道基本的SQL语句。而且不确定SQL Server是否可以快速处理20万行数据。

完整的动态过程可以在https://github.com/Padayappa/SQLProblem/blob/master/Performance上看到。

有人处理过像这样的大量数据吗？我该如何提高性能？

DECLARE @return_value int,
        @unitRows bigint,
        @unitPages int,
        @TenantId int,
        @unitItems int,
        @page int   
SET @TenantId = 1
SET @unitItems = 20
SET @page = 1

DECLARE @PatientSearch TABLE(
    [PatientId] [bigint] NOT NULL,
    [PatientIdentifier] [nvarchar](50) NULL,
    [PersonNumber] [nvarchar](20) NULL,
    [FirstName] [nvarchar](100) NOT NULL,
    [LastName] [nvarchar](100) NOT NULL,
    [ResFirstName] [nvarchar](100) NOT NULL,
    [ResLastName] [nvarchar](100) NOT NULL,
    [AddFirstName] [nvarchar](100) NOT NULL,
    [AddLastName] [nvarchar](100) NOT NULL,
    [Address] [nvarchar](255) NULL,
    [City] [nvarchar](50) NULL,
    [State] [nvarchar](50) NULL,
    [ZipCode] [nvarchar](20) NULL,
    [Country] [nvarchar](50) NULL,
    [RowNumber] [bigint] NULL
    ) 

    INSERT INTO @PatientSearch SELECT  PAT.PatientId  
     ,PAT.PatientIdentifier      
     ,PER.PersonNumber  
     ,PER.FirstName  
     ,PER.LastName  
     ,RES_PER.FirstName AS ResFirstName  
     ,RES_PER.LastName AS ResLastName  
     ,ADD_PER.FirstName AS AddFirstName  
     ,ADD_PER.LastName AS AddLastName  
     ,PER.Address  
     ,PER.City  
     ,PER.State  
     ,PER.ZipCode  
     ,PER.Country
     ,ROW_NUMBER() OVER (ORDER BY PAT.PatientId DESC) AS RowNumber 
  FROM  dbo.Patient AS PAT  
  INNER JOIN dbo.Person AS PER  
    ON PAT.PersonId = PER.PersonId  
  INNER JOIN  dbo.Person AS RES_PER  
             ON  PAT.ResponsiblePersonId = RES_PER.PersonId  
  INNER JOIN  dbo.Person AS ADD_PER  
             ON  PAT.AddedBy = ADD_PER.PersonId 
  INNER JOIN dbo.Booking AS B   
             ON PAT.PatientId = B.PatientId 

  WHERE  PAT.TenantId = @TenantId AND B.CategoryId =  @CategoryId 

  GROUP BY PAT.PatientId  
     ,PAT.PatientIdentifier      
     ,PER.PersonNumber  
     ,PER.FirstName  
     ,PER.LastName  
     ,RES_PER.FirstName 
     ,RES_PER.LastName
     ,ADD_PER.FirstName 
     ,ADD_PER.LastName
     ,PER.Address  
     ,PER.City  
     ,PER.State  
     ,PER.ZipCode  
     ,PER.Country      

  ;  

   SELECT @unitRows = @@ROWCOUNT  
     ,@unitPages = (@unitRows / @unitItems) + 1;  

   SELECT *  
   FROM @PatientSearch AS IT  
   WHERE RowNumber BETWEEN (@page - 1) * @unitItems + 1 AND @unitItems * @page

- Billa

你要插入多少行到你的表变量中？创建一个实际的临时表(#PatientSearch)可能更好。原因是查询优化器总是假定表变量只有一行（它缺乏任何统计信息），因此如果你向表变量中插入大量行，则性能会非常糟糕。 - marc_s

@marc_s，它实际上会为空搜索插入所有200000行 :( - Billa

1

顺便说一句：200,000行并不是绝对的巨大量......如果你说的是2000亿行 - 那么，那将是一个相当大的数据库。 - marc_s

@marc_s，REAL Temp table 意味着使用 CREATE Table #PatientSearch 而不是 DECLARE @patientsearch as Table 吗？ - Billa

是的 - 因为在这样的临时表上，您可以定义索引（如果有用），并且这些表具有统计信息 - 但是最好的方法是尽可能避免复制那200,000行数据！ - marc_s

显示剩余3条评论

2个回答

0

坦白说，对于 SQL Server 来说，20万行根本不算什么。

请先删除逻辑冗余，比如你已经有了主键，为什么还要分组这么多列，为什么需要三次连接同一张表（person）？

在删除逻辑冗余后，你需要至少创建一些复合索引/包含索引。获取执行计划（CTRL+M）或（CTRL+M），查看你错过的索引。如果需要进一步帮助，请粘贴你的表模式和几行示例数据。

- ljh

1个连接给我患者的名字，第2个连接是添加人的名字，第3个连接是负责医生的名字。 - Billa

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Adriaan Stander · Accepted Answer

嗯，除非我漏掉了什么（比如重复的行？），否则您应该能够删除 GROUP BY

GROUP BY PAT.PatientId  
     ,PAT.PatientIdentifier      
     ,PER.PersonNumber  
     ,PER.FirstName  
     ,PER.LastName  
     ,RES_PER.FirstName 
     ,RES_PER.LastName
     ,ADD_PER.FirstName 
     ,ADD_PER.LastName
     ,PER.Address  
     ,PER.City  
     ,PER.State  
     ,PER.ZipCode  
     ,PER.Country

由于您在选择列表中对所有字段进行了分组，并且您正在按照 PAT.PatientId 进行分区，因此需要进一步操作。

除此之外，您还应该在包含您连接/筛选的列的表上创建索引。

例如，我会在患者表上创建一个包含列 (TenantId,PersonId,ResponsiblePersonId,AddedBy) 和包含列 (PatientId,PatientIdentifier) 的索引。