How to optimize this query in sql server 2008

Question

0.00/5 (No votes)

See more:

SQL

ALTER proc [dbo].[SurveyImportListStatistics]
as
select distinct ListName,
(
select count(id) from tbl_SurveyAssign
where ListName=a.ListName and UserName in( select UserName from tbl_Panelist where tbl_Panelist.Subscribe='1' and tbl_Panelist.Pending='0' and tbl_Panelist.UserName in (select UserName from tbl_PanelistActivity where tbl_PanelistActivity.ActivityDate>(GETDATE()-180)) )
) as Active,
(
select count(id) from tbl_SurveyAssign
where ListName=a.ListName and UserName in( select UserName from tbl_Panelist where tbl_Panelist.Subscribe='1' and tbl_Panelist.Pending='1' )
) as Pending,
(
select count(id) from tbl_SurveyAssign
where ListName=a.ListName and UserName in( select UserName from tbl_Panelist where tbl_Panelist.Subscribe='0' and tbl_Panelist.Pending='0')
) as UnSubscribe,
(
select count(id) from tbl_SurveyAssign
where ListName=a.ListName and UserName in( select UserName from tbl_Panelist where tbl_Panelist.Subscribe='1' and tbl_Panelist.Pending='0' and tbl_Panelist.UserName not in (select UserName from tbl_PanelistActivity where tbl_PanelistActivity.ActivityDate>(GETDATE()-180)))
) as Inactive,
(
select count(id) from tbl_SurveyAssign
where ListName=a.ListName
) as Total,
(
select count(id) from tbl_SurveyAssign
where ListName=a.ListName and UserName not in (Select UserName from tbl_Panelist )
) as NotMember

from tbl_SurveyAssign a

Posted 28-Dec-13 18:46pm

maneesh katiyar

Add a Solution

Comments

maneesh katiyar 29-Dec-13 0:48am

its taking too much time to execute when tables large amount of data

maneesh katiyar 29-Dec-13 0:49am

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Christian Graus 29-Dec-13 14:17pm

This was deleted, and has been restored. Along the way, I didn't spot my answer was *wrong*. It's fixed now, the point was to lose the sub queries. I don't expect I've solved your issues, but I've pointed you in the right direction. Feel free to reply for more info, I need more info from you, to help more.

2 solutions

Solution 1

Run your query in the profiler and use the index tuning wizard : http://msdn.microsoft.com/en-us/library/ff650692.aspx[^]

Posted 28-Dec-13 19:07pm

Mehdi Gholam

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 · Accepted Answer · 2013-12-28T19:17:00

The query profiler won't be able to do much with this mess. You have three levels of queries. As an example:

SQL

select count(id) from tbl_SurveyAssign
where ListName=a.ListName and UserName in( select UserName from tbl_Panelist where tbl_Panelist.Subscribe='1' and tbl_Panelist.Pending='0' and tbl_Panelist.UserName in (select UserName from tbl_PanelistActivity where tbl_PanelistActivity.ActivityDate>(GETDATE()-180)) )
) as Active,

can be rewritten as

SQL

select count(id) from tbl_SurveyAssign
inner join tbl_Panelist on tbl_Panelist.xxx = tbl_SurveyAssign.xxx
inner join  tbl_PanelistActivity on tbl_PanelistActivity.UserName = tbl_Panelist.Username
where ListName=a.ListName and tbl_Panelist.Subscribe='1' and tbl_Panelist.Pending='0' 
and tbl_PanelistActivity.ActivityDate>(GETDATE()-180)

And then that can be factored back out as:

SQL

with Active
(
select uniqueId, count(id) as Active from tbl_SurveyAssign
inner join tbl_Panelist on tbl_Panelist.xxx = tbl_SurveyAssign.xxx
inner join  tbl_PanelistActivity on tbl_PanelistActivity.UserName = tbl_Panelist.Username
where ListName=a.ListName and tbl_Panelist.Subscribe='1' and tbl_Panelist.Pending='0' 
and tbl_PanelistActivity.ActivityDate > (GETDATE()-180)
)

select ListName, Active.Active
from tbl_SurveyAssign sa
inner join Active a on a.uniqueId = sa.uniqueId
group by ListName

This won't run, I don't know enough about your schema. But you can use CTEs to create tables that represent each value you want to pull out, and then join them all together.

Some more points:

Don't join on a username, which is a string. create a userid for each user, so that if they change their username, the whole DB does not need updating, and so things are faster.

Dont call tables tbl_xxx. Everyone knows it's a table.

Use aliases all the way through, instead of

inner join tbl_Panelist on tbl_Panelist.xxx = tbl_SurveyAssign.xxx

do

inner join tbl_Panelist p on p.xxx = sa.xxx

OF course, xxx is the column that can be used to join records. Without your schema, I don't know what that is.