Query plan re-use on views?

Here's the setup:

Client database has a complex view with eight nested subqueries used to return "dashboard" information. The application code uses NHibernate to call and filter the view with three parameters, one of which is the CustomerID.

A certain customer, (the biggest client), has more than ten times the number of records of the next largest customer.

Occasionally, the database reaches a state where when this particular customer tries to run the dashboard view, the application times out.

If I open up the view and re-save it, all is well again for a few days.

What gives?

Views are supposedly not pre-compiled, though I know that 2000 stores bits and pieces of query plans.

Any ideas on what causes this and what to do about it?are you experiencing large amounts of new data on a regular basis throwing off statistics and fragmenting your indices. just cuz they ain't precompiled don;t mean that they do not use that stuff right?|||No. Loaded a ton of historical data when the app was first installed, but inserts have been slow and steady since then.

Query Performance

Hello all-

I have a general question. I realize that joins are most of the time better than subqueries. Now that we have the derived tables, if I put the subquery as a derived table and join it with the other tables, will it make any difference in the performance?

eg:

SELECT EmpId
FROM Emp
WHERE EmpSalary = (SELECT MAX(EmpSalary)
FROM Emp Emp2)

Vs

SELECT Emp1.EmpId
FROM Emp Emp1
INNER JOIN (SELECT MAX(EmpSalary) FROM Emp) Emp2
ON Emp1.EmpSalary = Emp2.EmpSalary

(I know that there are better ways of writing this query, sorting my empsalary and getting the top 1 etc. This is just an example to demostrate my question.)

Appreciate your time
ThnxDid you do a show plan on the 2?

That will show you...but I don't think you'll see a difference...

It depeneds more on the number of rows you have and the indexing...