How to optimize this query in sql server 2008

Question

2.00/5 (1 vote)

See more:

SQL

select COUNT(id) as tablecount from tbl_Panelist where tbl_Panelist.IsTrash='0' and  tbl_Panelist.Subscribe='1' and tbl_Panelist.Pending='0' and tbl_Panelist.UserName not in (select distinct UserName from tbl_PanelistActivity where tbl_PanelistActivity.ActivityDate>(GETDATE()-180))

Posted 3-Jan-14 0:06am

maneesh katiyar

Updated 3-Jan-14 0:09am

phil.o

v2

Add a Solution

3 solutions

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)

Christian Graus · Answer 1 · 2014-01-03T10:45:00

1 - don't store things like IsTrash as a string. Use a bit if that's what you're storing ( 1 or 0 )

2 - try adding an index to every column you're checking to see if it speeds things up

3 - don't use DISTINCT if you can avoid it. It's not needed here, the DB does not need to filter the list to be distinct before checking on it.

4 - EXISTS is indeed faster, but getting rid of subqueries is better:

SQL

select COUNT(id) as tablecount from tbl_Panelist pl
left join tbl_PanelistActivity pa on pa.UserName = pl.UserName and pa.ActivityDate > (GETDATE()-180)
where pl.IsTrash='0' and  pl.Subscribe='1' and pl.Pending='0' and pa.Username is null

this uses a left join, if the pa values are null, then the values did not exist in the pa table.

5 - give your users a numeric id so the DB can filter on that, instead of reusing the username everywhere. What if someone changes their username, do you change it through the DB ? A numeric id will keep the records with the old username, associated with the new one.

GuyThiebaut · Answer 2 · 2014-01-03T00:18:00

It's better to use a not exists in this sort of scenario as you will not be iterating through every row in PanelistActivity:

SQL

select COUNT(id) as tablecount
from tbl_Panelist where tbl_Panelist.IsTrash='0'
and  tbl_Panelist.Subscribe='1'
and tbl_Panelist.Pending='0'
and not exists
(select UserName
from tbl_PanelistActivity
where tbl_PanelistActivity.ActivityDate>(GETDATE()-180)
and tbl_PanelistActivity.UserName = tbl_Panelist.UserName)

thatraja · Answer 3 · 2014-01-03T03:43:00

You should optimize your database too using Index, stored procedure, etc.,.
Check this for more
How To: Optimize SQL Queries[^]

0) From your query, the fields(IsTrash, Subscribe, Pending) are numeric or text datatype? If the values only 0,1 or only numbers then use numeric datatype.
1) You should use UserID column instead of UserName in subquery condition.
2) Agree with GuyThiebaut. You should use EXISTS instead of IN.

How to optimize this query in sql server 2008

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0