We help IT Professionals succeed at work.
Get Started

ClientDataset performance problem -- workaround/alternative(s) needed

dcfroehlich
dcfroehlich asked
on
14,366 Views
Last Modified: 2011-08-18
BACKGROUND:
I have written an engineering application that uses a ClientDataset with DBGrid and navigator components making the development process of easier and simpler (or "cleaner") in terms of both code and appearance on the screen.  Previously, I had been using my own scheme for storing the data sets, making use TurboPower's Orpheus data table to enter, edit, and view data.

The purpose of my application (or, more accurately, subcomponent of an application) is to manage several data sets consisting of terrain data points. The data sets can be manipulated by combining them in various ways to make new data sets. They are stored together in a single database file as nested data sets.
 
~~~~~~~~~~~~~~~~~~~~~~~~
INPUT DATA DESCRIPTION:
The raw "terrain" data are coordinate points obtained from ground surveys and saved in "point files" (using AutoCAD terminology) that come right out of survey instruments. The raw data files can have a variety of formats. Data for a "point" consists of at least three floating point numbers (X, Y, and Z coordinates), but may also include a unique point ID number (integer), and a point code (string). These files are in CSV format and frequently contain more than 200,000 coordinate points.
 
~~~~~~~~~~~~~~~~~~~~~~~~
PERFORMANCE PROBLEM:
Since the ClientDataset.LoadFromFile method only supports CDS and XML file formats, I am reading the CSV file and Inserting the data to the ClientDataset (see code snippet below).  After about 20k-30k Inserts, the performance slows down dramatically.  However, once the data have been inserted into the ClientDataset and saved to a file, in either CDS or XML format, the LoadFromFile only takes a couple of seconds.  So the length of time it takes to insert the CSV data into the ClientDataset more than offsets its convenience.

I am interested in finding a solution to the slow import of CSV files into a client data base.  For me, that would be the cleanest way of storing my data files, specifically by making use of nested data sets and storing them as binary (*.CDS) files.  I prefer saving the CDS in binary format because it makes it harder for people to fiddle with the data, outside of my application's control, if it is not in a text format.  As for preventing people from mucking up a text data file, they will do it if it is possible.  They can still alter a binary file, but if they do not understand what they see they seem to be less likely to touch it.
If there is no solution to the ClientDataset insert performance, I would like to know about alternative solutions.
My DB programming knowledge is scant and this is my first EE question.
 
~~~~~~~~~~~~~~~~~~~~~~~~
SAMPLE CODE (includes attempts to insert fields by name and by number):
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Factor := 100/(TerrainDataSet.PointList.Count - 1);
dlgProgressBar.Show;
ClientDataSet2.LogChanges := False;
with ClientDataSet2 do begin
// Open;
  LogChanges := False;
  for i := 0 to TerrainDataSet.PointList.Count - 1 do begin
    Application.ProcessMessages;
    if ((GetKeyState(VK_Escape) and 128) = 128) then begin
      if MessageDlg('Stop importing data?', mtConfirmation, [mbYes, mbNo], 0) = mrYes then Break;
    end;
    TP := TerrainDataSet.PointList[i];
//  Append;
//  FieldByName('Number').AsInteger := i;
//  FieldByName('X').AsFloat := TerrainPoint^.X;
//  FieldByName('Y').AsFloat := TerrainPoint^.Y;
//  FieldByName('Z').AsFloat := TerrainPoint^.Z;
//  FieldByName('Description').AsString := TerrainPoint^.Description;
    AppendRecord([TP^.Number,TP^.X,TP^.Y,TP^.Z,TP^.Description]);
    if (i mod 100 = 0) then dlgProgressBar.ProgressBar.Percent := Round(Factor*i);
 
    end;
end;
 
dlgProgressBar.Close;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Comment
Watch Question
All-around developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 2 Answers and 37 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE