[AccessD] Optimizing Stored Procedures and Views

David,

> I have a stored procedure that is based on a number of views ...  
> In the SQL database that this was developed in it runs fine 
> (in about 30 seconds).  However, when I script the objects and 
> restore them into the second database, the same stored procedure 
> runs for over 10 minutes...

It is a little hard to give advice without more information: 

* Are the views simple views (SELECTs against a single table) or complex
(SELECTs against multiple tables)?
* Are the views indexed?
* Are you returning a subset of the records in your procedure, or
everything? (In other words, is there a WHERE clause you are not showing
us?)
* What is the distribution of the data? For instance, does
vwAgedDebtorGet return greatly more or less records than
vwAgedDebtorGetLastStatDate? 

Even without answers to these questions, there is a diagnostic method
that will almost certainly work: Test by Destruction. To do this: 

1. Make copies of your proc on both databases. 
2. On the copies, remove one view at a time from each proc. (start from
the bottom)
3. Recreate both procs, and test the response time. 
4. Repeat steps 2-3 until your slower proc performs as expected, or you
run out of views

The last view you removed, or the last one standing, is the one that is
introducing the slowdown. Investigate that view, because something about
it is different between the two databases, or the data it is querying is
different. If the view is complex, you can test it by destruction using
the process outlined above to find the table or join that is causing the
problem.

Test by Destruction is a generic, simple way of isolating problems in
complex queries. 

-Ken