I have some code similar to the simplified version shown below which is being used to retrieve data using a stored procedure.

     for i := 0 to FStoredProcedure.RecordCount - 1 do
     begin
        Fullname := FStoredProcedure.FieldByName('Fullname').AsString;
        Address := FStoredProcedure.FieldByName('Address').AsString;
        Suburb := FStoredProcedure.FieldByName('Suburb').AsString;
        State := FStoredProcedure.FieldByName('State').AsString;

        FStoredProcedure.Next;
     end;

There are over 6,000 records. It takes about 16 seconds to iterate through all of the records.

Does anyone know if there is a faster way to retrieve the data?  The stored procedure itself executes very quickly. It's the iterating through the records that takes time.

I have tried using .Fields[0].AsString, etc., as well as persistent fields, but they seem to take the same amount of time.

Thanks.

= Steve

Steve Gill wrote:

> I have tried using .Fields[0].AsString, etc., as well as persistent
> fields, but they seem to take the same amount of time.

That is what I was going to suggest - in my code that it time optimised
I do the FieldByName and get a (pointer) to the TField object, and then
refer to that directly. It used to be that it was faster.

But if that is not making it faster, I'd make some good time
measurements. If you can, I'd get
http://www.nexusdb.com/support/index.php?q=qualitysuite which looks
like it should do the business with LineTimer. (When they do resource
checking too I'll be buying it, but the alternative AQTime product is
very good but more expensive)

Otherwise, do things like timing the initial call, each read, and then
try dropping any special fields like blobs which may be needing a
special call to get the data. If that changes anything, then you can
consider if you need that data there and then.

Basically, instrumentation until you work it out!

--

Matthew Jones

Steve


Bit out of the box here. Have a look at LIST. If you can go this way you'll get 4 fields holding what are effectively stringlists of your data. Stuff the info into stringlists so you can actually do something with it and iterate down the stringlists. Should be a lot faster.

Roy Lambert

Steve

Forget my idea


Just tried it on my contacts database there's about 16k records

