optimizing query

[jkuiper]

I have a form witch send parameters to a table for printing.
Therefore I have to create a Query for each parameter. Something like this:

Code: Select all

INSERT INTO printtable VALUES
(printjob,'paramfield','paramvalue1');
INSERT INTO printtable VALUES
(printjob,'paramfield2','paramvalue2');

I prefer this to do it at once, because only one stream have to make to the database for better peformance:

Code: Select all

INSERT INTO printtable VALUES
(printjob,'paramvield1','paramvalue1'),
(printjob,'paramvield2','paramvalue2');

Is there a method in MyDAC who does it?

[eduardosic]

if you go to insert a several rows try this

Code: Select all

Set Autocommit=0;
INSERT INTO printtable VALUES (printjob,'paramfield','paramvalue1');
INSERT INTO printtable VALUES (printjob,'paramfield2','paramvalue2'); 
.....
.....
Commit;

[jkuiper]

But still there will be a lot of queries sent to the database (see dbmonitor)

[eduardosic]

But still there will be a lot of queries sent to the database (see dbmonitor)

I did not understand, you I can explain with more details?

[jkuiper]

Okay, very slowy.
1. Using TMyscript.
2. You have several records.
3. For each record there's a INSERT query.
4. Each query connects to the database for inserting. You can see it in the DBMonitor of devart.
5. If you have 5 records, each time a insert will be done, the client sends a network request to the server. If 50 people are sending 5 records, the network traffic is huge.
6. If you have 1 query for five records, there are less network traffic is less. Especially on 50 people.

[eduardosic]

you need to mount this SQL manually. ex:

Code: Select all

...............
var
 cSQL:String;
 nCount:Integer;

begin
   cSQL := ' ';
     
   for nCount := 1 to 100 do //generate 100 record's to insert
     cSQL := cSQL + '(' + QuotedStr( Edit1.Text ) + ',' + QuotedStr( Edit2.Text )  + '),';
   
   // delete last ','
   System.Delete( cSQL, Length(cSQL),1 ); 
   
   cSQL :=  'INSERT INTO `table1` VALUES ' + cSQL + ';';

   Query1.SQL.Text := 'BEGIN; ' + cSQL + ' COMMIT;';
   Query1.Execute;
end;

please, i don't test this code.. test it.