Difference performance sqlite3 WIndows and Linux

This is the place for queries that don't fit in any of the other categories.

Difference performance sqlite3 WIndows and Linux

Postby mke21 » Fri Aug 09, 2013 12:16 pm

Hi,

I'm making a cross platform application which does it's storage in SQLite files. These files are quite big, 10,000,000 in a table.
When I open the file I run a query to count the amount of rows in the table.

My code, using the sqlite3 module:
Code: Select all
def runquery(con):
    # con is a sqlite3 connection object
    with con:
        cur = con.cursor()
        cur.execute("SELECT COUNT(rowid) FROM snp",[])
        return cur.fetchall()[0][0]


Execute time for a table with 10,000,000 rows:
- Kubuntu, 4 gb ram, dualcore: 8s
- Windows XP-32, 2 GB Ram, dualcore: 15m32s
- Win7-64, 4 GB RAM, I5: 13m15s
During execution, hardly any memory or cpu power is used, so no bottleneck there.
When I run the query through the sqlite3.exe shell it is as fast as the Linux implementation. I think this means that the python code is to blame for the bad performance.
It's the cur.execute line that takes so long to execute.

Anyone knows why the big difference betwene Windows and Linux? And more important, how can I imporve the performance, so it will work as fast as the Linux machine. It's unusable right now.

I'm using Python 2.7.5, fresh installed today on the machines.
mke21
 
Posts: 2
Joined: Fri Aug 09, 2013 11:56 am

Re: Difference performance sqlite3 WIndows and Linux

Postby mke21 » Sat Aug 10, 2013 10:27 am

update: it definately seems to be a problem with the windows version of the sqlite3 library. I tested the apsw library (https://code.google.com/p/apsw/ and it performs much better, about the same as I had on the Linux version of sqlite3.
mke21
 
Posts: 2
Joined: Fri Aug 09, 2013 11:56 am


Return to General Coding Help

Who is online

Users browsing this forum: snippsat and 3 guests