[AccessD] Optimizing Stored Procedures and Views

David, 

> I couldn't see any properties in SQL2000 in the view for indexing.  

SQL Server Books Online has a topic "Creating an Indexed View", that
covers how to create Indexed Views for SQL 2000. However, because the
problem view uses a TOP and an ORDER BY clause, it can't be indexed.

(As an aside, I don't recommend indexed views because their requirements
are so stringent. One big gotcha: all INSERT, UPDATE and DELETE
statements on tables referenced in the view must be executed with the
exact same SET options as are required for indexing the view. So if you
index a view, an application that doesn't use the exact SET options
CANNOT modify data on any tables referenced in that view.)

> The StatementNumber and InvNum fields in the underlying tables are
indexed ...

I don't think that underlying indexes will help much when joining
against aggregate queries. 

> In all other cases though the join is to a table, not another view ...


That may be a key, because SQL Server is likely using an index on the
table to speed the join with the view. In this problem case, an
aggregate view joined against an aggregate view may have to be resolved
using a Cartesian product, which is very expensive.

Others may have better insights to provide, but see BOL topic "Resolving
Indexes on Views", and judge for yourself.


Suggestion
----------

If joining tables to views gives good performance, duplicate that in
your procedure. Use a new SQL Server 2000 feature called Table
Variables. See "Table Variables" in BOL index.

Define a table variable at the start of your proc: 

DECLARE @temp_cust_stmt TABLE (
	CustomerID        Int,  -- (I'm guessing at datatypes)
	StatementNumber 	Int, 
	StatementDate	DateTime, 
	PRIMARY KEY 	(CustomerID, StatementNumber)
	)

(I'm guessing at the Fields and Primary Key; modify it if it doesn't
suit your data.)

Fill the table variable: 

INSERT INTO @temp_cust_stmt (CustomerID, StatementNumber, StatementDate)

SELECT DISTINCT CustomerID, StatementNumber, StatementDate
FROM 	dbo.vwAgedDebtorGet 

(This assumes there is only one StatementDate per StatementNumber)

Modify your FROM clause to join all views against your temp table: 

FROM  @temp_cust_stmt AS t INNER JOIN dbo.vwAgedDebtorGet 
        ON t.CustomerID = dbo.vwAgedDebtorGet.CustomerID AND
t.StatementDate = dbo.vwAgedDebtorGet.StatementDate 
        INNER JOIN dbo.vwAgedDebtorGetLastStatDate ON t.CustomerID =
dbo.vwAgedDebtorGetLastStatDate.CustIDNo 
            AND t.StatementDate =
dbo.vwAgedDebtorGetLastStatDate.StatementDate 
(etc.)

That way, you are joining all of your views against an indexed
(temporary) table. That might speed things up quite a bit. 

BTW, could you join vwAgedDebtorGet against StatementNumber instead of
StatementDate? If the data allows it, that might be a better join
candidate.

Hope this helps, 

-Ken