Hi Myles!

First something that I didn't notice until I read your PLAN. Your SQL
contains one error! A rather funny error that seems to have caused you
problems before since you alias ASSET_TYPE, but you "solved" the syntax
error through aliasing rather than delete the extraneous ASSET_TYPE:

INNER JOIN MODEL ON (ASSET.MODEL_ID = MODEL.MODEL_ID),
ASSET_TYPE

I.e. ASSET_TYPE is joined in a very strange way that I cannot even start
to think how affects your performance (SQL-89 JOIN without a WHERE
clause)! Remove ', ASSET_TYPE' and check your timing again. Sorting
400000 records in memory may take some time, but 34 minutes sounds
excessive.

Apart from this, your biggest problem is - in my opinion - the lack of a
WHERE clause. If it had been something like Google, most people would
stop after 1 or 2 pages, though I can imagine some very interested
persons to read up to about 100 pages. Still, that is only 2000 records
if there are 20 records per page, meaning that 398000 records are excess
baggage in virtually all cases.

Start thinking more client/server, let the user specify a bit what he
wants before you try to show it to him. Maybe he knows the clients name
or approximate purchase date or whatever. Starting from the top and
intending to go through 400000 records is a bit like wanting to contact
a friend that you've forgotten the name and address of, but knows that
lives in your town, and then start going through the telephone directory
from the beginning because you believe you will recognise his name when
you see it.

Hence, I suggest that you make your procedure(s) accept input parametres
and that you in all cases use FIRST 1000 (or something similar) within
your stored procedure to prevent too wide criteria that potentially show
more more pages than your users will care to read (you can use SELECT
FIRST 20 FROM MySP() even if MySP itself contains FOR SELECT FIRST 1000).

HTH,
Set

myles@... wrote:

> 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