David Emerson
newsgrps at dalyn.co.nz
Tue Nov 21 21:18:31 CST 2006
Thanks Arthur, I am not quite sure what you mean. Are you suggesting that I create queries within the query to do the filtering? For example, instead of filtering the dbo.tblCustomers table in the WHERE statement, I should use a query in the FROM statement based on the dbo.tblCustomers table and filter there? David At 22/11/2006, you wrote: >You are forcing table scans everywhere. You must rethink this. >Divide the problems into atomic queries and then build up to >molecular queries. The ANDs are killing you. Do the OUTER JOINs >first, each as an atom, then combine those, then do your query. I >suggest that performance will escalate dramatically. > >hth, >Arthur > >David Emerson wrote: > > >SQL 2000 > > > >I have the following query running in Query Analyser: > > > >SELECT dbo.tblCustStatement.StatementID, dbo.tblCustomers.TradingName > >as CustName, dbo.tblCustStatement.StatementDate, > > dbo.tblCustomers.AccountNo, dbo.tblCustStatement.StatementNumber > >FROM dbo.tblCustomers INNER JOIN dbo.tblCustICP ON > >dbo.tblCustomers.CustomerID = dbo.tblCustICP.CustIDNo > > INNER JOIN dbo.tblCustStatement ON > >dbo.tblCustomers.CustomerID = dbo.tblCustStatement.CustIDNo > > LEFT OUTER JOIN dbo.vwAgedDebtorReceipt ON > >dbo.tblCustStatement.CustIDNo = dbo.vwAgedDebtorReceipt.CustIDNo > > AND dbo.tblCustStatement.StatementNumber = > >dbo.vwAgedDebtorReceipt.StatNum > > LEFT OUTER JOIN dbo.vwAgedDebtorReceiptInv ON > >dbo.tblCustStatement.CustIDNo = dbo.vwAgedDebtorReceiptInv.CustIDNo > > AND dbo.tblCustStatement.StatementNumber = > >dbo.vwAgedDebtorReceiptInv.InvNum > >WHERE > > -- Remming out this statement works (and removing the AND at > >the beginning of the next line) > > (dbo.tblCustStatement.StatementDate >= '2006-11-01' And > >dbo.tblCustStatement.StatementDate <= '2006-11-30') > > -- Remming out this statement works > > AND (dbo.tblCustomers.AccStatus= 'Active') > > AND (dbo.tblCustStatement.[CurrentMth]+ > >dbo.tblCustStatement.[OneMonth]+ > >dbo.tblCustStatement.[TwoMonths]+ > >dbo.tblCustStatement.[ThreeMonths] + > >ISNULL(dbo.vwAgedDebtorReceipt.NetReceipts,0) > >+ ISNULL(dbo.vwAgedDebtorReceipt.GJCredit,0)+ > >ISNULL(dbo.vwAgedDebtorReceiptInv.NetReceiptsInv,0) > >+ ISNULL(dbo.vwAgedDebtorReceiptInv.GJCreditInv,0) > 20) > > AND (dbo.tblCustomers.PayArrange = 0) > > AND (dbo.tblCustomers.NoOverdueNote = 0) > > -- Remming out this statement works > > AND ( CASE > > WHEN > >ISNUMERIC(LEFT(dbo.tblCustomers.CreditStatus,2)) = 0 THEN '00' > > ELSE LEFT(dbo.tblCustomers.CreditStatus,2) > > END < CAST(DAY(GETDATE()) AS varchar(2))) > > AND (dbo.tblCustomers.CreditStatus NOT LIKE 'Last%' OR > >dbo.tblCustomers.CreditStatus IS NULL) > > -- Remming out this statement works > > AND (dbo.tblCustStatement.StatementNumber <> 1) > >ORDER BY CustName, dbo.tblCustomers.AccountNo, > >dbo.tblCustStatement.StatementDate > > > >When I remark out any 1 of the AND statements noted above in the > >WHERE clause the results come back in a couple of seconds. > >However, when I include all AND statement the query runs doesn't > >return any results (at least not within the first ten minutes!) > > > >There doesn't seem to be any pattern to the lines that can be remmed > >or combinations of remaining lines. The only clue I have is that > >data from the database from two months ago works fine but there are > >750 more records in each table being filtered. > > > >Any suggestions as to how I can solve this - it seems to be a data > >issue but the various combinations of AND statements above doesn't > make sense. > > > > > > > > > >-- >Marty Connelly >Victoria, B.C. >Canada > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com > > > > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com