Speeding up records loading time.


iam using interbase with delphi 5.0.  My problem is that in one of my table i have over 5 lakhs of records which iam showing in a drop down. when I populate this combo box it takes more than 50 sec.

Is there any way to speed it up.

I need it urgently. thanks. :-)

Who is Participating?
YensidModConnect With a Mentor Commented:
Question is PAQ'd and no points refunded.

Community Support Moderator @Experts Exchange
First: Identify where the bottlenecks are.  
* How much time is spent in Interbase?  
* How much time is spent in network communication?  
* How much time is spent in the VCL?

Here is how I resolved a similar problem.  We have a large number of sites communicate with our database via 56k frame relay, and a growing number that pull down somewhere in the neighbourhood of 3MB of data to populate drop down menus.  The math says that it takes a minimum of 30 minutes to get this data across the WAN.  Empirical testing shows that it takes 3 minutes to retrieve the data from the database and between 35 and 75 minutes to transmit it.  Once the transmission is complete, the drop down's operate very quickly (almost instant).

We load the data into a TClientDataset that remains in memory for the duration of the application session.  That copy and any changes made to it are persisted locally to a database.  The next time the application is started, the client dataset is initially loaded from the local copy and a background thread process is kicked off to fetch only the data that has changed on the master database and update the client dataset..

To our end users, the application takes about 30 seconds to load and then everything operates instantaneoulsly.  What they don't see is that their communications line may be going all out for the first hour and a half minutes (although it is typically far less since we only transmit the changes).
what are 5 lakhs ?
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

How did you populate the combo box, for example if you are using query please post it here, and post the table structure, indexes, and current records count.

varun_madhulikaAuthor Commented:
hi swift99

I can't load all the records in the memory also because then it will make my application slow more ever it will take time when it will laod for the first time.

madhulika :-)
hi all,

hi meikl :-)

looking at "50 sec" I suppose  that lakhs = million.

By the way, it is not good idea to populate all data into combobox, then it whill be difficult to user browse to necessary record. Right way is to let user type some first characters in combobox and then OnDropDown event use typed string as filter to extract only suitable records from the table.

We are using DB2 static SQL via direct connection to the IBM CLI drivers.  The wrapper code is proprietary, but the principle is not - kick off the DB engine, wait for the response, then take the result structure and feed the data into a TClientDataset.

The table record count is about 1.5 million, with a maximum of 12,000 records being loaded to any one site.

The query is a 14 way join to fetch only the data required by a single site, but to fetch it all at one go.  It's approximate structure is:

select fields
from HomeDatabase.table1, HomeDatabase.table2, HomeDatabase.table3, HomeDatabase.table4, HomeDatabase.etcetera
where various conditions are true
and Last_Update_Timestamp > :Last_Cached_Timestamp

From this you can see that the update requires that the local cache is queried first

select max (Last_Update_Timestamp) as Last_Cached_Timestamp
from CacheDatabase.Table

The first launch of the application requires the full transmission time unless we pre-populate the cache file at installation time, but that's the price of wanting all of that data in a GUI component.  Initial transmission time can be cut by generating the initial load of the cache file at the home site and then pushing it out as a zip file, but we haven't been that ambitious yet.

ITugay: You are very right about the filtering.  We use exactly the filtering technique you suggest because any drop down with more than a dozen entries is useless.  I had presumed (wrongly) that madhulika would already be doing that.
ummm that's really bad GUI design having so many recods in the combobox
you go search for the right entry .. loads of fun when you have 1 mil of records :)

anyway before doing any lengthy operations on datasets you should call DataSet.DisableControls and then EnableControls when you're done
but this is still a bad database as well as gui design
you should collect only the data you need
if you did this with a grid (can be only one column) .. you could smart load the needed records ..
reduce the db traffic and ofcourse the loading time
oh .. if you're manually populating the combobox then use
Rather than using Dataset.Disablecontrols, clone the dataset.  The typical Dataset is actually an interface to an underlying non-Delphi library with its own buffers.  Cloning the dataset leaves your GUI cursor alone while you traverse the data with another cursor.  Dispose of the clone at the end of the operation.

This helps to eliminate accidental event loops.
varun_madhulikaAuthor Commented:
hi all

Thanks for your comments. But nothing seems to work.

madhulika. :-)
do you mean that my way do not allow to speed up loading time?
You can use caching technieques, for example, for first time the user open the client application, it requests data from the table and populate the combo box, then you can save it in a text file, for the second times you can check if this file exists then load it from the file instead of reading it from database, also you can make expiration date for the file cache, if it becomes older than day you can remove it and load another fresh copy from the database.

btw. Did you try Igor's comment, about letting the user type the begining of the words and you run a query as auto-complete, the same technique that used by Delphi 6 IDE, try to write Object or component name then type the dot (.) then type letters, you will find that selections will be narrowed while you continue type letters.

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ and No Refund

Please leave any comments here within the next seven days.
Paul (pnh73)
EE Cleanup Volunteer
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.