Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OPtimization Skills Expand / Collapse
Author
Message
Posted Wednesday, April 24, 2013 5:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 11:19 PM
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???
Post #1445869
Posted Wednesday, April 24, 2013 5:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 43,010, Visits: 36,169
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 2008, MVP
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

Post #1445876
Posted Wednesday, April 24, 2013 7:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 11:19 PM
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???
Post #1445968
Posted Wednesday, April 24, 2013 8:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 43,010, Visits: 36,169
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 2008, MVP
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

Post #1446040
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse