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

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

Hello,

Environment:
- Windows XP Pro SP3
- Firebird Superserver 2.1.3.18185
- Using Delphi 7 and FIBPlus components to interface with Firebird

I have the following query and hope that someone may assist to speed the
query up. When working with a small date range, the response is fine, it
slows down considerably when you query "wider" date ranges.

SELECT
JOURNALHEADER.JVDATE,
JOURNALHEADER.DESCRIPTION,
JOURNALHEADER.VOUCHER,
JOURNALS.DEBITAMOUNT,
JOURNALS.CREDITAMOUNT,
LEDGERACCOUNTS.ACCOUNTNO,
LEDGERACCOUNTS.ACCOUNTNAME
FROM
JOURNALHEADER
INNER JOIN JOURNALS ON JOURNALHEADER.LINECODE = JOURNALS.LINECODE
INNER JOIN LEDGERACCOUNTS ON JOURNALS.INTRECNO_LEDGER =
LEDGERACCOUNTS.INTRECNO
WHERE
JOURNALS.LINECODE IN (SELECT J.LINECODE
FROM JOURNALHEADER JH , JOURNALS J
WHERE JH.LINECODE = J.LINECODE AND
JH.JVDATE >= '29 Sep 2009 00:00:00' AND
JH.JVDATE <= '29 Sep 2009 23:00:00' AND
JH.POSTED = 'Yes' AND
JH.EFFECTSLEDGER = 'Yes' AND
J.INTRECNO_LEDGER = 170
) AND
JOURNALHEADER.JVDATE >= '29 Sep 2009 00:00:00' AND
JOURNALHEADER.JVDATE <= '29 Sep 2009 23:00:00' AND
JOURNALHEADER.POSTED = 'Yes' AND
JOURNALHEADER.EFFECTSLEDGER = 'Yes'

Primary Links are as follows:
JournalHeader to Journals with LineCode field
Journals to Ledger Accounts with INTRECNO_Ledger to INTRECNO

The one thing that jumps out to me is the Sub Select's query in the
where statement, it is using the following plan:
PLAN JOIN (JOURNALHEADER INDEX (I_JOURNALHEADER_JVDATE), JOURNALS INDEX
(I_JOURNALS_LINECODE))
I'm wondering if I cannot get this section to make use of the
"I_JOURNALS_INTRECNO_LEDGER" index on the JOURNALS table, as this I
think would speed up at least the Sub Select query, which I suppose will
be executed numerous times.

The table definitions are below:

Journal Header
===========
CREATE TABLE JOURNALHEADER
(
INTRECNO INTEGER NOT NULL,
LINECODE VARCHAR( 50) NOT NULL COLLATE NONE,
JVDATE TIMESTAMP,
DESCRIPTION VARCHAR( 50) COLLATE NONE,
VOUCHER VARCHAR( 50) COLLATE NONE,
POSTED VARCHAR( 3) DEFAULT 'No' COLLATE NONE,
VATEFFECT VARCHAR( 3) COLLATE NONE,
TRANSACTIONTYPE INTEGER NOT NULL,
EFFECTSLEDGER VARCHAR( 3) COLLATE NONE,
INTRECNO_SALES INTEGER,
INTRECNO_PURCHASES INTEGER,
INTRECNO_CUSTOMER INTEGER,
TOTALAMOUNT DOUBLE PRECISION,
QUANTITY NUMERIC( 15, 4),
FP_USER VARCHAR( 30) DEFAULT CURRENT_USER
NOT NULL COLLATE NONE,
FP_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
REPLICATED VARCHAR( 3) DEFAULT 'No' COLLATE NONE,
INTRECNO_STOCK INTEGER,
OBJECTTYPE VARCHAR( 10) COLLATE NONE,
OBJECTID INTEGER,
BRANCHNO VARCHAR( 15) COLLATE NONE,
CBT_ID VARCHAR( 20) COLLATE NONE,
ENTITYNO VARCHAR( 30) DEFAULT '' COLLATE NONE,
PRIMARY KEY (INTRECNO)
);
Indexes:
CREATE ASC INDEX I_JOURNALHEADER_CBT ON JOURNALHEADER (CBT_ID);
CREATE ASC INDEX I_JOURNALHEADER_CUSTOMER ON JOURNALHEADER
(INTRECNO_CUSTOMER);
CREATE ASC INDEX I_JOURNALHEADER_FP_DATE ON JOURNALHEADER (FP_DATE);
CREATE ASC INDEX I_JOURNALHEADER_INTSKU ON JOURNALHEADER (INTRECNO_STOCK);
CREATE ASC INDEX I_JOURNALHEADER_JVDATE ON JOURNALHEADER (JVDATE);
CREATE ASC INDEX I_JOURNALHEADER_LINECODE ON JOURNALHEADER (LINECODE);
CREATE ASC INDEX I_JOURNALHEADER_TTYPE ON JOURNALHEADER (TRANSACTIONTYPE);
CREATE UNIQUE ASC INDEX RDB$PRIMARY21 ON JOURNALHEADER (INTRECNO);

