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
>
>