SELECT LIST(COALESCE(_Surname,''),#13),LIST(COALESCE(_Forename,''),#13),LIST(COALESCE(_Town,''),#13),LIST(COALESCE(_County,''),#13) FROM Contacts

vs

SELECT _Surname,_Forename,_Town,_County FROM Contacts


Q1 start: 23/04/2015 12:33:40
Q1 END: 23/04/2015 12:33:43
Q2 start: 23/04/2015 12:33:43
Q2 END: 23/04/2015 12:33:45

Q1 time: 3573  Q2 Time: 1170

SL Start: 23/04/2015 12:33:45
SL END: 23/04/2015 12:33:45
LOOP Start: 23/04/2015 12:33:45
LOOP END: 23/04/2015 12:33:45

SL time: 15  Loop Time: 125

OVERALL
Q1 & SL time: 3588  Q2 & Loop Time: 1295

I've used the fastest way of addressing fields - by their index, and a sensitive result set.

Other suggestions is to make sure the SP isn't hooked up to ANY visual controls - that will really slow it down. However, since you're taking 50% longer on less than half the data I suspect its what you're assigning to having an impact - either that or you have a slower machine, or both,


Roy Lambert


procedure TForm1.Button1Click(Sender: TObject);
var
t1, t2, t3, t4, t5, t6, t7, t8: integer;
surname, forename, town, county: string;
cntr: integer;
sl1, sl2, sl3, sl4: TStringList;
begin
memo1.lines.Clear;
memo1.lines.add('Q1 start: ' + Datetimetostr(Now));
t1 := gettickcount;
Qry1.Open;
t2 := gettickcount;
memo1.lines.add('Q1 END: ' + Datetimetostr(Now));
memo1.lines.add('Q2 start: ' + Datetimetostr(Now));
t3 := gettickcount;
Qry2.Open;
t4 := gettickcount;
memo1.lines.add('Q2 END: ' + Datetimetostr(Now));
Memo1.lines.Add('');
Memo1.lines.Add('Q1 time: ' + IntToStr(t2 - t1) + '  Q2 Time: ' + IntToStr(t4 - t3));
Memo1.lines.Add('');
memo1.lines.add('SL Start: ' + Datetimetostr(Now));
t5 := gettickcount;
sl1 := TStringList.Create;
sl2 := TStringList.Create;
sl3 := TStringList.Create;
sl4 := TStringList.Create;
sl1.Assign(qry1.Fields[0]);
sl2.Assign(qry1.Fields[1]);
sl3.Assign(qry1.Fields[2]);
sl4.Assign(qry1.Fields[3]);
for Cntr := 0 to sl1.Count - 2 do begin
 surname := sl1[Cntr];
 forename := sl2[Cntr];
 town := sl3[Cntr];
 County := sl4[cntr];
end;
sl1.Free;
sl2.Free;
sl3.Free;
sl4.Free;
t6 := gettickcount;
memo1.lines.add('SL END: ' + Datetimetostr(Now));
memo1.lines.add('LOOP Start: ' + Datetimetostr(Now));
t7 := gettickcount;
qry2.First;
while not qry2.Eof do begin
 surname := qry2.Fields[0].asString;
 forename := qry2.Fields[1].asString;
 town := qry2.Fields[2].asString;
 county := qry2.Fields[3].asString;
 qry2.Next;
end;
t8 := gettickcount;
memo1.lines.add('LOOP END: ' + Datetimetostr(Now));
Memo1.lines.add('');
Memo1.lines.Add('SL time: ' + IntToStr(t6 - t5) + '  Loop Time: ' + IntToStr(t8 - t7));

Memo1.lines.add('');
Memo1.lines.add('OVERALL');
Memo1.lines.Add('Q1 & SL time: ' + IntToStr((t2 - t1) + (t6 - t5)) + '  Q2 & Loop Time: ' + IntToStr((t4 - t3) + (t8 - t7)));
end;

You will see about a 20% improvement just by recompiling in XE8 due to the changes in how values are read and written in TField.

Steve Gill wrote:

I have some code similar to the simplified version shown below which is being used to retrieve data using a stored procedure.

     for i := 0 to FStoredProcedure.RecordCount - 1 do
     begin
        Fullname := FStoredProcedure.FieldByName('Fullname').AsString;
        Address := FStoredProcedure.FieldByName('Address').AsString;
        Suburb := FStoredProcedure.FieldByName('Suburb').AsString;
        State := FStoredProcedure.FieldByName('State').AsString;

        FStoredProcedure.Next;
     end;

There are over 6,000 records. It takes about 16 seconds to iterate through all of the records.

Does anyone know if there is a faster way to retrieve the data?  The stored procedure itself executes very quickly. It's the iterating through the records that takes time.

I have tried using .Fields[0].AsString, etc., as well as persistent fields, but they seem to take the same amount of time.

Thanks.

= Steve

Hi Steve,

Calling DisableControls before processing the loop will help some.

     FStoredProcedure.DisableControls;  <<<
     <your loop here>
     FStoredProcedure.EnableControls;  <<<


---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
http://www.atrex.com
---------------------------------------

>You will see about a 20% improvement just by recompiling in XE8 due to the changes in how values are read and written in TField.

That must be a first

Roy

Thanks for all of the suggestions. The stored procedure isn't connected to any visual controls. The stored procedure itself takes less than a second to execute.  It's the looping through the field values that takes all of the time.

I don't have XE8 yet so I can try that.  

I'll keep playing around with the code to see if I can speed it up.

Thanks again.

= Steve

Oops, that was meant to say "I don't have XE8 yet so I can't try that".

= Steve

Steve

>Thanks for all of the suggestions. The stored procedure isn't connected to any visual controls. The stored procedure itself takes less than a second to execute. It's the looping through the field values that takes all of the time.

Interesting. That's not what I found so it looks as though it could be whatever it is you're assigning to, or you can navigate a query much faster than an SP.

Out of interest what is it you're actually assigning to? It can't just be a simple string which is what I used - can it?

Also if you have the time try using a simple query rather than an SP

Roy Lambert