Re: [pysqlite] Performance degradation

Markus Gritsch wrote:
> Hi,
> 
> I have an SQLite database which is about 100 MB in size.  The execution time of
> 
> cursor.execute("SELECT * FROM entry WHERE note LIKE '%test%'")
> cursor.fetchall()
> 
> is quite different with the pysqlite versions I used for testing:
> 
> 1.15 sec using sqlite3 which ships with Python 2.5.2 on Windows
> 1.27 sec using pysqlite-2.4.1.win32-py2.5.exe
> 1.74 sec using pysqlite-2.5.0.win32-py2.5.exe
> 
> The changelog for pysqlite 2.5.0 mentions that the Windows binaries
> are now cross-compiled using mingw.  Was version 2.4.1 built using
> cl13.10 on Windows?  This could explain the quite significant
> slow-down in version 2.5.0.

I tried to find out if there's a general performance degradation for 
this use case from 2.4.1 to 2.5.0:

2.4.1:
took 6.8649045229 seconds - average 10 runs

2.5.0:
took 6.8972679615 seconds - average 10 runs

Considering this was not a completely idle system, I'm confident now 
that nothing changed at all performance-wise between these two versions.

I used the same SQLite versions and built using amalgamation both times.

My first guess would be different SQLite versions used in your case, I 
don't think different compilers make such a big difference.

Is there anybody here that could est speed of pysqlite compiled with 
Visual Studio 2005 vs. gcc on Windows? I'll attach my quick test script. 
As apparently pysqlite doesn't make the difference, it should be 
possible to test that with SQLite binaries, too.

-- Gerhard

_______________________________________________
list-pysqlite mailing list
list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org
http://itsystementwicklung.de/cgi-bin/mailman/listinfo/list-pysqlite

import time
from pysqlite2 import dbapi2 as sqlite3
import time

con = sqlite3.connect("data")

NUM = 5000 * 1000

def what(n):
    if n % 1000 == 0:
        return "test"
    else:
        return "foo"

def build_db():
    cur = con.cursor()
    bla = (("x%i%s%ix" % (i, what(i), i),) for i in xrange(NUM))
    cur.execute("create table entry(note)")
    cur.executemany("insert into entry(note) values (?)", bla)
    con.commit()

def test_db():
    cur = con.cursor()
    cur.execute("SELECT count(*) FROM entry WHERE note LIKE '%test%'")
    cur.fetchone()

if __name__ == "__main__":
    TIMES = 10
    sum = 0.0
    for i in range(TIMES):
        t0 = time.time()
        test_db()
        sum += time.time() - t0
    print "took", sum / TIMES, "seconds - average", TIMES, "runs"