Paradox7)

David Carle

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

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.

Russ Rees

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

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

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

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

1. SQL problem - joins plus sub query (Paradox7)

2. Update query with sub-query

3. D2/Paradox7/Disappearing records

4. Internal Lookup D2 & Paradox7

5. D2 on peer to peer (W95) - Network Initialisation Failed

6. Control sound/volume in W95 (d2)?

7. Q:Deleting Files in W95 Recycle Bin (D2)

8. D1/D2 Lock Volume W95

9. Fun w/PageCtrl and TabSheets in D2/W95

10. Problem with D2 and W95.