Optimization of query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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 11-Oct-13 0:08am

preetpal kapoor

Updated 11-Oct-13 0:13am

phil.o

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

coded007 · Answer 1 · 2013-10-17T20:33:00

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