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