Stored procedure performance

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi,

I have one query regarding stored procedure. I have 1 stored procedure with joins and it takes long time to execute and because of that my application doesn't show any data.

I have tried parameter stuffing but still it gives same result. Can anyone suggest me any solution?

Posted 20-Jul-12 21:46pm

Sweetynewb

Add a Solution

2 solutions

Solution 2

Run your queries in the SQLProfiler and have it create indexes for you : http://msdn.microsoft.com/en-us/library/ff650692.aspx[^]

Posted 20-Jul-12 22:15pm

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 · 2012-07-20T21:51:00

Solution 1

No. Without seeing your proc, we can't tell you why it's slow. We can't read minds. You don't say what sort of app it is, but, sometimes things are slow, so tell people to wait. But, first make sure it's as optimised as it can be. What is 'parameter stuffing' ?

Posted 20-Jul-12 21:51pm

Christian Graus

Comments

Sweetynewb 21-Jul-12 3:55am

Parameter stuffing is declare local variables and assign procedure parameters to them.
I am doing website and everything is working fine only below procedure takes long time-

select
sum(Case when patient_dev.dev_id= 1 Then patient_dev.dev_qty ELse 0 End) as [Zerostat VT],
Sum(Case when patient_dev.dev_id= 2 Then patient_dev.dev_qty ELse 0 End) as [Zerostat Spacer],
Sum(Case when patient_dev.dev_id= 3 Then patient_dev.dev_qty ELse 0 End) as Rotahaler,
Sum(Case when patient_dev.dev_id= 4 Then patient_dev.dev_qty ELse 0 End) as [Huf Puf Kit],
Sum(Case when patient_dev.dev_id= 5 Then patient_dev.dev_qty ELse 0 End) as [New Revolizer],
Sum(Case when patient_dev.dev_id= 6 Then patient_dev.dev_qty ELse 0 End) as [New Baby Mask],
Sum(Case when patient_dev.dev_id= 7 Then patient_dev.dev_qty ELse 0 End) as [Breathe O Meter]
from doc_business_data DB
inner join educator_call_dtls EC on DB.call_id=EC.call_id
inner join educator_master em on em.eid=EC.eid
left join patient_doc_business pati_doc on DB.doc_busi_id=pati_doc.doc_busi_id
left join patient_device_dtls patient_dev on patient_dev.pb_id=pati_doc.pb_id
inner join educator_state_master city on city.state_city_id= em.city_id
inner join educator_state_master region on region.state_city_id =city.parent_id
inner join educator_state_master st on st.state_city_id=region.parent_id
where (((year(EC.call_date) >2012) OR (year(EC.call_date)=2012 AND month(EC.call_date) >=3 ))
AND ((year(EC.call_date)=2012 AND month(EC.call_date)<=4) OR year(EC.call_date) < 2012))
AND st.state_city_id=20 AND em.deleted=0 and em.eid is NOT NULL

Christian Graus 21-Jul-12 4:00am

Looks messy, but I see no nested queries, excepting for the fact that your case statements need to be evaluated on every line. Would it be faster to do a sum for each value, in to variables, checjing the dev_id in a where clause, and then select them all at the end to get the same end result ? I am not sure. All the date checking makes no sense to me. Give me an example of a date that will not match these checks ? I think every date in the world will pass them, and they are wasting time.

Sweetynewb 21-Jul-12 4:41am

No. we are checking month and year so the data will come for month March-April 2012
Because of client requirement,we need to display some devices so we can't put dev_id in where condition. How to select all at the end? can u give any example so that i can try

Christian Graus 21-Jul-12 4:55am

Do selects like select @zerostart = sum(zerostat) to pull the seven values from the DB. Then do select @zerostat as zerostat, @zerostat_spacer as zerostat_spacer, etc. To do one select that grabs the values and returns them all in one row of one table.

Sweetynewb 21-Jul-12 5:21am

means you are saying , write 7 queries for 7 devices and then select all variables like @zerostat,@zerosts_spacer? Sir m trying like this let c

Christian Graus 21-Jul-12 5:26am

Correct. I don't see any other way, apart from getting rid of the date checks, that do nothing, to speed this up.

Sweetynewb 21-Jul-12 5:36am

No luck with 7 queries. but thank you for putting thought of dev_id in where condition. The number of devices are same for every user so i tried dev_id in where condition and it worked well.. Thank you very much.