Stored Procedure Slow On First Execution 1

[edsuk]

Hi

Appreciate any help with the following, fairly new to SQL Server:

I have a stored procedure for a report that runs a query against a view using parameters supplied by the user (in this instance, an example being the JobID). The view is fairly complex as it is a Job Costing report.

When this stored procedure runs the first time it takes about 30 seconds to return the relevant rows. When run subsequently the stored procedure takes less than 2 seconds to run.

The users want the less than 2 seconds performance in all instances and are getting frustrated when they run the report for the first time in a while and have to wait.

I must admit I thought once a stored procedure was run the execution plan remained in cache indefinitely, or am I missing the point here?

Once again, any help would be very much appreciated.

Thanks

Mark...

 

[SQLSister]

After an execution plan is generated, it stays in the procedure cache. SQL Server 2000 ages old, unused plans out of the cache only when space is needed.

Is this an indexed view? Perhaps you should look at the sp execution plan and identify where it might be possible to improve performance. POsting code here might help us see waht if anything can be done to improve performance.

"NOTHING is more important in a database than integrity." ESquared