[dba-SQLServer]Slow Sproc execution

David Emerson davide at dalyn.co.nz
Sat Nov 29 14:30:08 CST 2003


Problem solved - I created a table first before applying the filter statements.

At 28/11/2003, I wrote:
>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 _______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>

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