問題描述
我正在使用下面的查詢返回用于分頁的行數，它工作正常但需要很長時間才能返回，因為所有表都有數百萬條記錄.目前返回 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.
這篇關于從包含大量數據的查詢中獲取行數的優化方法的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！