David Emerson
davide at dalyn.co.nz
Mon Dec 1 12:41:13 CST 2003
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