Speed Issues With ADO

[cascot]

My problem is the time it seems to take to complete a batch update of 126 records to a Microsoft Access 2000 database connected using OLEDB 4.0. I am using the ADOTable component in Delphi 7.0

I have written code to extract data from an HTML file. My development test file involves extracting 126 instances of the data. Below I have removed the code used to extract the data but left the code I use within the loop to write each of the 126 records to the disconnected recordset. The cummulative time taken to write these 126 records is almost too short to measure, varying between 10 and 70 milliseconds. However once the extraction is completed and I do the BatchUpdate, that operation takes between 3700 and 4200 milliseconds!! That seems slow to me. Is that what I should expect? Is there anything I am doing incorrectly in the code I have included below, or are there any settings I should check related to the ADOConnection, etc, which may speed things up significantly?

ADOTable1.Active:=True;

while pos1 > 0 do begin
[Parsing code removed]
ADOTable1.Insert;
ADOTable1.FieldByName('BFEventId').Value :=
CurrentBFEventId;
ADOTable1.FieldByName('AmountMatched').Value :=
AmountMatched;
ADOTable1.FieldByName('BFRunnerID').Value := RunnerID;
ADOTable1.Post;
end;

ADOConnection1.Connected:=True;
ADOTable1.UpdateBatch(arAll);

 

[whosrdaddy]

setting up the ADOconnection takes time.
why not set it always active?

I see also that you are using the Value property, try using the correct fieldtype like .AsInteger or .AsString. this is faster since you don't have variant conversion, which is slow.

supposing the BFEventId field is integer you can do this :

ADOTable1.FieldByName('BFEventId').AsInteger :=
    CurrentBFEventId;

* small hint, if you post code here, use TGML.
like this :

[ignore]

Your delphi code

[/ignore]

--------------------------------------
What You See Is What You Get

 

[cascot]

Thanks for your reply whosrdaddy.

Thank you also for the pointer on the ADOConnection, though in this particular instance that can't be the cause as the only line of code I have between my StartTime and EndTime lines of code I use to obtain the time of (roughly) 4 seconds is:

ADOTable1.UpdateBatch(arAll)

So any time taken to initiate the connection is excluded.

I will also implement the code you mention to avoid type conversions, though that section of the code is quite fast already - about 0.4s to extract 17 values for 126 instances (so 2142 values) from the HTML file as well as write them to the disconnected recordset.

I'd be very interested in any other factors you think may be contributing to the speed of the batch update.