从包含大量数据的查询中获取行数的优化方法

问题描述

我正在使用下面的查询返回用于分页的行数，它工作正常但需要很长时间才能返回，因为所有表都有数百万条记录.目前返回 rowcount 需要 7 秒，任何人都可以帮助我快速返回它.

i am using the below query to return rowcount for paging, it works fine but take very long to return, because all of the table have millions of records. currently its taking 7 sec to return rowcount, can anyone help me in this to return it fast.

我也尝试过同样的查询，#table 和 @table 都很慢.查询是

i have also tried same query with #table and @table both are slow. query is

WITH cte_rowcount 
     AS (SELECT p.policyid 
         FROM   resident (nolock) r 
                INNER JOIN resident_policy (nolock) rp 
                        ON r.residentid = rp.residentid 
                INNER JOIN policy (nolock) p 
                        ON p.policyid = rp.policyid 
                --INNER JOIN PolicySource (NOLOCK) psourse ON p.PolicySourceID = psourse.PolicySourceId 
                INNER JOIN policy_locations (nolock) pl 
                        ON pl.policyid = p.policyid 
                INNER JOIN location (nolock) l 
                        ON pl.locationid = l.locationid 
                --INNER JOIN Policy_Status (NOLOCK) ps ON ps.PolicyStatusId = p.PolicyStatusId 
                INNER JOIN property (nolock) pr 
                        ON pr.propertyid = l.propertyid 
         --INNER JOIN dbo.States (NOLOCK) s ON s.StateId = pr.StateId 
         WHERE  r.primary_resident = 0x1 
                AND ( ( @ResidentFirstName IS NULL ) 
                       OR R.firstname LIKE @ResidentFirstName + '%' ) 
                AND ( ( @ResidentLastName IS NULL ) 
                       OR R.firstname LIKE @ResidentLastName + '%' ) 
                AND ( @PropertyAddress IS NULL 
                       OR pr.address LIKE @PropertyAddress + '%' ) 
                AND ( @Policynumber IS NULL 
                       OR p.policynumber LIKE @Policynumber + '%' ) 
                AND ( @LocationAddress IS NULL 
                       OR l.address2 LIKE @LocationAddress + '%' ) 
                AND ( @City IS NULL 
                       OR pr.city LIKE @City + '%' ) 
                AND ( @ZipCode IS NULL 
                       OR pr.zipcode = @ZipCode ) 
                AND ( @StateId IS NULL 
                       OR pr.stateid = @StateId ) 
                AND ( @PolicyStatusId IS NULL 
                       OR p.policystatusid = @PolicyStatusId )) 
SELECT @rowcount = Count(*) 
FROM   cte_rowcount 

推荐答案

我会说查看索引，但它可能不会有太大帮助，因为 a) 您可能已经这样做了，并且 b) 您可以这种查询没有搜索，只有扫描.

I'd say to look at the indexes, but it probably won't help much, because a) you probably did it already, and b) you can get no seeks with this kind of a query, only scans.

这个想法是摆脱这些 OR 并让优化器产生一个合理的计划.

The idea is to get rid of these ORs and allow the optimizer to produce a sound plan.

有两种选择.

不知道哪个版本的 SQL Server 有问题，但如果它是 SQL 2008 SP1 CU5 (10.0.2746) 或更高版本，或者 SQL 2008 R2 CU1 (10.50.1702) 或更高版本，或者任何更新的版本，将 option (recompile) 添加到查询中.这应该会产生更好的计划，在相关索引上使用搜索.

Don't know which version of SQL Server is in question, but if it's SQL 2008 SP1 CU5 (10.0.2746) or later, or SQL 2008 R2 CU1 (10.50.1702) or later, or anything newer than that, add an option (recompile) to the query. This should produce much better plan, using seeks on relevant indexes.

然而，这会给每次执行增加一些重新编译的开销，所以也许第二个选项更好.

This will, however, add some recompile overhead to every execution, so maybe the second option is better.

您可以将查询重写为动态查询，并在优化器甚至看到查询之前消除 NULL 参数.我试图重写您的查询，没有您的数据，因此无法对其进行测试，并且其中可能存在一些错误，但您还是会明白我的意图.我不得不猜测数据类型.(顺便说一句，SELECT p.policyid 是否有特定原因?)

You can rewite the query into dynamic one, and elliminate the NULL parameters before optimizer even see the query. I tried to rewrite your query, don't have your data so can't test it, and there may be some errors in it, but you'll get my intention nevertheless. And I had to guess the datatypes. (BTW, is there a specific reason for SELECT p.policyid?)

这是:

declare @qry nvarchar(4000), @prms nvarchar(4000);
set @qry = N'
SELECT count(*)
         FROM   resident (nolock) r 
                INNER JOIN resident_policy (nolock) rp 
                        ON r.residentid = rp.residentid 
                INNER JOIN policy (nolock) p 
                        ON p.policyid = rp.policyid 
                INNER JOIN policy_locations (nolock) pl 
                        ON pl.policyid = p.policyid 
                INNER JOIN location (nolock) l 
                        ON pl.locationid = l.locationid 
                INNER JOIN property (nolock) pr 
                        ON pr.propertyid = l.propertyid 
         WHERE  r.primary_resident = 0x1 '
if @ResidentFirstName IS NOT NULL
    set @qry = @qry + ' AND R.firstname LIKE @ResidentFirstName + ''%'''  
if @ResidentLastName IS NOT NULL 
    set @qry = @qry + ' AND R.firstname LIKE @ResidentLastName + ''%'''
if @PropertyAddress IS NOT NULL 
    set @qry = @qry + ' AND pr.address LIKE @PropertyAddress + ''%''' 
if @Policynumber IS NOT NULL 
    set @qry = @qry + ' AND p.policynumber LIKE @Policynumber + ''%''' 
if @LocationAddress IS NOT NULL 
    set @qry = @qry + ' AND l.address2 LIKE @LocationAddress + ''%''' 
if @City IS NOT NULL 
    set @qry = @qry + ' AND pr.city LIKE @City + ''%''' 
if @ZipCode IS NOT NULL 
    set @qry = @qry + ' AND pr.zipcode = @ZipCode'
if @StateId IS NOT NULL 
    set @qry = @qry + ' AND pr.stateid = @StateId'
if @PolicyStatusId IS NOT NULL 
    set @qry = @qry + ' AND p.policystatusid = @PolicyStatusId'


set @prms = N'@PolicyStatusId int, @StateId int, @ZipCode int,
@City varchar(50), @LocationAddress varchar(50), @Policynumber varchar(50), 
@PropertyAddress varchar(50), @ResidentLastName varchar(50), @ResidentFirstName varchar(50)'

exec sp_executesql 
@qry, 
@prms,
@PolicyStatusId = @PolicyStatusId, @StateId = @StateId, @ZipCode = @ZipCode,
@City = @City, @LocationAddress = @LocationAddress, 
@Policynumber = @Policynumber, @PropertyAddress = @PropertyAddress, 
@ResidentLastName = @ResidentLastName, @ResidentFirstName = @ResidentFirstName

如果您检查执行计划，您将看到索引查找，前提是您在 WHERE 和 JOIN 列上有非聚集索引.

If you chect the execution plan you'll see the index seeks, provided you have nonclustered indexes on WHERE and JOIN columns.

此外，计划将被缓存，每个参数组合一个.

Moreover, the plan will be cached, one for each combination of parameters.

这篇关于从包含大量数据的查询中获取行数的优化方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！