[AccessD] Stored Procedure not producing results

Hi David:

Just a further comment after conferring with a friend; it was noted that
each one of the statements, that were plused together require a complete
scan of their respective tables before they can be added.

The overhead must be immense. Try using the Rollup or Cube statements, which
are highly optimized and should give you a decent speed increase
(http://databases.about.com/od/sql/l/aacuberollup.htm)

It was also suggested that, if you have control over the database tables
designing and this report is needed on a regular bases, a single table
consolidating the flag fields would improve performance.

HTH
Jim       

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David Emerson
Sent: Tuesday, November 21, 2006 5:17 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Stored Procedure not producing results

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.


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com