优化查询 [英] Optimization of query

问题描述

我正在设计报告。该报告有12-13个过滤器，我们只从一个表中获取数据。我们使用的查询是：

Hi ,

I am designing a report . The report is having 12-13 filters and we are fetching the data from only one table. Query we are using is:

SELECT DISTINCT
  custname
 ,address
 ,street
 ,city
 ,state
 ,phone
 ,pincode
 ,delivered_counts
 ,undelivered_counts
 ,SMS
FROM report_table (NOLOCK)
WHERE
     ISNULL(Identificationdate,'NULL') >= (@StartDate)
 AND ISNULL(Identificationdate,'NULL') <= (@enddate)
 AND ISNULL(Custname,'NULL') IN (@custname)
 AND ISNULL(City,'NULL') IN (@city)
 AND ISNULL(state,'NULL') IN (@state)
 AND ISNULL(LOB,'NULL') IN (@LOB)
 AND ISNULL(prod,'NULL') IN (@prod)
 AND ISNULL(Subprod,'NULL') IN (@subprod)
 AND ISNULL(Cast(contentID as varchar(20)),'NULL') IN (@contentID)
 AND ISNULL(AlertID,'NULL') IN (@AlertID);

我还在我所拍摄的所有字段上创建了一个复杂索引在哪里条件。



现在当我运行这个查询时，需要很长时间才能获取数据。 report_table表中的总记录数为140000.



有人可以帮助优化此查询。我已经尝试过使用CTE，临时表但没有任何帮助来更快地获得结果。

I have also created a Complex Index on all the fields which I have taken in where condition.

Now When I run this query its taking long time to fetch the data. The total records present in the report_table table are 140000.

Could someone help in optimization of this query. I have tried using CTE,temp table but nothing helped in getting the result faster.

推荐答案

请尝试以下查询，这肯定会提高你的表现。

Please try the below query surely it will increase your performance.

SELECT DISTINCT
  custname
 ,address
 ,street
 ,city
 ,state
 ,phone
 ,pincode
 ,delivered_counts
 ,undelivered_counts
 ,SMS
FROM report_table (NOLOCK)
WHERE
     Identificationdate BETWEEN @StartDate  AND  @enddate
 AND Custname = @custname
 AND City = @city
 AND state = @state
 AND LOB  = @LOB
 AND prod = @prod
 AND Subprod = @subprod
 AND Cast(contentID as varchar(20)) = @contentID
 AND AlertID = @AlertID

这篇关于优化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！