Journal Detail
=========
CREATE TABLE JOURNALS
(
INTRECNO INTEGER NOT NULL,
LINECODE VARCHAR( 50) NOT NULL COLLATE NONE,
DEBITAMOUNT NUMERIC( 8, 2) DEFAULT 0,
CREDITAMOUNT DOUBLE PRECISION DEFAULT 0,
INTRECNO_LEDGER INTEGER NOT NULL,
LEDGERTYPE VARCHAR( 20) COLLATE NONE,
BRANCHNO VARCHAR( 15) COLLATE NONE,
REPLICATED VARCHAR( 3) DEFAULT 'No' COLLATE NONE,
CASHBOOKREF VARCHAR( 15) COLLATE NONE,
INTEGRATIONREF VARCHAR( 20) COLLATE NONE,
RECONINDICATOR VARCHAR( 3) COLLATE NONE,
RECONDATE TIMESTAMP,
RECONBYUSER VARCHAR( 30) COLLATE NONE,
CBT_ID VARCHAR( 20) COLLATE NONE,
RECONID VARCHAR( 10) COLLATE NONE,
RECONSTATUS VARCHAR( 15) COLLATE NONE,
PRIMARY KEY (INTRECNO)
);
Indexes:
CREATE ASC INDEX I_JOURNALS_CBT ON JOURNALS (CBT_ID);
CREATE ASC INDEX I_JOURNALS_INTRECNO_LEDGER ON JOURNALS (INTRECNO_LEDGER);
CREATE ASC INDEX I_JOURNALS_LINECODE ON JOURNALS (LINECODE);
CREATE ASC INDEX I_JOURNALS_RECONID ON JOURNALS (RECONID);
CREATE UNIQUE ASC INDEX RDB$PRIMARY20 ON JOURNALS (INTRECNO);

Ledger Accounts
============
CREATE TABLE LEDGERACCOUNTS
(
INTRECNO INTEGER NOT NULL,
ACCOUNTNO VARCHAR( 24) NOT NULL COLLATE NONE,
ACCOUNTTYPE VARCHAR( 20) NOT NULL COLLATE NONE,
ACCOUNTNAME VARCHAR( 40) NOT NULL COLLATE NONE,
EXTERNALACCOUNT VARCHAR( 40) COLLATE NONE,
CURRENTBALANCE NUMERIC( 8, 2) DEFAULT 0,
ISCONTROLACCOUNT VARCHAR( 3) DEFAULT 'Yes' NOT NULL
COLLATE NONE,
CONTROLACCOUNT VARCHAR( 24) COLLATE NONE,
SPECIALACCOUNT VARCHAR( 20) COLLATE NONE,
REPORTGROUP VARCHAR( 20) COLLATE NONE,
ISBOOKABLE VARCHAR( 3) DEFAULT 'No' COLLATE NONE,
OPENBALANCE DOUBLE PRECISION DEFAULT 0,
OPENBALANCEDATE DATE,
PRIMARY KEY (INTRECNO)
);
Indexes:
CREATE UNIQUE ASC INDEX I_ACCOUNTNO_LEDGER ON LEDGERACCOUNTS (ACCOUNTNO);
CREATE ASC INDEX I_LEDGERACCOUNTS_ACCTNO ON LEDGERACCOUNTS (ACCOUNTNO);
CREATE UNIQUE ASC INDEX RDB$PRIMARY16 ON LEDGERACCOUNTS (INTRECNO);


Any help or guidance will be appreciated.

Regards
Marius


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

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

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