How to optimize following query for execution time

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 2005 Forums

Transact-SQL (2005)

How to optimize following query for execution time

Author

Topic

Swati Jain
Posting Yak Master

139 Posts

Posted - 2008-03-18 : 10:30:23

Hell All,
Following query takes 7 minutes to execute while using search criteria as shown below in blue text(ie. IN(2006,2007)
if criteria changes to =2006 as shown in 2),this takes 2minutes

But I want expected output as in query 1) in less time.
How to optimize following query for execution time?

1)select sum(PB.CONSN_QTY)Consumption,Count(*),PB.BillPro_Year
from tbtrans_prowaterbill PB
INNER JOIN MIDC_AREA MA
ON PB.Area_cd = MA.Area_cd INNER JOIN MIDC_Division MD ON MA.Div_CD = MD.Division_CD
INNER JOIN MIDC_Circle MC ON MD.Circle_CD = MC.Circle_CD
INNER JOIN TBMST_SubDiv TS ON MA.SubDiv_CD = TS.SubDiv_CD
INNER JOIN MIDC_Zone MZ ON MD.Zone_CD = MZ.Zone_CD
INNER JOIN tbmst_consumer TC ON PB.cons_no = TC.Cons_No
INNER JOIN TBMST_CONSTYPE TCT ON TCT.Cons_Type = TC.Cons_Type
where pb.billpro_year IN('2006','2007') and MTR_Size = 15 and TCT.Cons_Type = '1A2'
and MZ.Zone_Name = 'MUMBAI' and MC.Circle_NAME = 'MMR' and MD.Division_Name = 'Dombivli' and TS.SubDiv_DESC = 'THANE DIVISION STAFF'
group by PB.BillPro_Year

2)select sum(PB.CONSN_QTY)Consumption,Count(*),PB.BillPro_Year
from tbtrans_prowaterbill PB
INNER JOIN MIDC_AREA MA
ON PB.Area_cd = MA.Area_cd INNER JOIN MIDC_Division MD ON MA.Div_CD = MD.Division_CD
INNER JOIN MIDC_Circle MC ON MD.Circle_CD = MC.Circle_CD
INNER JOIN TBMST_SubDiv TS ON MA.SubDiv_CD = TS.SubDiv_CD
INNER JOIN MIDC_Zone MZ ON MD.Zone_CD = MZ.Zone_CD
INNER JOIN tbmst_consumer TC ON PB.cons_no = TC.Cons_No
INNER JOIN TBMST_CONSTYPE TCT ON TCT.Cons_Type = TC.Cons_Type
where pb.billpro_year = '2006' and MTR_Size = 15 and TCT.Cons_Type = '1A2'
and MZ.Zone_Name = 'MUMBAI' and MC.Circle_NAME = 'MMR' and MD.Division_Name = 'Dombivli' and TS.SubDiv_DESC = 'THANE DIVISION STAFF'
group by PB.BillPro_Year

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-03-18 : 10:50:05

Hi,

remove COUNT(*) and replace that with COUNT(<pk column in PB>).
try creating index on cons_no including billpro_year

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-18 : 13:17:47

Or at least count the same column you are summing up.
One caveat though is that counting NULLs gives 0.

E 12°55'05.25"
N 56°04'39.16"

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-18 : 21:11:38

quote:
Originally posted by PeterNeo

Hi,

remove COUNT(*) and replace that with COUNT(<pk column in PB>).
try creating index on cons_no including billpro_year

This will not make any difference.

quote:
Originally posted by Peso

Hi,
Or at least count the same column you are summing up.
One caveat though is that counting NULLs gives 0.

This might give a different result though! As you know, count(*) gives you all rows, count(row) gives you non-null rows.

Anyway - some ideas for you

How much time does it take for just 2007? Presumably you think it takes < 5 minutes. If so, you could try doing a sum on a union of 2006 & 2007.

I don't know how much data you have but that is a long time - I'd expect several tens of million rows to produce this. You should make sure all your FKs are indexed.

Try replacing the last predicate:

MZ.Zone_Name = 'MUMBAI' and MC.Circle_NAME = 'MMR' and MD.Division_Name = 'Dombivli' and TS.SubDiv_DESC = 'THANE DIVISION STAFF'

with then encoded version of the names (MZ.Zone_CD = 'MB' or whatever instead of MZ.Zone_Name = 'MUMBAI' ). That will allow use of indexes on the predicate. As you have it now, it can use the index to do the joins but I think it has to full table scan to filter the results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources