Page 1 of 1
optimizing query
Posted: Wed 03 Dec 2008 11:30
by 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?
Re: optimizing query
Posted: Wed 03 Dec 2008 14:37
by 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;
Posted: Wed 03 Dec 2008 19:26
by jkuiper
But still there will be a lot of queries sent to the database (see dbmonitor)
Posted: Wed 03 Dec 2008 19:34
by eduardosic
jkuiper wrote: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?
Posted: Thu 04 Dec 2008 08:20
by 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.
Posted: Thu 04 Dec 2008 12:04
by 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.