>
> 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();

no - you need to pass the paging parameters into the procedure and let the
queries in the procedure handle this paging. Don't use FIRST skip on a
proceure output since you will be processing all records the most expensive
way everytime you call it. I have used it sometimes but only when performace
is acceptable and therefore only on small tables with inexpensive queries -
(and when I'm feeling really lazy)
Alan

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