Re: [firebird-support] parrallel update - Email found in subject
OK, i confirm the parametized queries it's MUCH MUCH more fast than normal
query (up to 3x more fast!)
Was a little "hard" to understand the api to know how to use well the
parametized queries ...
i do component for that to make it more simple to use (delphi) and call
the query like
update(SQL, array of params);
now when i read this article
http://codicesoftware.blogspot.com/2008/04/fastest-way-to-insert-100k-registers.html
i thing a possibility to call update like in MySql
INSERT INTO testtable (iobjid, ifield0, ifield1) VALUES ({0}, {1}, {2}),
0, 30, 5, ({0}, {1}, {2}), 0, 30, 5 ...
can not be so bad as the problem of the parametized query is that they
need special components and it's could be good to run parametized query
from inside SQL text .
On 1/26/2012 11:19 PM, Leyne, Sean wrote:
>
> > > Or is this what you're already doing, just that your example was
> > > simplified?
> > >
> > > HTH,
> > > Set
> > >
> >
> > yes, of course parametized queries will be more fast, but i don't
> think they
> > will change the ratio in the test (it's will simply be more fast for
> both
> > variantes)
>
> I would disagree!
>
> Your test was skewed and did not reflected the reality of
> prepare/parameterized statements! (the 2 scenarios are "apples and
> oranges")
>
> By using un-prepared statements, you reduced the disk IO load of your
> test and thus the overhead of Classic server page synchronization. In
> essence your test created "openings" to allow for multiple disk IO to
> occur.
>
> Using prepared statements will increase the speed of a single
> connection but also increase the synchronization overhead and disk
> contention. Thus reducing the benefit of parallel connections.
>
> Sean
>
>
[Non-text portions of this message have been removed]
Re: [firebird-support] parrallel update - Email found in subject
ok, i will try the parametized queries to see ... thanks ! On 1/26/2012 11:19 PM, Leyne, Sean wrote: > > > > Or is this what you're already doing, just that your example was > > > simplified? > > > > > > HTH, > > > Set > > > > > > > yes, of course parametized queries will be more fast, but i don't > think they > > will change the ratio in the test (it's will simply be more fast for > both > > variantes) > > I would disagree! > > Your test was skewed and did not reflected the reality of > prepare/parameterized statements! (the 2 scenarios are "apples and > oranges") > > By using un-prepared statements, you reduced the disk IO load of your > test and thus the overhead of Classic server page synchronization. In > essence your test created "openings" to allow for multiple disk IO to > occur. > > Using prepared statements will increase the speed of a single > connection but also increase the synchronization overhead and disk > contention. Thus reducing the benefit of parallel connections. > > Sean > > [Non-text portions of this message have been removed]
RE: [firebird-support] parrallel update - Email found in subject
> > Or is this what you're already doing, just that your example was > > simplified? > > > > HTH, > > Set > > > > yes, of course parametized queries will be more fast, but i don't think they > will change the ratio in the test (it's will simply be more fast for both > variantes) I would disagree! Your test was skewed and did not reflected the reality of prepare/parameterized statements! (the 2 scenarios are "apples and oranges") By using un-prepared statements, you reduced the disk IO load of your test and thus the overhead of Classic server page synchronization. In essence your test created "openings" to allow for multiple disk IO to occur. Using prepared statements will increase the speed of a single connection but also increase the synchronization overhead and disk contention. Thus reducing the benefit of parallel connections. Sean
Re: [firebird-support] parrallel update
> Changing the entire sql statement 10 times like your example do
> takes a considerable amount of time. The easiest way for you to speed
> things up would be to use parameters, prepare once and execute 10
> times, i.e. something like (using IBO and Pascal since that is what I
> use):
>
> TIB_DSQL1.SQL.Add('insert Into Table_A(ID) VALUES(:MyParam)');
> TIB_DSQL1.Prepare;
> for I:=1 to 10 do
> begin
> TIB_DSQL1.Params[0].AsString:='<#randomchar>';
> TIB_DSQL1.Execute;
> end;
>
> Or is this what you're already doing, just that your example was
> simplified?
>
> HTH,
> Set
>
yes, of course parametized queries will be more fast, but i don't think
they will change the ratio in the test (it's will simply be more fast
for both variantes)
[Non-text portions of this message have been removed]
RE: [firebird-support] parrallel update
>**
>now i do in loop with one single thread, one connection :
>
>Start transaction
>insert Into Table_A(ID) VALUES('<#randomchar>'); //
>insert Into Table_A(ID) VALUES('<#randomchar>'); // (100 000 loop)
>insert Into Table_A(ID) VALUES('<#randomchar>'); //
>commit transaction
>
>average Insert time taken for each thread : 0.24 ms
>total time to insert 300 000 rec: 34.8 seconds
>
...
>now with 3 different Thread and 3 different connection
...
>average Insert time taken for each thread : 0.12 ms
>total time to insert 300 000 rec: 18.7 seconds
>
>so the parallel are 2 times more faster ! (i was hopping 3 times, but i do
>the test on a slow sata hard drive and a single processor computer that
>can explain)
>
>so at end the parrallel insert a much more faster (2x) than the
>sequential insert !
Changing the entire sql statement 10 times like your example do takes a
considerable amount of time. The easiest way for you to speed things up would
be to use parameters, prepare once and execute 10 times, i.e. something
like (using IBO and Pascal since that is what I use):
TIB_DSQL1.SQL.Add('insert Into Table_A(ID) VALUES(:MyParam)');
TIB_DSQL1.Prepare;
for I:=1 to 10 do
begin
TIB_DSQL1.Params[0].AsString:='<#randomchar>';
TIB_DSQL1.Execute;
end;
Or is this what you're already doing, just that your example was simplified?
HTH,
Set
Re: [firebird-support] parrallel update
dear ann,
> Sorry for the dumb question, but why do you want to do those
> operations in parallel? The SuperServer runs only one thread at a
> time, ties a connection to an thread, and uses only one processor. Do
> you think it will be faster interweaving n commands than running the
> operations sequentially? And of course, the classic architecture has
> the same general limitations, except there is a process per
> connection.
>
no dumb question at all ...
to answer you, i do this simple test
(on FB super classic, single processor):
Create 3 tables
Create table TABLE_A(ID VARCHAR(15));
Create table TABLE_B(ID VARCHAR(15));
Create table TABLE_C(ID VARCHAR(15));
**
now i do in loop with one single thread, one connection :
Start transaction
insert Into Table_A(ID) VALUES('<#randomchar>'); //
insert Into Table_A(ID) VALUES('<#randomchar>'); // (100 000 loop)
insert Into Table_A(ID) VALUES('<#randomchar>'); //
commit transaction
average Insert time taken for each thread : 0.24 ms
total time to insert 300 000 rec: 34.8 seconds
**
now with 3 different Thread and 3 different connection
thread1
Start transaction
insert Into Table_A(ID) VALUES('<#randomchar>'); (100 000 loop)
commit transaction
Thread2
Start transaction
insert Into Table_B(ID) VALUES('<#randomchar>'); (100 000 loop)
commit transaction
thread3
Start transaction
insert Into Table_C(ID) VALUES('<#randomchar>'); (100 000 loop)
commit transaction
average Insert time taken for each thread : 0.12 ms
total time to insert 300 000 rec: 18.7 seconds
so the parallel are 2 times more faster ! (i was hopping 3 times, but i do
the test on a slow sata hard drive and a single processor computer that
can explain)
so at end the parrallel insert a much more faster (2x) than the
sequential insert !
so i thing it's can be usefull that firebird permit us to send to him a
"batch
of sql" to do, and he will himself execute all the SQLs in parallel in
different
thread ?
thanks for all
stéphane
> >
> > is their any way to execute theses n update in parallele inside one
> > connection and one transaction ?
>
> No.
>
> Good luck,
>
> Ann
>
>
[Non-text portions of this message have been removed]
Re: [firebird-support] parrallel update
Stephane, > > I want to do simultaneous these update : > > Update table1 ... > Update table2 ... > etc... > Update tablen ... > > the table1, table2, ... tableN are not connected in any way > > now i can do these update in parallel using n connections, but > i would to avoid using n connection (mean start n transaction, etc...) Sorry for the dumb question, but why do you want to do those operations in parallel? The SuperServer runs only one thread at a time, ties a connection to an thread, and uses only one processor. Do you think it will be faster interweaving n commands than running the operations sequentially? And of course, the classic architecture has the same general limitations, except there is a process per connection. > > is their any way to execute theses n update in parallele inside one > connection and one transaction ? No. Good luck, Ann
