> What is the plan generated by this query?

This is what the plan says:

Plan
PLAN SORT (JOIN (JOIN (MODEL NATURAL,ASSET INDEX (RDB$FOREIGN61),CLIENT_SITE
INDEX (PK_CLIENT_SITE),ASSET_TYPE1 INDEX (RDB$PRIMARY32),CLIENT INDEX
(RDB$PRIMARY3)),ASSET_TYPE NATURAL))SORT (JOIN (JOIN (MODEL NATURAL,ASSET
INDEX (RDB$FOREIGN61),CLIENT_SITE INDEX (PK_CLIENT_SITE),ASSET_TYPE1 INDEX
(RDB$PRIMARY32),CLIENT INDEX (RDB$PRIMARY3)),ASSET_TYPE NATURAL))SORT (JOIN
(JOIN (MODEL NATURAL,ASSET INDEX (RDB$FOREIGN61),CLIENT_SITE INDEX
(PK_CLIENT_SITE),ASSET_TYPE1 INDEX (RDB$PRIMARY32),CLIENT INDEX
(RDB$PRIMARY3)),ASSET_TYPE NATURAL))

Adapted Plan
PLAN SORT (JOIN (JOIN (MODEL NATURAL,ASSET INDEX (INTEG_173),CLIENT_SITE
INDEX (PK_CLIENT_SITE),ASSET_TYPE1 INDEX (INTEG_121),CLIENT INDEX
(INTEG_43)),ASSET_TYPE NATURAL))SORT (JOIN (JOIN (MODEL NATURAL,ASSET INDEX
(INTEG_173),CLIENT_SITE INDEX (PK_CLIENT_SITE),ASSET_TYPE1 INDEX
(INTEG_121),CLIENT INDEX (INTEG_43)),ASSET_TYPE NATURAL))SORT (JOIN (JOIN
(MODEL NATURAL,ASSET INDEX (INTEG_173),CLIENT_SITE INDEX
(PK_CLIENT_SITE),ASSET_TYPE1 INDEX (INTEG_121),CLIENT INDEX
(INTEG_43)),ASSET_TYPE NATURAL))

I'm not sure if this helps or not.

> Given that you have no 'where' clause, you probably want to do
> an
> indexed walk, so make sure you have an ascending index on ASSET
> (ASSET_SERIAL_NO_ASCENDING).

Yes, I do have that.

> > 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?
>
> Well if you have an efficient method of getting the candidate
> rows,
> that is prefered, but if you look at your query, it may
> *complete*
> faster if it starts with the largest table in natural order and
> joins
> out from there, then sorts in memory. The downside is that it
> must do
> every join before it can return the first 20 record. Because you
> are
> only interested in a tiny proportion of records, it makes sense
> to
> read in order of ASSET_SERIAL_NO_ASCENDING, even if it would
> take
> longer to complete the entire table. Locating the first 20
> records is
> very quick, but returning them all would be slower.

I definitely will need to find a way to do this quicker. What I'm doing is
to show a list of the records to the user through PHP with hyperlinks on the
actual primary key for the row. The joins are only provided to expand the
display of the primary table records. So there is no reason why I can't
create a simple query with no joins just to get a list of the record ids to
show. But I'm not sure how to use that list for the 20 rows that I need to
display.

Do you know of any sample code that does this inside a stored procedure?

Regards,
Myles

===============================
Myles Wakeham
Director of Engineering
Tech Solutions US, Inc.
Phone (480) 451-7440
www.techsol.org