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.

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.


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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month9 days, 23 hours left to enroll

624 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