SQL query slow - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL query slow

Author

Topic

hanifikri
Starting Member

14 Posts

Posted - 2007-01-16 : 22:11:03

Hi,

My problem is my query took more than 1 hr to complete it. Normally it's less than 1 minute.

For your information, I don't change anything to my query.

Please help me.

thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-16 : 23:07:10

I'm not sure how we can help with the very, very, very limited information that you provided.

Tara Kizer

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-17 : 02:57:46

Make sure your query is not getting blocked due to locks placed on the same table by some other user. Use sp_who2 to find out blocking issue.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-17 : 03:06:47

Are you running a defragmentation on your disk(s)?
Is the network on the same speed?
Are you running multiple applications simultaneously?

Peter Larsson
Helsingborg, Sweden

hanifikri
Starting Member

14 Posts

Posted - 2007-01-19 : 02:48:38

for information, my query involve more than 10 tables.

Below is my query:

select shd.payoutno,
shd.month,
a.compnycode,
a.staffno,
grp.name,
a.cojoindt,
grp.yrsvdt,
grp.grpjoindt,
grp.datebirth,
a.paygrpcode,
dep.departname,
brh.brhlocname,
job.jobpost,
grd.gradename,
grdc.grdcatname,
jmov.emptypcodeto,
smov.basicamt,
shd.basicpaid,
shd.alamt allallw,
isnull(sum(sal.amountenter),0.00) fixedallw,
case
when sum(sal.amountenter)=0 or sum(sal.amountenter)=shd.alamt then
0
when sum(sal.amountenter) < shd.alamt then
shd.alamt - sum(sal.amountenter)
else
shd.alamt
end varallw,
shd.coepf,
shd.coepfve + shd.coepfvep + shd.coepfves coepfve,
shd.cosocso,
shd.stfpcbtax,
a.pcbcatcode,
a.glseg2code,
cc1.calccode1,
cc1d.calcdesc,
a.vecode
from hrsc_employee a
JOIN hrsc_grpperson grp ON
grp.personid = a.personid
JOIN hrsc_salhd shd ON
shd.compnycode = a.compnycode
and shd.staffno = a.staffno
and shd.payoutno in (15,16)
and shd.month = 1
and shd.year = 2007
LEFT OUTER JOIN hrsc_salcur2 sal ON
sal.compnycode = a.compnycode
and sal.staffno = a.staffno
and sal.payoutno = shd.payoutno
and sal.year = shd.year
and sal.month = shd.month
and sal.salarytype = 'A'
and sal.allwcode in
(select distinct(ad.adocode) from hrsc_adod ad, hrsc_salcur2 sl
where ad.adocode = sl.allwcode
and ad.admode = 'F'
and ad.adotype = 'A')
JOIN hrsc_jobmov jmov ON
jmov.compnycode = a.compnycode
and jmov.staffno = a.staffno
and shd.todt between jmov.fromdt and jmov.todt
JOIN hrsc_salmov smov ON
smov.compnycode = a.compnycode
and smov.staffno = a.staffno
and shd.todt between smov.fromdt and smov.todt
JOIN hrsc_locmov lmov ON
lmov.compnycode = a.compnycode
and lmov.staffno = a.staffno
and shd.todt between lmov.fromdt and lmov.todt
JOIN hrsc_depart dep ON
dep.departcode = lmov.deptcodeto
JOIN hrsc_brhloc brh ON
brh.brhloccode = lmov.brhloccodeto
JOIN hrsc_grade grd ON
grd.gradecode = jmov.gradecodeto
JOIN hrsc_grdcat grdc ON
grdc.grdcatcode = grd.grdcatcode
JOIN hrsc_job job ON
job.jobcode = jmov.jobcodeto
JOIN hrsc_calcstfs cc1 ON
cc1.compnycode = a.compnycode
and cc1.staffno = a.staffno
JOIN hrsc_costctr cc1d ON
cc1d.calccode = cc1.calccode1
group by a.compnycode,
a.userfld01,
a.staffno,
grp.name,
a.cojoindt,
grp.yrsvdt,
grp.grpjoindt,
a.paygrpcode,
grp.datebirth,
dep.departname,
brh.brhlocname,
job.jobpost,
jmov.emptypcodeto,
smov.basicamt,
shd.alamt,
shd.payoutno,
shd.month,
shd.coepf,
shd.coepfve + shd.coepfvep + shd.coepfves,
shd.cosocso,
shd.stfpcbtax,
a.pcbcatcode,
shd.basicpaid,
grd.gradename,
grdc.grdcatname,
a.glseg2code,
cc1.calccode1,
cc1d.calcdesc,
a.vecode
order by shd.payoutno,a.staffno

Please advise.

Thanks.

lvrboy
Starting Member

1 Post

Posted - 2007-01-23 : 07:47:34

may be this is due to some uncommitted transactions before..
which involves any one of the tables ..

hanifikri
Starting Member

14 Posts

Posted - 2007-01-23 : 21:41:30

lvrboy,

how do i know that there is uncommitted transactions? is there any command to check?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-23 : 21:42:32

Have you run DBCC DBREINDEX or DBCC INDEXDEFRAG recently? How about UPDATE STATISTICS? What indexes do you have on each of the tables involved in your query?

Tara Kizer

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-01-24 : 03:02:20

how do i know that there is uncommitted transactions? is there any command to check?


Select @@TranCount

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources