0

I have been experimenting with retrieving data from an MSSQL server from a Delphi program (using Delphi 2007). The program has so far been using TADODataset to retrieve data. During tests we found that TQuery with an ODBC provider fetched a large select of data much quicker. But on the other hand TADODataset preformed much better with parametrized+prepared selects. Both TADODataset and TQuery connect to the same MSSQL database table. For this experiment I used MSSQL Native Client as provider for both. TADODataset is set up with a connection string to the database, TQuery uses TDatabase with ODBC connection.

Here is some code from the experiment:

procedure TForm2.Button1Click(Sender: TObject);
begin
  ADODataSet1.CommandText := 'select top 100000 '+FDataFields+' from transactions';
  GetData(ADODataSet1);
  ReadData(ADODataset1);
  ADODataSet1.Close;
end;

procedure TForm2.Button2Click(Sender: TObject);
var
  I: Integer;
begin
  ADODataSet1.CommandText := 'select '+FDataFields+' from transactions where originaltransno = :FieldValue order by Key2';
  ADODataSet1.Parameters[0].Value := '000';
  ADODataSet1.Prepared := True;
  GetData(ADODataSet1);
  for I := 0 to 2000 do
  begin
    ADODataSet1.Parameters[0].Value := '123';
    //no match in DB, just a check if it is there
    ADODataSet1.Requery;
    ReadData(ADODataset1);
  end;
  ADODataSet1.Close;
  ADODataSet1.Prepared := False;
end;

procedure TForm2.Button3Click(Sender: TObject);
begin
  Query1.SQL.Clear;
  Query1.SQL.Add('select top 100000 '+FDataFields+' from transactions');
  GetData(Query1);
  ReadData(Query1);
  Query1.Close;
end;

procedure TForm2.Button4Click(Sender: TObject);
var
  I: Integer;
begin
  Query1.SQL.Clear;
  Query1.SQL.Add('select '+FDataFields+' from transactions where originaltransno = :FieldValue order by Key2');
  Query1.Params[0].Value := '000';
  Query1.Prepare;
  GetData(Query1);
  for I := 0 to 2000 do
  begin
    Query1.Params[0].Value := '123';
    Query1.Close;
    Query1.Open;
    ReadData(Query1);
  end;
  Query1.Close;
  Query1.UnPrepare;
end;

procedure TForm2.GetData(ADataSet: TDataSet);
begin
  ADataSet.DisableControls;
  ADataSet.Open;
end;

procedure TForm2.ReadData(ADataSet: TDataSet);
begin
  while not ADataSet.EOF do
  begin
    ReadLine(ADataSet);
    ADataSet.Next;
  end;
end;

function TForm2.ReadLine(ADataSet: TDataSet): string;
var
  I: Integer;
begin
  for I := 0 to ADataSet.Fields.Count - 1 do
  begin
    if ADataSet.FieldDefs[I].DataType = ftString then
      Result := Result + ADataSet.Fields[I].AsString;
  end;
end;

A rough timing (using CPU Time in task manager)

  • Button1 - TADODataset large select: 13 seconds
  • Button2 - TADODataset small selects: 7 seconds
  • Button3 - TQuery large select: 4 seconds
  • Button4 - TQuery small selects: 24 seconds

Futher testing indicated the slow time with large TADODataset select is from reading the fields AsString I tried replacing ReadLine:

function TForm2.ReadLine(ADataSet: TDataSet): int64;
var
  I: Integer;
begin
  for I := 0 to ADataSet.Fields.Count - 1 do
  begin
    if ADataSet.FieldDefs[I].DataType = ftInteger then
      Result := Result + ADataSet.Fields[I].AsInteger;
  end;
end;

With integer reading I got these timings:

  • Button1 - TADODataset large select: 3 seconds
  • Button3 - TQuery large select: 3 seconds

I tried using Value instead, but "adding" a Variant to string was much slower

Can I do anything in reading the strings that speed up TADODataset reading?

MGH
  • 1,189
  • 1
  • 10
  • 18
  • I suspect the problem is the table design. You are searching for a specific value in a column in query 4. Try running that same query in SSMS. My guess is that it will be slow because you are missing an index on originaltransno. – Sean Lange Aug 28 '15 at 14:01
  • @Seanlange if it was a missing index, why would it be quick with ODBC+TQuery access/ slow with ADO+TADODataset? It is the same database. Both OriginalTransNo and Key2 are indexed though. – MGH Aug 28 '15 at 14:06
  • I am not a delphi person, I am a sql server person, so I cant begin to help you with the delphi performance. I notice that in button 2 and 4 you have a loop in your code that goes through the entire resultset row by row. That loop is not in buttons 1 or 3. – Sean Lange Aug 28 '15 at 14:21
  • @SeanLange I do believe the performance issue is either in Delphi or ADO. The SQL queries are the same both for the slow and the quicker. 1 and 3 is measuring one thing, 2 and 4 is measuring another, so it is correct that two has a loop and two does not. – MGH Aug 28 '15 at 14:31
  • Have you tried monitoring what is getting sent to the server on behalf of your app, using ODBC and Ado using Sql Server's profiler? ODBC may be setting an optimization hint which constructs a temporary index. Btw, your ReadLine doesn't seem to initialize Result. – MartynA Aug 28 '15 at 14:47
  • Try make a speed test with dbExpress. – Jens Borrisholt Aug 28 '15 at 17:05
  • @JensBorrisholt I tried with dbExpress components TSQLConnection + TSQLDataset. It took 3 seconds for the large and 15 seconds for small ones. As with TQuery I had to use Open+Close instead of Requery for the small ones. – MGH Aug 31 '15 at 08:57
  • I found some components called AnyDAC and UniDAC that might help me. I cannot download AnyDAC trial, but UniDAC trial gave me good results; 3 second for large and less than a second for small ones. Going to experiments some more with that one. – MGH Aug 31 '15 at 13:19

0 Answers0