[AccessD] Stored Procedure not producing results

Precisely. I call this "Atomic and Molecular Queries." Atomic queries address precisely one table (or view), and molecular queries assemble at least two atomic queries. This gives you a) optimization and b) re-usability.

Arthur

----- Original Message ----
From: David Emerson <newsgrps at dalyn.co.nz>
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Sent: Tuesday, November 21, 2006 10:18:31 PM
Subject: Re: [AccessD] Stored Procedure not producing results

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

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