PySQLLite Speed

[Kevin]

Hello All,

I wanted to thank Roger Binn for his email. He had
the answer to my issue with writing speed. It's
actual made an incredible change in the preformace. I
didn't have to go all the way to implementing the
synchronous mode(for my app). Previously, I was
insert one record at a time. The key was to write
them all at one time. I moved up to a 13 meg file and
wrote it to the db in secs. Now the issue is the 120
meg of RAM consumed by PyParse to read in a 13 meg
file. If anyone has thoughts on that, it would be
great. Otherwise, I will repost under a more specific
email.

Thanks,
Kevin



db.execute("begin")

while i < TriNum
db.execute("""insert into TABLE(V1_x)
values(%f),""" (data))
i = i + 1

db.execute("commit")





__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail

 

[Kent Johnson]

Hello All,

I wanted to thank Roger Binn for his email. He had
the answer to my issue with writing speed. It's
actual made an incredible change in the preformace. I
didn't have to go all the way to implementing the
synchronous mode(for my app). Previously, I was
insert one record at a time. The key was to write
them all at one time. I moved up to a 13 meg file and
wrote it to the db in secs. Now the issue is the 120
meg of RAM consumed by PyParse to read in a 13 meg
file. If anyone has thoughts on that, it would be
great. Otherwise, I will repost under a more specific
email.

If your data is (or can be) created by an iterator, you can use this recipe to group the data into
batches of whatever size you choose and write the individual batches to the db.
http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/303279

Kent

Thanks,
Kevin



db.execute("begin")

while i < TriNum
db.execute("""insert into TABLE(V1_x)
values(%f),""" (data))
i = i + 1

db.execute("commit")





__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail

 

[Gerhard Haering]

If your data is (or can be) created by an iterator, you can use this recipe
to group the data into batches of whatever size you choose and write the
individual batches to the db.
http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/303279

If your data is (or can be) created by an iterator, then you might find it
interesting that *pysqlite2*'s .executemany() not only works on lists, but also
on iterators.

Example:

import pysqlite2.dbapi2 as sqlite
...
# A generator function (which returns an iterator)
def gen():
for i in xrange(5):
yield (5, 'foo')

cu.executemany("insert into foo(x, y) values (?, ?)", gen())

So, in pysqlite2, .executemany() and iterators provide best
performance. .executemany() reuses the compiled SQL statement (so the
engine only needs to parse it once), and the iterator, if used
smartly, reduces the amount of memory used because you don't need to
construct large lists any more.

I hope I don't create too much confusion here ;-)

-- Gerhard

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFBsF3JdIO4ozGCH14RAkSHAJwNsXFjV5XRZsJpB4EvEk6/hMacUgCfa89R
87c2oH75fpIKZIO0PkvK++s=
=MSvj
-----END PGP SIGNATURE-----