[dba-SQLServer]Slow Sproc execution

David Emerson davide at dalyn.co.nz
Fri Nov 28 00:42:29 CST 2003


I have a sproc that has a couple of where statements in it.  If I only 
include 1 statement then the sproc runs within a second and returns about 
128 records.  If I include both statements then it takes about 40 
seconds.  Using the SQL Profiler the single statements record approx 8950 
reads but with both included the SQL Profiler records approx 136000 
reads.  There seems to be a need for SQL to pass through the records 
several times yet the comparisons are both in the same table.  My problem 
is how to reduce the time taken to run the sproc.  Here is the code -

ALTER PROCEDURE sprptAgedDebtor
         (
                 @txtAged1Dte nvarchar(20),
                 @txtAged2Dte nvarchar(20),
                 @bitDD bit,
                 @bitDDFreq varchar(1)
         )
AS
         SET NOCOUNT ON

         --The next line creates a view. The resulting view has about 13000 
records in it
         exec spvwAgedDebtorGetLastStatDate @txtAged1Dte,  @txtAged2Dte

         declare @qs varchar(8000)

SELECT @qs = '
         SELECT dbo.vwAgedDebtorGet.CustomerID, 
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.vwAgedDebtorReceipt.CNReceipts, 
dbo.vwAgedDebtorReceipt.Receipts, dbo.vwAgedDebtorGet.MGrpIDNo,
                 dbo.vwAgedDebtorGet.DDRegistered, 
dbo.vwAgedDebtorGet.DDFrequency
         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 '
IF @bitDD = 1
         BEGIN
                 SELECT @qs = @qs + 'WHERE (dbo.vwAgedDebtorGet.DDFrequency 
= ' + @bitDDFreq + ')'
         END
IF @bitDDFreq <> '0'
         BEGIN  -- @bitDD will always be 1 if @bitDDFreq <> '0'
                 SELECT @qs = @qs + ' AND (dbo.vwAgedDebtorGet.DDRegistered 
= 1)  '
         END

EXEC (@qs)



Regards

David Emerson
Dalyn Software Ltd
25 Cunliffe St, Churton Park
Wellington, New Zealand
Ph/Fax (877) 456-1205 


More information about the dba-SQLServer mailing list