Regarding Query Optimisation

Question

Regarding Query Optimisation

mithun.gite

SSCrazy

Points: 2333
More actions
April 21, 2009 at 5:23 am

#384403

Dear Friends,
Can any one give me a link for Query Optimization, using that i can start optimizing my queries?
And One more important question , Is there any tunning tricks available with joins?
like any key word we can use with joins or , we should use primary key fields in joins for improved performance, anything like this ?
I thank you all in adavance....
Mithun

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

mithun.gite (4/21/2009)
Can any one give me a link for Query Optimization, using that i can start optimizing my queries?

I can suggest some books.

Inside SQL Server 2005: Query Tuning and Optimisation

SQL Server 2008 Query Performance Tuning Distilled

And One more important question , Is there any tunning tricks available with joins?

Good indexes.

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

Ronald H SSCarpal Tunnel Points: 4023 More actions · Answer 2

You can write a whole library about this.

Yes, use PK's in your joins, so you can use their indexes. This is pretty much a general rule that applies for all queries. Avoid using (NOT) IN (SELECT...), use joins or EXISTS instead. Avoid triangular joins (like ON a.ID<b.ID). In general, avoid UDF's, since they're often used in the wrong way. Avoid correlated subqueries in your SELECT statement.

These are the guidelines I follow at least. But there are many more.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

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

r.hensbergen (4/21/2009)
Avoid using (NOT) IN (SELECT...), use joins or EXISTS instead.

Not necessarily. As with many things, the best advice here is test and see which, if either, is faster.

Avoid correlated subqueries in your SELECT statement.

The vast majority of times, SQL executes correlated subqueries with the same plan as if the tables were joined. Again, test both and seer

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

mithun.gite SSCrazy Points: 2333 More actions · Answer 4

GilaMonster (4/21/2009)
mithun.gite (4/21/2009)
Can any one give me a link for Query Optimization, using that i can start optimizing my queries?
I can suggest some books.
Inside SQL Server 2005: Query Tuning and Optimisation
SQL Server 2008 Query Performance Tuning Distilled
And One more important question , Is there any tunning tricks available with joins?
Good indexes.

Can i get the link to download this book?

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

mithun.gite (4/21/2009)
GilaMonster (4/21/2009)
mithun.gite (4/21/2009)
Can any one give me a link for Query Optimization, using that i can start optimizing my queries?
I can suggest some books.
Inside SQL Server 2005: Query Tuning and Optimisation
SQL Server 2008 Query Performance Tuning Distilled
And One more important question , Is there any tunning tricks available with joins?
Good indexes.
Can i get the link to download this book?

Inside SQL Server 2005: Query Tuning and Optimisation

SQL Server 2008 Query Performance Tuning Distilled

Neither is expensive considering the value of the information.

If you want to get a copy without paying, you could try dropping the author a note and tell him that you want a copy but don't want to pay. Grant (author of the second) posts here regularly.

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 6

GilaMonster (4/21/2009)
mithun.gite (4/21/2009)
GilaMonster (4/21/2009)
mithun.gite (4/21/2009)
Can any one give me a link for Query Optimization, using that i can start optimizing my queries?
I can suggest some books.
Inside SQL Server 2005: Query Tuning and Optimisation
SQL Server 2008 Query Performance Tuning Distilled
And One more important question , Is there any tunning tricks available with joins?
Good indexes.
Can i get the link to download this book?
Inside SQL Server 2005: Query Tuning and Optimisation
SQL Server 2008 Query Performance Tuning Distilled
Neither is expensive considering the value of the information.
If you want to get a copy without paying, you could try dropping the author a note and tell him that you want a copy but don't want to pay. Grant (author of the second) posts here regularly.

And he doesn't have electronic copies available... I need to talk to the publisher to see if I can get one. All I have are a stack of Word files. But no, I'd have to say no to requests for a free electronic copy, if I had one. Sorry.

However, contact Apress and tell them you'd like to write a review. They send copies to user groups for that purpose, you might be able to get one too.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning