Hi,

> I know, that selecting 300K rows is not all right but users of the
> application generally do not know about SQL and other things and what
> is smart or not. They are always asked to enter a search criteria
> before the SELECT is issued, but sometimes they open the form with no
> search criteria and then the SELECT with no WHERE clause is used. And
> when they select ordering by some of the column, the query lasts long.
>
> > > I need to use LEFT JOIN and also order the result set according
> to
> > > LNAME.
> >
> > SELECT
> > PERSON.LNAME,
> > ( SELECT COMPANY.COM_NAME FROM COMPANY WHERE COMPANY.COM_ID =
> > PERSON.COM_ID )
> > FROM
> > PERSON

> Thanks, this is very fast, but there is one question: suppose that
> generally I select more than one field from table COMPANY, so the
> query would look like

I guess you didn't test with fetching all records, because it should have
the same speed when fetching all records.

> SELECT PERSON.LNAME,
> (SELECT COMPANY.COM_NAME FROM COMPANY WHERE
> COMPANY.COM_ID=PERSON.COM_ID),
> (SELECT COMPANY.COM_TOWN FROM COMPANY WHERE
> COMPANY.COM_ID=PERSON.COM_ID),
> (SELECT COMPANY.COM_STREET FROM COMPANY WHERE
> COMPANY.COM_ID=PERSON.COM_ID),
> (SELECT COMPANY.VAT_ID FROM COMPANY WHERE
> COMPANY.COM_ID=PERSON.COM_ID),
> (SELECT COMPANY.NOTE1 FROM COMPANY WHERE
> COMPANY.COM_ID=PERSON.COM_ID),
> (SELECT COMPANY.NOTE2 FROM COMPANY WHERE
> COMPANY.COM_ID=PERSON.COM_ID)
> /* ... and more COMPANY fields*/
> FROM PERSON
>
> this is not very good too, isn´t it?

No, this is more expensive if you're going to fetch all records. If you need
only the first 30 of the 300000 then it's fast. Better use the LEFT JOIN
option here.
Searching in a resultset with 300k records is for the user also not easy, so
can't you force at least some criteria?

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81