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("beg in")

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

db.execute("com mit")





_______________ _______________ ____
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.

http://promotions.yahoo.com/new_mail

[Kent Johnson]

Re: PySQLLite Speed

Kevin wrote:[color=blue]
> 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.[/color]

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
[color=blue]
>
> Thanks,
> Kevin
>
>
>
> db.execute("beg in")
>
> while i < TriNum
> db.execute("""i nsert into TABLE(V1_x)
> values(%f),""" (data[i]))
> i = i + 1
>
> db.execute("com mit")
>
>
>
>
>
> _______________ _______________ ____
> Do you Yahoo!?
> Yahoo! Mail - You care about security. So do we.
> http://promotions.yahoo.com/new_mail[/color]

[Kent Johnson]

Re: PySQLLite Speed

Kevin wrote:[color=blue]
> 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.[/color]

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
[color=blue]
>
> Thanks,
> Kevin
>
>
>
> db.execute("beg in")
>
> while i < TriNum
> db.execute("""i nsert into TABLE(V1_x)
> values(%f),""" (data[i]))
> i = i + 1
>
> db.execute("com mit")
>
>
>
>
>
> _______________ _______________ ____
> Do you Yahoo!?
> Yahoo! Mail - You care about security. So do we.
> http://promotions.yahoo.com/new_mail[/color]

[Gerhard Haering]

Re: PySQLLite Speed

On Fri, Dec 03, 2004 at 06:06:11AM -0500, Kent Johnson wrote:[color=blue]
> 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/Coo.../Recipe/303279[/color]

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.dbapi 2 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)

iD8DBQFBsF3JdIO 4ozGCH14RAkSHAJ wNsXFjV5XRZsJpB 4EvEk6/hMacUgCfa89R
87c2oH75fpIKZIO 0PkvK++s=
=MSvj
-----END PGP SIGNATURE-----

[Gerhard Haering]

Re: PySQLLite Speed

On Fri, Dec 03, 2004 at 06:06:11AM -0500, Kent Johnson wrote:[color=blue]
> 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/Coo.../Recipe/303279[/color]

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.dbapi 2 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)

iD8DBQFBsF3JdIO 4ozGCH14RAkSHAJ wNsXFjV5XRZsJpB 4EvEk6/hMacUgCfa89R
87c2oH75fpIKZIO 0PkvK++s=
=MSvj
-----END PGP SIGNATURE-----