Threaded Insert Records

[bernsoft]

I have a threaded application that i wrote a while back in Delphi and ADO. I purchased a copy of mydac std - latest. I would like to port this application to Delphi/mysql (am running sql 5).

The application processes many records and creates a thread for each record to insert details in table i.e its same as running a loop 1000 times and each time you create a thread to insert records. Below find the code that actually does the insert into my database.

In ADO, i have to use coinitialize.... and then connect to the db, make my insert and thats it.

How do i achieve this same thing when my application using mysql - do i use same connection for each thread? Please give me a code snippet that i can use quickly.

Also, anyone with a better way to do what i am trying to do below

My Code:

procedure Tinsert.Execute;
begin
{ Place thread code here }
Coinitialize(nil);

try
with TADOCOmmand.Create(nil) do
begin
//frmmain.cxMemo1.Lines.Add('Adding sms');
ConnectionString:=SQLSERVER CONNECTION STRING HERE; /
CommandText:='addnewrecord '+
QuotedStr(f_Destination)+','+
inttostr(f_DestTON)+','+
inttostr(f_DestNPI)+','+
QuotedStr(f_Originator)+','+
inttostr(f_OrigTON)+','+
inttostr(f_OrigNPI)+','+
QuotedStr(f_SMText)+','+
QuotedStr(GetEnumName(TypeInfo(TtcEncodingType), integer(f_Encoding)))+','+
QuotedStr(f_UserDataHeader)+','+
QuotedStr(f_OptionalParameters);
Execute;
Free;
end;
except

on E:Exception do
begin
frmmain.cxMemo1.Lines.Add('Database Error: ' + E.Message);
end;

end;
CoUninitialize;
end;

[jkuiper_them]

I just read in the FAQ that MyDAC is threadsafe. The only thing you have to do is create a connection to the database for every thread.

[bernsoft]

Thank you for your reply.

I have connectivity concerns (e.g if i have 5000 threads...does that mean 500 0 connections.... what does that do to performance.

I will try your method.

I am dissappointed with CRLAB that as a licensed customer, i have been waiting for so long to get a response from them - i also sent this to their sales and support email!

I expect better....for licensed customers.

[jkuiper_them]

Thank you for your reply.

I have connectivity concerns (e.g if i have 5000 threads...does that mean 500 0 connections.... what does that do to performance.

How do you manage that? It's a lot. A thread is a process witch is closed after the execution is terminated. The connection will be closed also. Don't you mean connections per desktop /pc?

I am dissappointed with CRLAB that as a licensed customer, i have been waiting for so long to get a response from them - i also sent this to their sales and support email!

I expect better....for licensed customers.

You're right about that. The support should response mutch better by email. Did you put your account /license number with it?

[Antaeus]

In MyDAC for each thread you should create separate TMyConnection object. To execute a command you can use the ExecSQL or ExecSQLEx method of TMyConnection. Then destroy the created TMyConnection object. In this case each command will be executed in the separate connection (one connection for one command).

If you want to reuse connections, you should enable pooling. In this case connections are put into pool on disconnect. If you need a new connection, it will be taken back from pool. Please see the following articles in MyDAC help for more information about pooling: "Using connection pooling", "TCustomDAConnection.Pooling", "TCustomDAConnection.PoolingOptions".

[Dimon]

You can noticeably increase the performance of your application if you don't create a thread for inserting each record. Only call the TMyCommand.Execute method in a loop.
Also you can use the TMyLoader component which serves for fast loading of data to the server.