sql server stored proc question

5 pts.
Tags:
SQL Server 2005
SQL Server stored procedures
Hi the code is like this. Its taking too long to excute. if exists(select jbadsd from saldayf sa where substring(convert(char(7),sa.jbadsd),4,4) = substring(convert(char(7),@jbadsd),4,4) and substring(convert(char(7),sa.jbadsd),2,2) = '07') --print'exists' update [national].dbo.tempsaldayfna set castsdly = sa.sum_castsdly from [national].dbo.tempsaldayfna sna inner join (select cusnsd,dseqsd,slmnsd,pnumsd,slm4sd,slm5sd,jbadsd, sum( castsd) as 'sum_CASTSDlY' from saldayf group by cusnsd,dseqsd,slmnsd,pnumsd,slm4sd,slm5sd,jbadsd) as sa on sa.cusnsd = sna.cusnsd and sa.dseqsd = sna.dseqsd where sna.jbadsd = @jbadsd and sna.pnumsd = sa.pnumsd and sna.slmnsd = sa.slmnsd and sna.slm4sd = sna.slm4sd and sna.slm5sd = sa.slm5sd update [national].dbo.tempsaldayfna set lvalsdly = sa.sum_lvalsdly from [national].dbo.tempsaldayfna sna inner join (select cusnsd,dseqsd,slmnsd,pnumsd,slm4sd,slm5sd,jbadsd, sum( lvalsd) as 'sum_lvalSDlY' from saldayf group by cusnsd,dseqsd,slmnsd,pnumsd,slm4sd,slm5sd,jbadsd) as sa on sa.cusnsd = sna.cusnsd and sa.dseqsd = sna.dseqsd where sna.jbadsd = @jbadsd and sna.pnumsd = sa.pnumsd and sna.slmnsd = sa.slmnsd and sna.slm4sd = sna.slm4sd and sna.slm5sd = sa.slm5sd Please help.. Thanks in advance

Answer Wiki

Thanks. We'll let you know when a new response is added.

Since there are 3 SQL statements involved, the first thing I would do is try to find out which one of the 3 statements is taking longer to execute.

Are there any indexes available ? Have you seen the execution plan of these statements to see if those indexes are in fact being used ?

At first sight, it seems that the 2 updates are pretty similar. Is it not possible to update the 2 fields in just one update ?

——————-

This part:
substring(convert(char(7),sa.jbadsd),4,4) = substring(convert(char(7),@jbadsd),4,4)
and substring(convert(char(7),sa.jbadsd),2,2) = ’07’)

Is never going to run fast. This section is forcing either an index scan or a table scan no matter what.

Can you save the XML for the execution plan and post it so we can see what it actually happening.

I would assume that you are missing indexes or that the indexes do not have the columns in the correct order.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following