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