March 19, 2010 at 11:16 am
I use a Stored Procedure (with parameter) which uses 3/4 joins to retrieve some fields.i.e., a select from 3/4 tables. The problem is whenever the SP is executed for a particular input for the first time, it takes 5 to 6 mins to retrieve records(1 to 100). But the same SP for the same parameter after that completes in a shorter duration. If the parameter is changed, then it takes similarly long time, but the second execution for the same parameter in shorter duration.
Execution plan does not have any table scan.
There are indexes present in all the tables.
recompiling SP, Freeing cache does not have desired effect.
SQL 2005
Any recommendations?
March 19, 2010 at 12:01 pm
balasach82 (3/19/2010)
I use a Stored Procedure (with parameter) which uses 3/4 joins to retrieve some fields.i.e., a select from 3/4 tables. The problem is whenever the SP is executed for a particular input for the first time, it takes 5 to 6 mins to retrieve records(1 to 100). But the same SP for the same parameter after that completes in a shorter duration. If the parameter is changed, then it takes similarly long time, but the second execution for the same parameter in shorter duration.Execution plan does not have any table scan.
There are indexes present in all the tables.
recompiling SP, Freeing cache does not have desired effect.
SQL 2005
Any recommendations?
Freeing cache will make it slower on the next run as well.
Do you have appropriate indexes?
Have you checked for parameter sniffing?
how do the statistics appear for your tables?
How is the index fragmentation level?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply