David Emerson
davide at dalyn.co.nz
Mon Dec 1 14:19:40 CST 2003
Sorry to take up your time. The code in your message was from my original problem. I subsequently solved it by changing it to a 'real' temp table as you suggest. David At 1/12/2003, you wrote: >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 >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > >