[AccessD] Optimizing Stored Procedures and Views

David Emerson newsgrps at dalyn.co.nz
Sun Apr 2 21:02:37 CDT 2006


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 


More information about the AccessD mailing list