Huh? Since the subselect had the same criteria for JOURNALHEADER as JOURNAL and that the JOIN criteria of the subselect where the exact same field as the field of interest, I was certain that the two queries should produce the exact same result.

You can of course try:

WITH LINECODES(LINECODE) AS
(SELECT DISTINCT J2.LINECODE
FROM JOURNALHEADER JH2
INNER JOIN JOURNALS J2 ON JH2.LINECODE = J2.LINECODE
WHERE JH2.JVDATE between '29 Sep 2009 00:00:00' AND '29 Sep 2009 23:00:00' AND
JH2.POSTED = 'Yes' AND
JH2.EFFECTSLEDGER = 'Yes' AND
J2.INTRECNO_LEDGER = 170
)
SELECT
JH.JVDATE,
JH.DESCRIPTION,
JH.VOUCHER,
J.DEBITAMOUNT,
J.CREDITAMOUNT,
LA.ACCOUNTNO,
LA.ACCOUNTNAME
FROM
JOURNALHEADER JH
INNER JOIN JOURNALS J ON JH.LINECODE = J.LINECODE
INNER JOIN LEDGERACCOUNTS LA ON J.INTRECNO_LEDGER = LA.INTRECNO
INNER JOIN LINECODES LC ON J.LINECODE = LC.LINECODE
WHERE JH.JVDATE between '29 Sep 2009 00:00:00' AND '29 Sep 2009 23:00:00' AND
JH.POSTED = 'Yes' AND
JH.EFFECTSLEDGER = 'Yes'

which is an identical rewrite of your original query (but with the excess JH2 alias).

One thing that I simply didn't think of and that I have no idea whether matters: You don't by any chance have a table or view named LINECODES? If so, just use a different name for the above WITH and JOIN clause in the main select.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Marius Labuschagne
Sent: 23. oktober 2009 20:37
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Help with Optimizing a Query

Hello,

Svein thanks for trying to assist.

I have executed the suggestion on my sample set of data, the solution
set should be 245 rows, for the sample query I provided.

Even though your suggestion is super fast, it returns in excess of 3
million records.

The purpose of the Subselect is to obtain the LINECODE of all Journal
Entries that has a specified General Ledger account within the Journal
Entry. Then once I have this, I can select all the Journal Entries that
forms part of the Accounting Journal's which has got the specified
account as part of the Journal Set (Journal Details - Multiple Records
per Journal Header)

The reason I join to the Journal Header table in the Subselect is to be
able to minimize the number of journal details I have to search through,
so in this case it eliminates all records outside the selected date
range, there are millions of Journal Detail records in the table (the
Journals table represents the detail (M) side of a 1 to many relation
between Journal Header and Journals).

The idea behind the output is that I'll be able to give my users the
ability to analyze Contra Journal Entries, based on a given Account
Number. By just including a JOURNALS.INTRECNO_LEDGER <> 170 in the
outer select's where clause, my user's have the choice to see only the
contra accounts or alternatively the contra accounts as well as the
journal leg with the original account number in it.

I hope that the extra explanation above provides more business context
to a technical question, which will hopefully spark some more ideas from
people who will be able to assist me with the optimization of the query.

Please note the original query is not unbearably slow, its just that my
gut feeling is telling me that it could be faster. On my dataset it
currently executes in approx 30 seconds on a month's worth of data.



Svein Erling Tysvær wrote:

>
>
> You're right that your subselect can be executed several times,
> Marius. Moreover, I don't understand why you use JOURNALHEADER in your
> subselect since you already use the same criteria for it in your main
> select and you join it to journals on the linecode. Since you use
> Firebird 2.1 I suggest you try the select below, it should be
> considerably quicker and hopefully get the same result:
>
> with linecodes(linecode)
> as (SELECT distinct J2.LINECODE
> FROM JOURNALS J2
> WHERE J2.INTRECNO_LEDGER = 170)
>
> SELECT
> JH.JVDATE,
> JH.DESCRIPTION,
> JH.VOUCHER,
> J.DEBITAMOUNT,
> J.CREDITAMOUNT,
> LA.ACCOUNTNO,
> LA.ACCOUNTNAME
> FROM
> JOURNALHEADER jh
> INNER JOIN JOURNALS j ON J.LINECODE = J.LINECODE
> INNER JOIN LEDGERACCOUNTS la ON J.INTRECNO_LEDGER = LA.INTRECNO
> inner join linecodes lc on j.linecode = lc.linecode
> WHERE JH.JVDATE between '29 Sep 2009 00:00:00' AND '29 Sep 2009
> 23:00:00' AND
> JH.POSTED = 'Yes' AND
> JH.EFFECTSLEDGER = 'Yes'
>
> HTH,
> Set
>
>

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links