Monday, March 12, 2012

Frequent SP recompiles in proc with many temp tables

I noticed a specific stored proc was getting recompiled quite often, so I decided to try to optimize it a bit.

In the proc, there are 9 and sometimes 10 temp tables created; the tenth table is created conditionally:

IF @.param IS NOT NULL
BEGIN
CREATE TABLE #tbl (col1 VARCHAR(21))
CREATE UNIQUE INDEX XIDX_tmp_tbl_pk ON #tbl (col1)
INSERT #tbl (col1) SELECT @.param
END

All the create table statements are at the top of the proc, except this conditional one, which follows the last unconditional table create statement.

Each table gets at least one Index. Some are Unique, some are compund, some are on a single column. All the Indexes are created as above, using CREATE INDEX statements. None are created as primary keys.

Here's the question:
What effect on the overall performance, and specifically the recompiles, would it have if I first created the temp tables, then created all the indexes, before the rest of the work done in the proc?

I'm hesitant to mess with this badboy without some idea that it will help the performance.

TIA.

Hrmmmm... too much information? 20 views and not one comment?

How about if I get more generic: What are some "best practices" to prevent SP recompiles?

No comments:

Post a Comment