[AccessD] Optimizing Stored Procedures and Views

Check your indexes in the tables that make up the views on the db that is
running slow and compare them with the same tables in the db that is running
fast. I'd venture to say you don't have one or more columns indexed in the
slow db that should be indexed.

Also, I would suggest using aliases in your stored procedures or any long
query. It makes reading them much easier. Such as this:

SELECT 
	a.CustomerID
	,a.StatementID
	,a.AccountNo
	,a.CustName
	,a.StatementDate
	,a.StatementNumber
	,a.AccFreq
	,a.CurrentMth
	,a.OneMonth
	,a.TwoMonths
	,a.ThreeMonths
	,a.NoOverdueNote
	,c.NetReceipts
	,c.GJCredit
	,d.NetReceiptsInv
	,d.GJCreditInv
	,a.MGrpIDNo
	,a.DDRegistered
	,a.DDFrequency
	,a.PayArrange
	,e.ReceiptNotPost AS ReceiptsNotPosted
	,a.VIPCust
	,a.AccStatus
FROM dbo.vwAgedDebtorGet AS a
INNER JOIN dbo.vwAgedDebtorGetLastStatDate AS b
	ON a.CustomerID = b.CustIDNo 
	AND a.StatementDate = b.StatementDate 
LEFT OUTER JOIN dbo.vwAgedDebtorReceipt AS c
	ON a.CustomerID = c.CustIDNo 
	AND a.StatementNumber = c.StatNum 
LEFT OUTER JOIN dbo.vwAgedDebtorReceiptInv AS d 
	ON a.CustomerID = d.CustIDNo 
	AND a.StatementNumber = d.InvNum
LEFT OUTER JOIN dbo.vwAgedDebtorReceiptNotPost AS e
	ON a.CustomerID = e.CustIDNo 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David Emerson
Sent: Sunday, April 02, 2006 6:03 PM
To: accessd at databaseadvisors.com; dba-SQLServer at databaseadvisors.com
Subject: [AccessD] Optimizing Stored Procedures and Views

Cross posted AccessD SQL

SQL2000

I have a stored procedure that is based on a number of views.  I have 
two different databases that have the same tables, views and stored 
procedures.  The only difference is that the data is for separate 
companies.  Both databases are on the same server.

I developed one set of queries and stored procedure in one of the 
databases.  The main part of the stored procedure is as follows:

SELECT dbo.vwAgedDebtorGet.CustomerID, 
dbo.vwAgedDebtorGet.StatementID, 
dbo.vwAgedDebtorGet.AccountNo,  dbo.vwAgedDebtorGet.CustName, 
dbo.vwAgedDebtorGet.StatementDate, dbo.vwAgedDebtorGet.StatementNumber,
         dbo.vwAgedDebtorGet.AccFreq, dbo.vwAgedDebtorGet.CurrentMth, 
dbo.vwAgedDebtorGet.OneMonth,
         dbo.vwAgedDebtorGet.TwoMonths, 
dbo.vwAgedDebtorGet.ThreeMonths, dbo.vwAgedDebtorGet.NoOverdueNote,
         dbo.vwAgedDebtorReceipt.NetReceipts, 
dbo.vwAgedDebtorReceipt.GJCredit, dbo.vwAgedDebtorReceiptInv.NetReceiptsInv,
         dbo.vwAgedDebtorReceiptInv.GJCreditInv,
dbo.vwAgedDebtorGet.MGrpIDNo,
         dbo.vwAgedDebtorGet.DDRegistered, 
dbo.vwAgedDebtorGet.DDFrequency, dbo.vwAgedDebtorGet.PayArrange,
         dbo.vwAgedDebtorReceiptNotPost.ReceiptNotPost AS 
ReceiptsNotPosted, 
dbo.vwAgedDebtorGet.VIPCust,        dbo.vwAgedDebtorGet.AccStatus
FROM dbo.vwAgedDebtorGet INNER JOIN dbo.vwAgedDebtorGetLastStatDate 
ON dbo.vwAgedDebtorGet.CustomerID =
         dbo.vwAgedDebtorGetLastStatDate.CustIDNo AND 
dbo.vwAgedDebtorGet.StatementDate =
         dbo.vwAgedDebtorGetLastStatDate.StatementDate LEFT OUTER 
JOIN dbo.vwAgedDebtorReceipt ON
         dbo.vwAgedDebtorGet.CustomerID = dbo.vwAgedDebtorReceipt.CustIDNo
AND
         dbo.vwAgedDebtorGet.StatementNumber = 
dbo.vwAgedDebtorReceipt.StatNum LEFT OUTER JOIN dbo.vwAgedDebtorReceiptInv
ON
         dbo.vwAgedDebtorGet.CustomerID = 
dbo.vwAgedDebtorReceiptInv.CustIDNo AND
         dbo.vwAgedDebtorGet.StatementNumber = 
dbo.vwAgedDebtorReceiptInv.InvNum
         LEFT OUTER JOIN dbo.vwAgedDebtorReceiptNotPost ON
         dbo.vwAgedDebtorGet.CustomerID = 
dbo.vwAgedDebtorReceiptNotPost.CustIDNo

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 with about a fifth of the data.

Is there some optimizing that I need to do to get the second database 
to work as fast as the first one?

Regards

David Emerson
Dalyn Software Ltd
New Zealand 
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com