Click here to Skip to main content
15,071,262 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:
SQL
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);


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.
Posted
Updated 11-Oct-13 0:13am
v2
Comments
ArunRajendra 11-Oct-13 6:34am
   
Have you confirmed that the problem is in the database? Because some the data retrieval is fast but the output rendering is slow.
preetpal kapoor 11-Oct-13 7:13am
   
What kind of problem can be there in Database ? can you please elaborate.
ArunRajendra 11-Oct-13 7:21am
   
I mean if running this query directly in the management studio does it take long time to return?
preetpal kapoor 11-Oct-13 7:29am
   
if I simply write select * from report_table; it takes 50 to 60 sec to return the full result set.
Mike Meinz 11-Oct-13 8:18am
   
All of those IN predicates in the Where clause are suspect. Do you really mean to ask the SQL Server to process IN predicates for eight columns. Did you really mean to use = sign?

Is there an index that you could use in a "Query Hint" to help SQL Server narrow down the number of rows to be examined?
preetpal kapoor 11-Oct-13 9:16am
   
I have created a NONclustered index..

create index in_report opn report_table (Identificationdate,
Custname,City,state,LOB,prod,Subprod ,contentID,AlertID
) include (
address
,street
,phone
,pincode
,delivered_counts
,undelivered_counts
,SMS
)

This index helped me to shorten the time to 8 min. But need to short it more...
preetpal kapoor 11-Oct-13 9:18am
   
Also the distinct data in the variable(like custname,city,state .. etc. more) is quite large amount (more tahn 10000)..
Mike Meinz 11-Oct-13 9:28am
   
I believe the ISNULL() function on the left side of each search condition causes a table scan. I read somewhere that variables or functions on the left side of a search condition causes table scans.

Avoid SQL Server function in where clause

Optimizing SQL

SQL Server Optimization Tips

Many more available via Google search


In the search conditions, where you have eight IN predicates, are those variables (like @custname, @city, etc.) really lists of values or just one value. If just one value, use = sign instead of IN.
preetpal kapoor 11-Oct-13 10:11am
   
ISNULL function is used on left side in the query ... u mean that I should use it on right side???..

Also I cnt use '=' as there are list of values in variables.
Mike Meinz 11-Oct-13 10:15am
   
No, I mean do not use ISNULL function in the Where clause.

Use of IN predicate will be another reason for performance issues.
preetpal kapoor 11-Oct-13 10:31am
   
I used coalesce , but the query was giving some error when I ran it in the SSRS report. And I cnt remove IN becz the variables are having list of data into it.
Mike Meinz 11-Oct-13 11:21am
   
Why use ISNULL or any other function? Just use the column name!
preetpal kapoor 14-Oct-13 2:38am
   
ISNULL is required to track if there is any NULL values and I have converted all the NULL values to 'NULL' (i.e. a string value).. It was the requirement :)

1 solution

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

SQL
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
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900