[sqlalchemy] Re: Insert through ORM performance (was: Performance question)
CUT
thats because SA still does a lot of work on each batch of {params} to
check for defaults and also to process bind parameters. We might
look into optimizing some of the redundant work which occurs within
this process in 0.4, however as long as people still want their
unicodes converted to utf-8, their datetimes converted to strings on
sqlite, their binaries correctly massaged, their Python side defaults
to fire off, this overhead will still be present for those types.
So, if you truly want DBAPI-speed inserts, use the raw connection:
engine.connect().connection.executemany(your statement, [{params1},
{params2}, {params3}, ...])
This only for 0.4 version.. i think
is this the correct methods for the 0.3 ?
engine.connect()._executemany()
Thank's
Glauco
--
++
Glauco Uri - Programmatore
glauco(at)allevatori.com
Sfera Carta Software(r) [EMAIL PROTECTED]
Via Bazzanese,69 Casalecchio di Reno(BO) - Tel. 051591054
++
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Insert through ORM performance (was: Performance question)
So, if you truly want DBAPI-speed inserts, use the raw connection:
engine.connect().connection.executemany(your statement, [{params1},
{params2}, {params3}, ...])
Wow.. after some try i found this is enought fast for me..
engine.connect().execute(sql,list_qry_params )
direct execution of a precompiled sql throw a list of 3000 dict is
faster about 12 times!!
Main interesting feature is occupation of CPU and memory .. it is lesser
than an half of previous method ( n x single row insertion )
thank's Michael for your help
Glauco
--
++
Glauco Uri - Programmatore
glauco(at)allevatori.com
Sfera Carta Software(r) [EMAIL PROTECTED]
Via Bazzanese,69 Casalecchio di Reno(BO) - Tel. 051591054
++
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Insert through ORM performance (was: Performance question)
On 7/19/07, Mike Orr [EMAIL PROTECTED] wrote: I'm amazed at the speed of mysqldump and its reloading. It packs a bunch of rows into one INSERT statement. I don't see why that's so much faster than than executemany but it provides another potential avenue for speed. I'm not sure if MySQL is the only engine that does this. Here's an exhaustive set of timings on the options you have with postgresql: http://www.depesz.com/index.php/2007/07/05/how-to-insert-data-to-database-as-fast-as-possible/ summary: COPY is fastest; that is what pg_dump uses. multi-row inserts in transactions got within a factor of 3. naive single-row inserts were 30x slower than COPY. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Insert through ORM performance (was: Performance question)
I will comment that DBAPI has no begin() method. when you use DBAPI
with autocommit=False, youre in a transaction - always. SQLAlchemy
defines a transaction abstraction on top of this that pretends to
have a begin. Its when theres *not* a sqlalchemy transaction going
on that youll see a COMMIT issued after every insert/update/delete;
otherwise youre transactional.
Anyway, if the email is talking about batched inserts of this type
being slow (i.e. non-ORM inserts):
table.insert().execute({params1}, {params2}, {params3}, )
thats because SA still does a lot of work on each batch of {params} to
check for defaults and also to process bind parameters. We might
look into optimizing some of the redundant work which occurs within
this process in 0.4, however as long as people still want their
unicodes converted to utf-8, their datetimes converted to strings on
sqlite, their binaries correctly massaged, their Python side defaults
to fire off, this overhead will still be present for those types.
So, if you truly want DBAPI-speed inserts, use the raw connection:
engine.connect().connection.executemany(your statement, [{params1},
{params2}, {params3}, ...])
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Insert through ORM performance (was: Performance question)
Andreas Kostyrka wrote:
Correctly and quickly loading data is strongly depending upon the DB.
E.g. For PostgreSQL you can achieve a magnitude of speedup by using COPY
FROM STDIN;
But the kinds hacks are out of scope for sqlalchemy.
On 7/19/07, Michael Bayer [EMAIL PROTECTED] wrote:
Anyway, if the email is talking about batched inserts of this type
being slow (i.e. non-ORM inserts):
table.insert().execute({params1}, {params2}, {params3}, )
thats because SA still does a lot of work on each batch of {params} to
check for defaults and also to process bind parameters. We might
look into optimizing some of the redundant work which occurs within
this process in 0.4, however as long as people still want their
unicodes converted to utf-8, their datetimes converted to strings on
sqlite, their binaries correctly massaged, their Python side defaults
to fire off, this overhead will still be present for those types.
Andreas is pointing out that bulk inserts are intrinsically slow in
some database engines, which adds an additional level of overhead that
SQLAAlchemy has no control over. MySQL suggests LOAD DATA INFILE
... for these situations, to read data from a tab-delimited or CSV
file (with SELECT INTO OUTFILE ... for writing). PostgreSQL has the
equivalent but with different syntax.Unfortunately that means
putting the data in still *another* format which may have quirks, and
it will have to be an encoded bytestring rather than Unicode.
Perhaps SQLAlchemy could add a side feature to load/save data in this
manner, to smooth out the differences between engines. But I'm not
sure that's worth much effort. To do it with SQLAlchemy now you can
create a raw SQL string with the full path of the file to be
read/written.
I'm amazed at the speed of mysqldump and its reloading. It packs a
bunch of rows into one INSERT statement. I don't see why that's so
much faster than than executemany but it provides another potential
avenue for speed. I'm not sure if MySQL is the only engine that does
this.
--
Mike Orr [EMAIL PROTECTED]
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
