Handling Huge data from TAdoQuery in delphi

Question

Programming Language: Delphi 6

SQL Server in The Back end.

Problem:

Application used to hit DB each time we needed something , Ended up hitting it for more than 2000 times for getting certain things, Caused problems with application being slow. This hitting DB happened for a lot of tables each having different structure and different number of columns. So I’m trying to reduce the number of calls. We can have about 4000 records at a time from each table.

Proposed Solution:

Let’s get all the data from DB at once and use it when we need it so we don’t have to keep hitting the DB.

How Solution is turning out so far:

This version of Delphi doesn’t have a dictionary. So we already have an implementation of a dictionary from String List (Let us assume that implementation is good).

Solution 1:

Store this in a dictionary that we created with: A unique field as a key. And Add rest of the data as strings in String List separated like this: FiledName1:FileValue,FieldName2:FieldValue2,….. Might Have to create about 2000 String List to map data to key. I took a look at the following link: How Should I Implement a Huge but Simple Indexed StringList in Delphi? Looks like they could move to a different DB not possible with me. Is this a sane solution?

Solution 2:

Store this in a dictionary with List. That list will have Delphi Records. Records can’t directly be added to so I took a look at this Link: Delphi TList of records

Solution 3:

Or Given that I’m using TAdoQuery should I use Seek or locate to find my records. Please advice on the best way to do this?

Requirements:

Need Random Access of this data. Insertion of data will happened only once when we get all the data , per table as we require them.

Need to only read the data , don’t have to modify it.

Constantly need to search in terms of primary key.

In addition to changing the application we have already done good indexing on DB to take care of things from DB side. This is more to make things run well from the application.

Thanks I'm from C# background .Using TClientDataset was all that I needed.Simple but didn't know where to look. — user3313541
– user3313541, Commented Feb 15, 2014 at 22:30
You don't need a TClientDataset. ADO data sets has the ability to work in disconnected/batch mode. — kobik
– kobik, Commented Feb 16, 2014 at 17:20
Thanks Yeah started looking into it yesterday. TAdoDataSets also seems to do the job. — user3313541
– user3313541, Commented Feb 16, 2014 at 23:43

Ken White · Accepted Answer · 2014-02-15 22:37:32Z

1

This sounds like a perfect use case for TClientDataSet. It's an in-memory dataset that be indexed, filtered, and searched easily, hold any information you can retrieve from the database using a SQL statement, and it has pretty good performance over a few thousand reasonable-sized rows of data. (The link above is to the current documentation, as I don't have one available for the Delphi 6 docs. They should be very similar, although I don't recall which specific version added the ability to directly include MidasLib in your uses clause to eliminate distributing Midas.dll with your app.)

Carey Jensen wrote a series of articles about it a few years back that you might find useful. The first one can be found in A ClientDataset in Every Database Application - the others in the series are linked from it.

answered Feb 15, 2014 at 22:37

Ken White

126k15 gold badges237 silver badges476 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Handling Huge data from TAdoQuery in delphi

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related