[AccessD] Stored Procedure not producing results

Jim,

Thanks for the info - both items to consider for future development.

However, my issue is no speed (my database with 4 times the amount of 
data runs in less than 2 seconds).  It seems to be either data in the 
smaller one, or a change in structure.

David

At 23/11/2006, you wrote:
>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
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com