[dba-SQLServer] [AccessD] Optimizing Stored Procedures and Views

Thanks to all who have responded.  I am working my way through the suggestions.

I downloaded the trial version of Red-Gate SQL comparison.  It only 
showed some extended properties as being different in the various 
tables, views and sprocs.  I synchronized the objects but the problem 
still persists.

Using Ken's Test by Destruction method I identified the problem view 
as highlighted below:

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

This is the whole of the sproc - there are no other where clauses etc.

When I delete this join (and the fields in the SELECT section from 
the dbo.vwAgedDebtorReceiptInv view) then the sproc runs in 4 seconds 
and returns 680 rows.

When I run the dbo.vwAgedDebtorReceiptInv view within Access it 
returns 2780 rows in a couple of second.

Ken asked if the views are indexed.  I couldn't see any properties in 
SQL2000 in the view for indexing.  The StatementNumber and InvNum 
fields in the underlying tables are indexed

I have used the dbo.vwAgedDebtorReceiptInv view in other sprocs and 
they work in an acceptable time.  In all other cases though the join 
is to a table, not another view. (However, remember that the same 
sproc runs fine in the second database).

The dbo.vwAgedDebtorReceiptInv is not a simple view:

SELECT TOP 100 PERCENT
    SUM(ROUND((CASE WHEN TranCode = 'CR' THEN CASE WHEN TranAmt < 0 
THEN TranAmt ELSE 0 END ELSE 0 END)
         * CASE WHEN GSTPayable = 1 THEN (1 + GSTRate) ELSE 1 END, 2))
         + SUM(ROUND((CASE WHEN TranCode = 'CN' THEN CASE WHEN 
TranAmt < 0 THEN TranAmt ELSE 0 END ELSE 0 END)
         * CASE WHEN GSTPayable = 1 THEN (1 + GSTRate) ELSE 1 END, 2))
         + SUM(ROUND((CASE WHEN TranCode = 'RR' THEN CASE WHEN 
TranAmt > 0 THEN TranAmt ELSE 0 END ELSE 0 END)
         * CASE WHEN GSTPayable = 1 THEN (1 + GSTRate) ELSE 1 END, 
2)) AS NetReceiptsInv,
    SUM(ROUND((CASE WHEN TranCode = 'CR' THEN CASE WHEN TranAmt < 0 
THEN TranAmt ELSE 0 END ELSE 0 END)
         * CASE WHEN GSTPayable = 1 THEN (1 + GSTRate) ELSE 1 END, 
2)) AS CRReceiptsInv,
    SUM(ROUND((CASE WHEN TranCode = 'CN' THEN CASE WHEN TranAmt < 0 
THEN TranAmt ELSE 0 END ELSE 0 END)
         * CASE WHEN GSTPayable = 1 THEN (1 + GSTRate) ELSE 1 END, 
2)) AS CNReceiptsInv,
    SUM(ROUND((CASE WHEN TranCode = 'RR' THEN CASE WHEN TranAmt > 0 
THEN TranAmt ELSE 0 END ELSE 0 END)
         * CASE WHEN GSTPayable = 1 THEN (1 + GSTRate) ELSE 1 END, 
2)) AS RevReceiptsInv,
    SUM(ROUND((CASE WHEN TranCode = 'GJ' THEN CASE WHEN TranAmt < 0 
THEN TranAmt ELSE 0 END ELSE 0 END)
         * CASE WHEN GSTPayable = 1 THEN (1 + GSTRate) ELSE 1 END, 
2)) AS GJCreditInv, dbo.tblGenJournal.LinkIDNo,
    dbo.tblGenJournal.CustIDNo, dbo.tblCustInvoice.InvoiceNumber - 1 
AS InvNum, dbo.tblCustInvoice.InvoiceDate
FROM dbo.tblGenJournal INNER JOIN dbo.tblCustInvoice ON 
dbo.tblGenJournal.LinkIDNo = dbo.tblCustInvoice.InvoiceID
WHERE (dbo.tblGenJournal.TranType = 1)
GROUP BY dbo.tblGenJournal.LinkIDNo, dbo.tblGenJournal.CustIDNo, 
dbo.tblCustInvoice.InvoiceNumber - 1, dbo.tblCustInvoice.InvoiceDate
HAVING (dbo.tblCustInvoice.InvoiceNumber - 1 > 0)
ORDER BY dbo.tblGenJournal.LinkIDNo


David

At 4/04/2006, you wrote:

>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
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com