sql server stored proc question
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
Software/Hardware used:
Software/Hardware used:
ASKED:
November 6, 2008 5:05 PM
UPDATED:
November 6, 2008 6:09 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
