Why would a Stored Procedure run slower than naked T-SQL?

Question

I have a stored procedure in a MS-SQL 2005 database that:

• Creates two temp tables
• Executes a query with 7 joins but is not otherwise terribly complex
• Inserts the results into one of the temp tables
• Executes two more queries (no joins to "real" tables) that puts records from one of the temp tables into the other.
• Returns a result set from the second temp table
• Drops both temp tables

The SP takes two parameters, which are then used in the first query.

When I run the SP for a given set of parameters, it takes 3 minutes to execute.

When I execute the contents of the SP as a regular T-SQL batch (declaring and setting the parameters beforehand), it takes 10 seconds. These numbers are consistent across multiple sequential runs.

This is a huge difference and there's no obvious functional changes. What could be causing this?

UPDATE

Reindexing my tables (DBCC REINDEX) sped up the SP version dramatically. The SP version now takes 1 second, while the raw SQL takes 6.

That's great as a solution to the immediate problem, but I'd still like to know the "why".

Answer 1

Does your SP use dynamic T-SQL at all? If so, you' lose the benefits of cached execution plans...

Failing that, are the connections used to run the SP vs T-SQL configured in the same way? Is the speed differential consistent or is the SP as slow the fist time it's run after moification?

Answer 2

It might have been exactly due to the fact that in SP the execution plan was cached and it was not optimal for the data set. When data set depends greatly on the parameters or changes considerably between invocations it's better to specify 'with recompile' in 'create proc'. You lose a fraction of a second on recompilation, but may win minutes on execution.

PS Why cannot I comment? Only "Your Answer" is available.