Myles,
I realize you re running Firebird v1.5 – there’s a new feature in the just released Firebird v2.0 which should make a very significant impact on your query if you were in a position to use it. See the notes on the Derived Tables feature in the release notes at:
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_download_20

While I haven’t used this under Firebird (only came across it 2 days ago), I have used it in an MS database – and it’s a very powerful and straightforward feature to use. Basically you create two SQL queries – one nested inside the other – where the outer query treats the inner query as if it were a table in its own right – the outer query can join to it and to other tables as well.

In your particular case here one approach would be to have the inner query do a simple select such as:
(SELECT FIRST 20 SKIP 0 ASSET_SERIAL_NO from ASSET order by ….) AS ASSET_20
which should be quite fast as it is a straight run over the ASSET index itself.

Then the outer query would do an inner join from ASSET to the logical ASSET_20 table above and then join to all the other tables as before, but only for the particular 20 records required – quite a saving one might expect!

HTH,
Cheers,
John

> -------Original Message-------
> From: myles@...
> Subject: [firebird-support] Optimizing a query
> Sent: 28 Nov '06 18:44
>
> I am running a query in a stored procedure on a table containing 400,000
> rows. The query appears to be simple to me, and I have indexes created on
> all joins, etc. I have checked the plan which tells me that there is a
> plan
> in place for this query.
>
> I only wish to receive 20 records per query as the results are being
> displayed on a web page.
>
> I call the stored procedure as follows:
>
> SELECT FIRST 20 SKIP 0 * from ASSET_LIST_GET_PRC();
>
> The query is taking 34 minutes to run, which clearly isn't going to work.
> Here is the query:
>
> SELECT
> ASSET.ASSET_ID,
> ASSET.ASSET_SERIAL_NO,
> CLIENT.CLIENT_NAME,
> ASSET.PURCHASE_DATE,
> ASSET_TYPE1.AT_NAME,
> MODEL.MODEL_NO,
> ASSET.DESCRIPTION
> FROM
> ASSET
> INNER JOIN ASSET_TYPE ASSET_TYPE1 ON (ASSET.AT_ID =
> ASSET_TYPE1.AT_ID)
> INNER JOIN CLIENT ON (ASSET.FK_CLIENT_ID = CLIENT.CLIENT_ID)
> INNER JOIN CLIENT_SITE ON (ASSET.FK_SITE_ID = CLIENT_SITE.SITE_ID)
> INNER JOIN MODEL ON (ASSET.MODEL_ID = MODEL.MODEL_ID),
> ASSET_TYPE
> order by ASSET.ASSET_SERIAL_NO ASCENDING
> into
> :R_ASSET_ID,
> :R_ASSET_SERIAL_NO,
> :r_client_name,
> :R_PURCH_DATE,
> :R_ASSET_TYPE,
> :R_MODEL_NO,
> :R_DESCRIPTION
>
> Am I approaching this the right way, based on what I need to get from
> this
> query? Should I be doing a separate query first to get the candidate
> rows,
> and then somehow joining these resulting rows back to this query to get
> just
> those records with the joined values?
>
> What's the secret to getting performance from Firebird in this scenario?
>
> I'm running this on a Firebird 1.5 Super Server running on Fedora Core 5
> Linux.
>
> Thanks in advance for any advice.
>
> Regards,
> Myles
>
> ============================
> Myles Wakeham
> Director of Engineering
> Tech Solutions US, Inc.
> Scottsdale, Arizona USA
> Phone (480) 451-7440
> www.techsol.org
>
>