writing OLAP queries - best practices

this is a list of some query tuning best practices i’ve compiled, with the general the goal being to avoid the problems of inefficient report queries that take a ton of time to execute.

• begin each sproc with "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED". this allows you to avoid NOLOCK hints within the query and ensures you don't accidentally forget one
• write the query one join at a time
• turn on ‘Show Execution Plan’ and study the plan with each join you add
  o look for the item taking the greatest % of execution time
  o check for thick lines (lots of data)
  o check for table scans/bookmark lookups
  
  
  
• turn on 'Set Statistics Time' and 'Set statistics IO' from the Tools...Options...Connection Properties menu
  o look for extremely high logical reads (in general, < 10,000 logical reads per table is ok)
  o total execution time for a query of average complexity on a server under average load should not exceed 60 seconds for one day's worth of data
  
  
  
• MAKE USE OF INDEXES! if you expected an index to be used that wasn't, figure out why
• it could be that you are missing columns in your join; for example, an index on Table1 on presenceid,userid,starttime will not be used for "select starttime from Table1 where presenceid = sp.presenceid", but will be used for "select starttime from Table1 where presenceid = sp.presenceid and userid = sl.userid"
• if there is no way to make use of an existing index, request that a new one be added or add columns to an existing one
• use a "covering index" to avoid bookmark lookups
  o a covering index is an index that is both for the seek (where/join clause) and the selected data (select clause)
  o include the columns in your where/join clause first, then the columns you are selecting. for example, with an index on Table1 on presenceid,userid,starttime, no bookmark lookup will be needed when you "select starttime from Table1 where presenceid = sp.presenceid and userid = sl.userid", since presenceid and userid are in the where/join clause and starttime is in the select clause
  o in general, make the covering index as wide as you need to avoid bookmark lookups (the exception to this is extremely wide varchar columns that will inflate the size of your index too much)
  
  
  
• if you still can't get the query execution time down, figure out another way to get the same data
• use different tables/joins
• use "EXISTS" instead of join
• use subquery instead of join
• use "=" not "<>", and "NOT EXISTS" instead of "NOT IN", whenever possible
• follow examples in existing sprocs that perform well

lots of other good tips here: http://www.sql-server-performance.com/transact_sql.asp

SCOPE_IDENTITY(), @@IDENTITY, and IDENT_CURRENT('tablename')

this came up today, want to make sure everyone understands how these work:

• SCOPE_IDENTITY() returns the value of the Identity column for the last insert, for the current session (i.e. sproc calling SCOPE_IDENTITY()) AND current scope (i.e. trigger inserts caused by your insert are not considered)
• @@IDENTITY returns the value of the Identity column for the last insert, for the current session but REGARDLESS of scope (i.e. trigger inserts caused by your insert are considered)
• IDENT_CURRENT('tablename') returns the value of the Identity for the last insert, REGARDLESS of session or scope

so in general, you probably want to be using SCOPE_IDENTITY() to get the last inserted value for an Identity column. if there are no triggers on the table being inserted to, @@IDENTITY and SCOPE_IDENTITY() are identical, but if a trigger is later added your sproc will need to be changed. you almost never want to use IDENT_CURRENT(‘tablename’), since the current value can easily change between the time you make your call to this function and you do something with the result--

try/catch with open transaction

something i’m noticing we haven’t been so good about: when you write a try/catch block with an open transaction, make sure that the last line of code in the try block is the CommitTransaction(). if there is code after the Commit() that throws an error, the catch handler will try to rollback a transaction that has already been committed, and that will throw its own unhandled exception.

void dbcall()
{

try
{

ds.OpenConnection();

ds.BeginTransaction();

//db calls here

//make this the
last line of the try block:

ds.CommitTransaction();

}

catch (Exception x)
{

ds.RollbackTransaction();

handleError(x);

return;

}

finally

{

ds.CloseConnection();

}

//put any other code that
happens after you Commit here

}

click to run an ActiveX control

here’s the lowdown from Microsoft on the ‘click to run an ActiveX control’ dialog (affects plugins like flash, shockwave, and quicktime) and a fix for it (you need to create the object with javascript and not use an object tag):

http://msdn.microsoft.com/library/default.asp?url=/workshop/author/dhtml/overview/activating_activex.asp

more info:

http://www.cssbeauty.com/skillshare/comments.php?DiscussionID=482