[dba-SQLServer]Slow Sproc execution

This is one way, additionally looking at your sample code it looks like 
you're not really using temp tables at all, instead you've created a 
view to temporary use.  why not use "real" temp tables, that way you 
don't have recreate views all the time w/ that sproc anymore... instead 
you create a temporary table with the subset of data you wanted, it gets 
stored for the duration of the sproc and you'd only be making the call 
once not twice to the source tables as your sample seems to indicate.

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)

        CREATE TABLE #AgedDebtorGet
			(Column1 [int] Null,
			 Column2 [int] Null,
			 Column3 [varchar] (255) Null
			)

		INSERT INTO #AgedDebtorGet
			--SELECT statement w/ Dates

IF @bitDD = 1 AND @bitDDFreq <> '0'
	BEGIN
		SELECT * FROM #AgedDebtorGet
		WHERE (#vwAgedDebtorGet.DDFrequency = @bitDDFreq )
		AND (#vwAgedDebtorGet.DDRegistered = 1)
	END
ELSE IF @bitDD = 1 AND @bitDDFreq = '0'
	BEGIN
		SELECT * FROM #AgedDebtorGet
		WHERE (#vwAgedDebtorGet.DDFrequency = @bitDDFreq )
	END


They live for the duration of your sproc.  Please note that you'll 
actually have to re-do the #TempTable to the appropriate columns


--
-Francisco


David Emerson wrote:

> I ended up creating a temporary table for the first part and then 
> creating a dynamic sql applying the where clause using the table.  
> Changed the time from 40 sec to about 2.
> 
> How could I create a static sql when the filters to be included depend 
> on the parameters passed in.
> 
> Regards
> 
> David Emerson
> Dalyn Software Ltd
> 25 Cunliffe St, Churton Park
> Wellington, New Zealand
> Ph/Fax (877) 456-1205
> 
> At 1/12/2003, you wrote:
> 
>> 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
> 
>