-1

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.

Community
  • 1
  • 1
user3313541
  • 65
  • 6

1 Answers1

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.

Ken White
  • 123,280
  • 14
  • 225
  • 444