[AccessD] Stored Procedure not producing results

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