Board index » delphi » Sub Query is Sooooooo Sloooooow (D2/W95/Paradox7)

Sub Query is Sooooooo Sloooooow (D2/W95/Paradox7)

I am creating a query to select the companies (customers) for which a certain user is any type of internal
contact (eg Sales, Support, Accounts etc)
The Company table has a primary key of CoID and the structure of the IntCont (Internal Contacts) table is:
ICID          Autonumber   Primary Key
ICFilePrefix  Alpha 2      ("CO" when linked to company table)
ICRecordFK    Integer      Foreign Key to CoID
ICType        Integer      Contact Type lookup
ICUserFK      Integer      Foreign Key to user table
This table has a secondary key on ICFilePrefix + ICRecordFK
and also a secondary key on ICFilePrefix + ICUserFK

Both tables are Paradox 7 on a local PC and contain approx 4000 records (currently 1 to 1, but other internal
contacts will be added later).
If I use a sub-query such as:
select * from company where CoID in (select ICRecordFK from IntCont where ICFilePrefix="CO" and ICUserFK=1 )

it works but takes 2mins30sec to 3mins30secs to run!

If I use a join such as:
select company.* from company, IntCont where ICFilePrefix="CO" and ICUserFK=1 and ICRecordFK=CoID
then the query takes between 1 and 6 seconds to execute.

What I'd like to know is why the sub-query takes so long (I guess it must be checking each of the 4000
companies against the 150 or so records returned by the sub-query)
and (more importantly) are there any ways to speed it up.

BTW I'd like to use the sub-query because the query can already be pretty complex (eg selecting from the
company, external contacts and company analysis tables)
and it's easy to add the sub-query line onto my sql statement.

TIA for any suggestions.


Re:Sub Query is Sooooooo Sloooooow (D2/W95/Paradox7)

Unfortunately, the BDE does not optimize queries in Paradox using the tables

The alternative is to make use of the SETRANGE method with a TTABLE.  It is
VERY FAST BUT, has some limitations and gotchas.

If you use SetRange, you have to set the ttable.indexname first and if you
later change the ttable.indexname, the "range" is lost.  Also, if you
setrange and the result is recordCount = 0, you need to EXIT the procedure
and CancelRange, because any code that attempts to access the table may
cause an error.

Other Threads