[dba-SQLServer]Slow Sproc execution

try to use static sql instead of dynamic and you'll see the speed 
improve as well.  Additionally what is your query execution time.


-- 
-Francisco


David Emerson 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
> 
>