SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OPtimization Skills


OPtimization Skills

Author
Message
rohit11.jagannath
rohit11.jagannath
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 4
Hello All....

I am a fresher in an MNC and have been assigned the task of optimization. I am working on Sql Server 2000 for database support. So, can anybody help me out in getting the list of things which i can look into while optimizing the code in SQL?? It will be better if i get something on optimizing at logical level.

Here is a few things which i look into....

1) Removal of Cursors
2) Removal of unused variables
3) Prefixing qualified name "dbo." while calling Stored Procedures
4) Replacement of multiple 'Set' statements with a single select statement.
5) Removing unnecessary BEGIN or END
6) Taking care of numeric precisions while declaring variables
7) Replacing 'Else If' conditional blocks with 'Case' statement blocks

So, other than these things can i get something better on optimizing from logical point of view??? :-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228659 Visits: 46342
rohit11.jagannath (4/24/2013)
2) Removal of unused variables


Code cleanup, not much to do with optimisation

4) Replacement of multiple 'Set' statements with a single select statement.

That's more a style decision than anything else in most cases.

5) Removing unnecessary BEGIN or END

Code cleanup, not much to do with optimisation

7) Replacing 'Else If' conditional blocks with 'Case' statement blocks


SQL doesn't have a CASE that can replace if/else blocks. SQL's case is a part of a select/insert/update/delete statement

Take a read through this, yes, it's SQL 2008, most of the principals apply.
http://www.amazon.com/Server-Performance-Tuning-Distilled-Experts/dp/1430219025

Optimisation is about making queries run faster, through code changes or 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


rohit11.jagannath
rohit11.jagannath
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 4
Thank You....:-) Since i am still in the beginning stages of developing queries in SQL or rather optimizing them, how exactly should i cultivate the skills of SQL???
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228659 Visits: 46342
Practice, practice, practice and a hell of a lot of reading.

Start with you basic skills in writing queries. If you don't know how to write queries well, you won't be able to tune them effectively.

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search