On 4/22/15 10:11 AM, John Doe wrote:
On Wednesday, April 22, 2015 at 2:46:19 PM UTC+1, Michael Bayer wrote:
I carefully re-read your first email, and now I see, that you are
trying
to write *TO* the database, yes? And you're comparing the speed of
writing *TO* to the speed of reading *FROM*?
Unfortunately, selecting data from some tables compared to inserting
data in those tables are two completely different activities. It is
not at all surprising to see a large difference in speed.
Of course. I do not expect that reading from will be as fast as
writing to the database.
However, 6 minutes to write 11MBs of data is ridiculous, because this
is the time it takes even when:
1) there are no text columns (so the nvarchjar(max) issue I mentioned
and I saw in other cases cannot apply)
2) there are only floating-point numbers
3) there are no primary keys, no indices and no constraints of any kind
I trust you'll concur it's hard to justify such a long time to
transfer such a small table even in the examples above.
I am not a DBA, but I cannot think of other reasons that would cause
such terrible speeds. Any thoughts would be more than welcome!
Maybe the to_sql method is committing to the database after inserting
every single row and this slows things down? Even so, 6 minutes for
11MBs means 31KB per second. My internet connection in the '90s was
faster than that :)
a COMMIT every time would slow it down, yes, but then in your profiling
you'd see the do_commit() method taking up that time as well.
if you can get your logging going, you'll see what SQL its emitting, and
you can also set up the formatting to have timestamps so you can measure
the time spent between queries.
Also, if you can, try out the pymssql driver, instead of pyodbc. pymssql
is much better maintained these days.
I have also been very surprised by the lack of info on the net. Either
I am the only one using the to_sql method, or I am the only one who
finds it so slow!
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
