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
-~----------~----~----~----~------~----~------~--~---
