How to handle slow loading of data into TClientDataSet in Delphi

Ok, thanks, again the idea of a separate thread may be very useful. Any coding sample? I am not quite in place with threads. However, about your initial reservations, now my code simply uses ACTIVE := TRUE to load, without any SQL statements or filters. Anything better?

I believe PacketRecords can help you to solve your problem; I haven't tried with such bigger table so can't say what will be the effectiveness.

Example I plucked out of a bigger app that did threadded sql calls.

type
  TSQLThread = class(TThread)
  private
  protected
    procedure Execute; override;
  public
    ConnStr : WideString;
    SQLString : WideString;
    IsNotice : Boolean;
    IsSelect : Boolean;
    sIRCDest : String;
    Output : TIdIRC;
    Priority: TThreadPriority;
  end;
 
function RunThread(ConnectionString: WideString; SQLString: WideString; Output: TIdIRC; isNotice: Boolean; isSelect: Boolean; sIRCDest: String; Priority: TThreadPriority): TSQLThread;
 
implementation
 
uses Main;
 
function RunThread(ConnectionString: WideString; SQLString: WideString; Output: TIdIRC; isNotice: Boolean; isSelect: Boolean; sIRCDest: String; Priority: TThreadPriority): TSQLThread;
var
  SQLThread : TSQLThread;
begin
  SQLThread := TSQLThread.Create(True) ;
  SQLThread.FreeOnTerminate := True;
  SQLThread.ConnStr := ConnectionString;
  SQLThread.SQLString := SQLString;
  SQLThread.Output := Output;
  SQLThread.IsSelect := IsSelect;
  SQLThread.isNotice := isNotice;
  SQLThread.sIRCDest := sIRCDest;
  SQLThread.Priority := Priority;
  SQLThread.Resume;
  Result := SQLThread;
end;
 
procedure TSQLThread.Execute;
var
  Qry : TADOQuery;
  k : integer;
  Temp : String;
begin
  inherited;
  CoInitialize(nil);
  Qry := TADOQuery.Create(nil);
  try
    Qry.ConnectionString := ConnStr;
    Qry.CursorLocation := clUseServer;
    Qry.LockType := ltReadOnly;
    Qry.CursorType := ctOpenForwardOnly;
    Qry.SQL.Text := SQLString;
    Qry.Open;
    If IsSelect Then
      begin
      If IsNotice Then
        begin
        Output.Notice(sIRCDest,'Query Executed.');
      end
      Else
        begin
        Output.Say(sIRCDest,'Query Executed.');
      end;
    end
    Else
      begin
      while NOT Qry.Eof and NOT Terminated do
        begin
        Temp := '';
        For K := 0 To Qry.FieldCount - 1 Do
          begin
          Temp := Temp + '[' + Qry.Fields[K].FieldName + ': ' + Qry.Fields[K].AsString + '] ';
        end;
        If IsNotice Then
          begin
          Output.Notice(sIRCDest,Temp);
        end
        Else
          begin
          Output.Say(sIRCDest,Temp);
        end;
        Qry.Next;
      end;
    end;
    Qry.Free;
  except
    If IsNotice Then
      begin
      Output.Notice(sIRCDest,'Query Failed.');
    end
    Else
      begin
      Output.Say(sIRCDest,'Query Failed.');
    end;
    Qry.Free;
  end;
  CoUninitialize() ;
end;

Select allOpen in new window

>>now my code simply uses ACTIVE := TRUE to load,
are you doing this on table component? if so then look at your code and see if all records are required if then answer is no then get rid of table component and use sql query component with simple sql statement select list of required fields from table_name

>>idea of a separate thread may be very useful. Any coding sample?
it depends on how you app is built, so I need some more info to post code

ziolko.

Thanks guys, anyway, to Ziolko (and atul_parmar partially)

