Speeding up records loading time.

Posted on 2002-05-15
Last Modified: 2010-04-04

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. :-)

Question by:varun_madhulika
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +5

Expert Comment

ID: 7012954
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).
LVL 27

Expert Comment

ID: 7013010
what are 5 lakhs ?

Expert Comment

ID: 7013020
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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 7013095
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 :-)

Expert Comment

ID: 7013224
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.


Expert Comment

ID: 7013669
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.
LVL 12

Expert Comment

ID: 7013977
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
LVL 12

Expert Comment

ID: 7013987
oh .. if you're manually populating the combobox then use

Expert Comment

ID: 7014012
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.

Author Comment

ID: 7015495
hi all

Thanks for your comments. But nothing seems to work.

madhulika. :-)

Expert Comment

ID: 7015558
do you mean that my way do not allow to speed up loading time?

Expert Comment

ID: 7015609
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.


Expert Comment

ID: 9005861
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

Accepted Solution

YensidMod earned 0 total points
ID: 9096785
Question is PAQ'd and no points refunded.

Community Support Moderator @Experts Exchange

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question