Need help on optimize the query

Question

Need help on optimize the query

moosamca

Right there with Babe

Points: 773
More actions
February 18, 2010 at 8:21 am

#156685

Please help me write the below query in basic optimized way. Thanks in advance
Query:
====
select b.CallLetters,f.ContractKey,c.DatecontractLineEntered,d.HeadlineNumber,count(a.sk_SalesFact),a.GrossRate
from salesfact a with(nolock), station b with(nolock),DateContractLineEntered c with(nolock),HeadlineNumber d with(nolock),ContractKey f with(nolock)
where a.sk_station = b.sk_station
and a.sk_datecontractLineEntered = c.sk_datecontractLineEntered
and a.sk_HeadlineNumber = d.sk_HeadlineNumber
and a.sk_ContractKey = f.sk_ContractKey
and c.DatecontractLineEntered >= '2007-10-02'
and c.DatecontractLineEntered <= '2007-10-03'
and a.sk_station in (29)
group by b.CallLetters,f.ContractKey,c.DatecontractLineEntered,d.HeadlineNumber,a.GrossRate

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

c.lambe SSC Journeyman Points: 85 More actions · Answer 1

Optimise it yourself.

Try capitalising all of the SQL Keywords, using proper join syntax, using better aliases and some indentation whileyou are at it.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

c.lambe (2/18/2010)
Optimise it yourself.

That comes across a little rude, I don't know if you intended it that way.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Also, please be careful of Nolock. It's often used in an attempt to make queries faster by ignoring locks. However it can cause inconsistent results. See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

Rather leave the locking at default and optimise the queries so that any blocking is minimised.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

c.lambe SSC Journeyman Points: 85 More actions · Answer 4

Ooops, I meant:

Optimise it yourself:

and not

Optimise it yourself.

or

Optimise it yourself!

One dot makes a big difference sometimes.

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 5

moosamca (2/18/2010)
Please help me write the below query in basic optimized way. Thanks in advance
Query:
====
select b.CallLetters,f.ContractKey,c.DatecontractLineEntered,d.HeadlineNumber,count(a.sk_SalesFact),a.GrossRate
from salesfact a with(nolock), station b with(nolock),DateContractLineEntered c with(nolock),HeadlineNumber d with(nolock),ContractKey f with(nolock)
where a.sk_station = b.sk_station
and a.sk_datecontractLineEntered = c.sk_datecontractLineEntered
and a.sk_HeadlineNumber = d.sk_HeadlineNumber
and a.sk_ContractKey = f.sk_ContractKey
and c.DatecontractLineEntered >= '2007-10-02'
and c.DatecontractLineEntered <= '2007-10-03'
and a.sk_station in (29)
group by b.CallLetters,f.ContractKey,c.DatecontractLineEntered,d.HeadlineNumber,a.GrossRate

This looks like homework to me.

However, you will see a substantial performance bump if you drop the with(nolocks) (per Gail) and use INNER JOIN with your 5 tables, moving most of your WHERE items to ON statements. I don't know if a BETWEEN conjuction will be faster than your greater than/less than criteria, but it might be worth a shot. And, don't use IN()

BOL should give you all you need

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Dan Guzman - Not the MVP (2/18/2010)[hr
However, you will see a substantial performance bump if you drop the with(nolocks) (per Gail) and use INNER JOIN with your 5 tables, moving most of your WHERE items to ON statements.

Unlikely. The exec plans for joins in the from and joins in the where will be identical in the majority of cases.

I don't know if a BETWEEN conjuction will be faster than your greater than/less than criteria, but it might be worth a shot.

During query parsing a BETWEEN is expanded into a >= and <= combination. The two will optimise to exactly the same plan.

I can't see anything in the query that will change performance. Moving the joins to the from will make it easier to read, but it won't change the performance. I suspect any optimisation will have to come from indexes in this case.

p.s. Dan, your username makes me laugh every time I see it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

moosamca Right there with Babe Points: 773 More actions · Answer 7

Hi Gila,

Thanks for your reply, I have attached the execution plan, Table and index script for your reference. For your informaiton i have used nolock because it is an dataware house environment. Please help me to revise the script.

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 8

p.s. Dan, your username makes me laugh every time I see it.

What can I say, It has avoided some confusion in the past. Mostly from 'Hi, been a long time, how have you been' comments from complete strangers 😀

Thanks for the correction on the JOINs vs WHEREs, I was sure that non explicit JOINs would cause CROSS joins, which would be bad.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

See if the following indexes help

DROP INDEX [idx_SalesFact_Station] ON [dbo].[SalesFact]

GO

CREATE NONCLUSTERED INDEX [idx_SalesFact_Station] ON [dbo].[SalesFact]

([sk_Station])

INCLUDE ([sk_ContractKey],[sk_DateContractLineEntered],[sk_HeadlineNumber],[GrossRate])

GO

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

moosamca Right there with Babe Points: 773 More actions · Answer 10

FYI, i have created a non clustered index on cotractkey table skip the table scan.

Dan i will change the query to BETWEEN clause instead of >= & <= that will give easy readable.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Dan Guzman - Not the MVP (2/18/2010)

What can I say, It has avoided some confusion in the past. Mostly from 'Hi, been a long time, how have you been' comments from complete strangers 😀

LOL. No urge to leech off the other Dan's reputation?

Thanks for the correction on the JOINs vs WHEREs, I was sure that non explicit JOINs would cause CROSS joins, which would be bad.

Only if the join condition isn't specified at all. The optimiser's smart enough to find the join conditions in the where clause. It's if they're missing (which is easier to do using this style of join) that you'll get a cross join.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

moosamca Right there with Babe Points: 773 More actions · Answer 12

Gila,

Wow Awesome, THe script ran for 0 ms from 16 sec. Thank you so much. The index really helped.

Regards

Moosa

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Pleasure.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

moosamca Right there with Babe Points: 773 More actions · Answer 14

Hi Gila,

Thanks for your reply, I have attached the execution plan, Table and index script for your reference. For your informaiton i have used nolock because it is an dataware house environment. Please help me to revise the script.