Solved

Speeding up records loading time.

Posted on 2002-05-15
14
166 Views
Last Modified: 2010-04-04
hi

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

madhulika
0
Comment
Question by:varun_madhulika
  • 3
  • 2
  • 2
  • +5
14 Comments
 
LVL 6

Expert Comment

by:swift99
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).
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7013010
what are 5 lakhs ?
0
 
LVL 7

Expert Comment

by:Motaz
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.

Motaz
0
 

Author Comment

by:varun_madhulika
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 :-)
0
 
LVL 9

Expert Comment

by:ITugay
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.

-------
Igor
0
 
LVL 6

Expert Comment

by:swift99
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.
0
 
LVL 12

Expert Comment

by:Lee_Nover
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
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 12

Expert Comment

by:Lee_Nover
ID: 7013987
oh .. if you're manually populating the combobox then use
Combo.Items.BeginUpdate;
try
  ...
finally
  Combo.Items.EndUpdate;
end;
0
 
LVL 6

Expert Comment

by:swift99
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.
0
 

Author Comment

by:varun_madhulika
ID: 7015495
hi all

Thanks for your comments. But nothing seems to work.

madhulika. :-)
0
 
LVL 9

Expert Comment

by:ITugay
ID: 7015558
do you mean that my way do not allow to speed up loading time?
0
 
LVL 7

Expert Comment

by:Motaz
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.

Motaz
0
 
LVL 1

Expert Comment

by:pnh73
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.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
Paul (pnh73)
EE Cleanup Volunteer
0
 

Accepted Solution

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

YensidMod
Community Support Moderator @Experts Exchange
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now