'Random' query performance woes

Hi,

We have a strange performance issue which we have reproduced in the production environment as well as our local development environment. It's a pretty strange on one, so please bear with me.

How is it possible for a stored procedure to run fast (<1 second) on one day but perform really slowly the next day (> 20 seconds with no data/structural changes)? Then, the strangest part - we made this run fast again by just swapping the sequence of table1 and the view1 in the FROM clause :confused:

Below is an example of the part of the stored procedure which we identified to be running the slowest in this case. We broke it up in Query Analyser and added timing points - i.e 'select 1, getdate()' between each statement:


--Slow today (but was fine yesterday)
SELECT view1.col1, view1.col2
INTO #temp2
FROM table1
INNER JOIN view1
ON view1.col1 = table1.col1
INNER JOIN table2
ON table1.col2 = table2.col2
INNER JOIN #temp as T
ON table2.col3 = T.col3

--Swapped view/table in from clause - same logic
-- but fast again - no data/object structual changes?!
SELECT view1.col1, view1.col2
INTO #temp2
FROM view1
INNER JOIN table1
ON view1.col1 = table1.col1
INNER JOIN table2
ON table1.col2 = table2.col2
INNER JOIN #temp as T
ON table2.col3 = T.col3

This query is in fact part of a stored procedure which was always slow until last Friday (took 20-30s) but we finally got round to rewriting. After that, it was performing well in under 1 second even with a large range of parameters.

However, today it was really slow, but nothing else has changed since then (no data/structural changes) and there weren't any other connections/queries/sql jobs/transactions at this time.

Apart from this problem, we have found that we get periods where a stored procedue will start to perform particularly slowly over the course of a day (every few weeks). When the users start complaining, we have found that if we DROP the stored procedure and CREATE it again, it seems to fix this problem. In this case, doing the same to this example and all child objects (views, etc) did not solve it.

We will be moving the product to SQL Server 2005 and rewriting the database so hopefully this will resolve things in the long term, but for now we're getting a shed-load of flack from our clients over timeout issues which we cannot pinpoint to resolve.

We do have an extremely complicated database and although I don't know how the optimizer works, I feel that it cannot handle many hundred of complex stored procedures (some containing huge inner joins to dozens of tables and 'trees of views') which contain most of the business logic of the product!

Any thoughts or advice will be appreciated.

Many thanks,

Lee