I want to optimize record searches in linked SQL tables.
I have 3 linked Interbase tables:
The tables are represented by TIBQuerys:
select * from Patients
select * from Folders
where PatID = :PatID
select * from Studies
where FldrID = :FldrID
Scrolling Patients forces scrolling in the linked tables:
with ibqFolders do begin
with ibqStudies do begin
This works well for manually scrolling thru the tables. However, doing a ibqPatients.Locate('PatID', myTargetID, ) causes ibqPatients to scroll thru the Patient table and as each patient record is accessed ibqFolders is forced in AfterScroll to be requeried. In turn ibqStudies is also requeried! On large tables this gets to be rather sloooow.
I think I should unhook the AfterScroll event handlers while doing the Locate. This would be followed by forcing the Folders and Studies tables to be requeried (probably by calling the afterscroll event handler for ibqPatients).
Is the best way to handle this? My proposal seems rather ugly & probably hard to maintain.
Can someone suggest a way to "unhook" the event handlers in code? I have considered setting a flag (fLocating) that is tested in the event handler.
The primary keys in the tables are created with Generators by Interbase. They are indexed. I thought Delphi's Dataset.Locate method used the available indexes to optimize the search. Thus I was surprised to see Locate causing ibqPatients to scroll sequentially thru the table to find the desired record! This, of course, contributes greatly to my problem. Any suggestions for a fix? This appears to be a bug. Or have I missed something?