>>now my code simply uses ACTIVE := TRUE to load,
You asked: are you doing this on table component? if so then look at your code and see if all records are required if then answer is no then get rid of table component and use sql query component with simple sql statement select list of required fields from table_name
This is a good fundamental idea, thanks for reminding me, but, if I end up loading from a table compoent, as I do now, is it then not possible to have a "select" or filter statement on loading. It seems from this fine article: http://www.podgoretsky.com/ftp/Docs/Delphi/D5/dg/5_ds3.html that ClientDataSets only have properties from batch loads according to the number or records with PacketRecords. May I specified another filter than "number of records", like a proper filter?

Just an addtion: I mean filter on loading, not on the already loaded dataset

>>May I specified another filter than "number of records", like a proper filter?
there ae properties .Filter and .Filtered in clientdataset which you can use (described in same article: http://www.podgoretsky.com/ftp/Docs/Delphi/D5/dg/5_datset.html#3835) but personally I always use SQL statements to get what I want.

other adavantage of using "select field_list from..." instead of "select *" or table component are indexes on server side when you use table component or "select *" indexes defined on server side will not be used (unless of course there's full covering index on table but this is rare situation).

ziolko.

Thanks ziolko, I think I will reengineer to use a SQL components, rather tables here, but just to be syre understand correctly: the filters refered to is then applied to the table component itself before the clientdataset becomes active, it is not something you can specify as a property for the clientdataset, only to load according to a specified filter, right?

filters are applied to dataset not to database, clientdataset is independent from database engine so it would be to apply filter to db engine directly as clientdataset "doesn't know" which engine is used and which sql dialect it uses

ziolko.

related question link:
https://www.experts-exchange.com/questions/23299349/Looking-for-a-simple-solution-to-port-database-application-to-an-SQL-multi-user-environment.html

I advised you to limit the number of rows you get from the SQL Server (related by user area).  Does this mean that each user is responsible for one million+ rows?!?

What is the TClientDataset's .Capacity property value?

Have you disconnected the TClientDataset from its bound controls while you are loading?

Hi aikimark:

About the SQL etc.:
Perhaps it would help me to relate that the 1 mio.+ table is a detail table and that the user is only responsible for max 10,000 records from the detail table at any given point in time, and that I am only loading all right now for purpose of simplicity - because it works well, except for loading time ;-). I may have to change it, load from query repeatedly as user changes masterrecord control, that much slowly dawns on me. But does the detail table thing gives a clue about any alternative options.

You asked: What is the TClientDataset's .Capacity property value?
What is this property? I don't know the value just now.

You asked: Have you disconnected the TClientDataset from its bound controls while you are loading?
Yes, controls are created on the fly, i.e. not created on loading dataset

Make the SQL change first (you will need to add some condition into your WHERE clause, most likely parameterized).  Let use know about the post change performance.

Hi again,

Ok, did the SQL change, nice to get rid of all those tables, speed is faster too, but still, I need to load limited datasets on the fly, for sure.

Problem: A related big problem, that's spending my day: it seems that the clientdataset's fieldnames are empty and perhaps that's why I cannot set MasterFields (error: Field index out of range), when I do something like this:

with blabla
    IndexFieldNames := 'Profile ID';
    MasterFields := 'ID'; <- Error: Field index out of range (checked fieldcount, ok, but all fields[i].names are empty, is that why - and why are they empty???
    MasterSource := whatever;

Additional question: As I understand, the provider attaches itself to the data, not the executed query, right? I mean, I am reusing the same query for multiple providers, that's ok, right?

@blarke

You've stumped me.  I don't know what kind of 'plumbing' you are using to get data from your SQL Server databse, what kind of query mechanism(s) you are using, or multi-table configurations/relationships.

Based on your earlier remarks, you should have experienced a load time 1/100 the original load time.  Is that about right?

Well, I am afraid I didn't record the exact improvement, but currently with 150,000 records it takes about 3 minutes to load that particular query into the clientdataset. Before it certainly took longer, enough to go and have a bit of lunch.

And, hey, it's just that I am not really proud of my plumbing, but here is the particular bit (unedited), that simply loads MSSQL tables and views into an array of clientdatasets. Please notice, that I am trying to go simple on this, just wanting to load all data into datasets on application load.

procedure Tdm.WithDBTables(VsDB: String; ViAction: Integer);
var
  slTableNames: TStringList;
  i, j: Integer;
  sTrimmedTableName: String;
begin
  slTableNames := TStringList.Create;
  GetDataBase(VsDB).Open;
   slTableNames := GetUserTables(VsDB);
  SetLength(dspArray,slTableNames.Count);
  SetLength(cdsArray,Length(dspArray));
  SetLength(srcArray,Length(dspArray));
  for i:=0 to slTableNames.Count-1 do
  begin
    ExecuteSQL(VsDB,'SELECT * FROM [' + slTableNames[i] + ']');
    sTrimmedTableName := TrimBlanks(slTableNames[i]);
    with GetDataBaseQuery(VsDB) do
    Case ViAction of
      CiOpen:
        begin
          Open;
          dspArray[i] := TDataSetProvider.Create(Self);
          cdsArray[i] := TClientDataSet.Create(Self);
          srcArray[i] := TDataSource.Create(Self);
          dspArray[i].Name := 'dsp' + sTrimmedTableName;
          cdsArray[i].Name := 'tbl' + sTrimmedTableName;
          srcArray[i].Name := 'src' + sTrimmedTableName;
          dspArray[i].DataSet := GetDataBaseQuery(VsDB);
          cdsArray[i].ProviderName := dspArray[i].Name;
          srcArray[i].DataSet := cdsArray[i];
           cdsArray[i].Active := True;
//          for j := 0 to cdsArray[i].FieldCount-1 do
//            OkMessage(cdsArray[i].Fields[j].Name); - just testing here, this one shows only empty
          Close;
        end;

etc...

Currently my problem is that I am not allowed to set master-child relationships, but I don't really know what to tell about the existing infrastructure (MSSQL, TADOConnection, TADOQUERY - no longer any tables, only queries and datasets).

* "150,000 records" <> "10,000 records"

* I still don't see where you are limiting your rows.

* Although you want to keep this simple, the code certainly does a LOT of stuff.

* You should record the time it takes to do certain stuff.

* Not sure what you are doing with this encapsulation:
with GetDataBaseQuery(VsDB) do
...
    dspArray[i].DataSet := GetDataBaseQuery(VsDB);

* You aren't reusing any of your data objects.

* How many tables are you retrieving data from?

Aikimark: yes, the 150,000 records is the current gross no of recs, soon going in excess of 1 mio+, 10,000 records is the relevant dataset, not limited yet. Still just trying to get it running the old way first. Afterwards, I'll need to find a way to handle the particular detail dataset differently from other datasets.

Connections and query are created earlier to this procedure, GetDataBaseQuery(VsDB) simply returns the component as TAdoQuery - using findcomponent on string (VsDB) , where VsDB is the Connection name, e.g. 'dbCDB' - as I am currently handling 3 connections (one local, two remote).

About reusing, true, I know going in array is kind of crazy, but its somehow intuitive to me - and what's reusable? I still - to begin with - just want to open all the relevant datasets - then I need a dataprovider, clientdataset, and datasource for each table, right? Later the query will limit to User IDs - so the user will only load the dataset he owns, plus what you could call public records, which should be readonly. Don't know how to handle that yet though.

I am retrieving about 50 tables and a couple of static views from 3 connections. That works! After that I want to master-child relationships. That works not! What happens to those field names - or ...?

Here's some reading on the subject:
http://delphi.about.com/od/database/l/aa071001a.htm

ADO master/detail setup from efg: http://efg2.com/Lab/Library/Delphi/ADO/MasterDetail/index.html#Setup

walk-through steps:
http://www.prestwood.com/ASPSuite/KB/document_view.asp?qid=100080

excellent Cary Jensen article:
http://dn.codegear.com/article/29825