Optomizing code guidlines

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  August 15, 2007 at 12:51 pm

  #727154

  Still don't need a cursor for that... but we get your point .

• Jeff Moden

  SSC Guru

  Points: 1004362

  More actions

  August 15, 2007 at 8:27 pm

  #727231

  And, breaking a set of millions of rows up into smaller sets still isn't RBAR...

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Mohit Nayyar

  Ten Centuries

  Points: 1008

  More actions

  August 16, 2007 at 11:34 pm

  #727541

  See, if we have cursor in SQL Server then they must be of some use.

  So it depends on the business / technical requirement and our capability of using a cursor. Using READ ONLY, FAST FORWARD cursors are not that bad.

  And that’s the way to achieve any row-by-row operation. But still the usage should be minimal.

  And you can find various best practices / optimization guidelines on the net but the main objective should be focused on these points.....

  1.    Reduce disk I/O

  2.    Reduce CPU / Memory utilization

  3.    Reduce network traffic

   

  So we can at least start with basic stuff like this....

  1. Large Result Set - Make sure you only ask for what your program really needs (Don't use * always use column names instead)
  2. Missing Where Clause - To restrict the number of rows based on business requirement
  3. Lack of useful statistics - Make sure create stats and update stats option are TRUE for the database
  4. Out-of-date statistics
  5. Lack of useful indexes - Make sure you have useful clustered / non-clustered indexes and don't forget they come with performance penalty
  6. Extensive Trigger usage, see if you can do this with Constraints
  7. Use Views or Stored procedures instead of Dynamic SQL
  8. Use SET NOCOUNT ON in procedures
  9. Data stripping is the key to Disk I/O

  I will be posting one extensive list soon on my blog at http://mohitnayyar.blogspot.com/

   

   

  Thanks
  Mohit Nayyar
  http://mohitnayyar.blogspot.com/
  "If I am destined to fail, then I do have a purpose in my life, To fail my destiny"