Kevin Kline, president of the
PASS organization, came to Atlanta last night to present some Stored Procedure best practices. The slides will be available on the
Atlanta MDF site soon, so I'm not going to try to repeat all of his material. I will point out a few highlights:
- before the presentation began we had a tough "Stump the Chumps" question from the crowd. This individual was looking for guidance in a very complex process that needed to do left outer joins with coalesce statements from a SQL Server set of data INTO an Oracle database. Kevin's recommendation - bypass the paradigm boundries and copy all of the raw data from SQL Server into Oracle THEN do the coalesce and joins into the final table. This avoids all the cryptic problems when working with different environments
- never prefix a stored procedure with "sp_" - it causes a lookup in the master database which wastes cycles and potentially executes the wrong version of a stored proc
- use "SP_EXECUTESQL" instead of "EXEC" to run embedded SQL. You'll get better performance and avoid problems precompiling large statements with inline variables
- use local temp tables instead of TempDb tables. Better yet, use table variables. Significant improvements in perf are achieved
- Refer to http://sql-server-performance.com. Bookmark it. Love it.
- Get the book Inside SQL Server 2000 by Kalen Delaney. This is an essential "must-have" for anyone who works with SQL Server.