Execution Plan for Prepared Statement

[TealWren]

I have a fairly complex query with one integer type parameter. When I run it as a prepared statement, it takes ~50 seconds to run. When I run the same statement in query analyzer, it takes less than 1 second.

My theory is that the execution plan is somehow different when it's running as a prepared statement. Is there any way to determine the execution plan used for a prepared statement?

Thanks for your help!
Jenica Humphreys


TealWren

 

[NoCoolHandle]

Memory says there is a dbcc statement that alows you to return all queryplan data to the client. (check books on line for DBCC - or get a copy of "inside sqlserver" by Kalen Delaney (ms press - it should have it in there)

However that is from sql 6.5 days. So it is probably undocumented. (seen note above)

You could check syscacheobjects in the master database and see how many plans you have... Also if this is a multi proc system, parallel query plans can take longer .

And last..

Have you thought of building a storedproc in the database and avoiding the trap of prepairing a statement (like a proc) every time you run your app?

Rob