Re: [sqlite] Huge performance drop when using prepared statement

2007-12-10 Thread Markus Gritsch
On 10/12/2007, Ed Pasma <[EMAIL PROTECTED]> wrote: > Hello, think I got it, but it is disappointingly simple, see below. Ed. > > Markus Gritsch wrote: > > > Even more strange: > > > > c.execute("""SELECT * FROM entry, word, word_entry WHERE > > entry.id = word_entry.entry_id AND > > word.id = wor

Re: [sqlite] Huge performance drop when using prepared statement

2007-12-09 Thread Ed Pasma
Hello, think I got it, but it is disappointingly simple, see below. Ed. Markus Gritsch wrote: Even more strange: c.execute("""SELECT * FROM entry, word, word_entry WHERE entry.id = word_entry.entry_id AND word.id = word_entry.word_id AND word.word GLOB ? """, ('tes*',)) takes less than 1ms

Re: [sqlite] Huge performance drop when using prepared statement

2007-12-09 Thread Markus Gritsch
On 09/12/2007, Markus Gritsch <[EMAIL PROTECTED]> wrote: > On 09/12/2007, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > > > SQLite will optimize a GLOB where the right parameter is > > a literal string. It will not do so if the right parameter is a > > parameter. http://www.sqlite.org/optovervie

Re: [sqlite] Huge performance drop when using prepared statement

2007-12-09 Thread Markus Gritsch
On 09/12/2007, Mag. Wilhelm Braun <[EMAIL PROTECTED]> wrote: > hi Markus, Hi Wilhelm > I just tried for fun: > > start = time.time() > SQLString=('''SELECT * FROM entry, word, word_entry WHERE > entry.id = word_entry.entry_id AND > word.id = word_entry.word_id AND > word.word GLOB '%s' > ''' %

Re: [sqlite] Huge performance drop when using prepared statement

2007-12-09 Thread Markus Gritsch
On 09/12/2007, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > On Dec 9, 2007, at 5:27 AM, Kees Nuyt wrote: > > > Problematic SELECT: > >> c.execute("""SELECT * FROM entry, word, word_entry WHERE > >> entry.id = word_entry.entry_id AND > >> word.id = word_entry.word_id AND > >> word.word GLOB ? >

Re: [sqlite] Huge performance drop when using prepared statement

2007-12-09 Thread Mag. Wilhelm Braun
hi Markus, I just tried for fun: start = time.time() SQLString=('''SELECT * FROM entry, word, word_entry WHERE entry.id = word_entry.entry_id AND word.id = word_entry.word_id AND word.word GLOB '%s' ''' % "hui*") c.execute(SQLString) and it is as fast as your first one - seems a pysqlite probl

Re: [sqlite] Huge performance drop when using prepared statement

2007-12-09 Thread D. Richard Hipp
On Dec 9, 2007, at 5:27 AM, Kees Nuyt wrote: Problematic SELECT: c.execute("""SELECT * FROM entry, word, word_entry WHERE entry.id = word_entry.entry_id AND word.id = word_entry.word_id AND word.word GLOB ? """, ('hui*',)) SQLite will optimize a GLOB where the right parameter is a lite

Re: [sqlite] Huge performance drop when using prepared statement

2007-12-09 Thread Markus Gritsch
On 09/12/2007, Kees Nuyt <[EMAIL PROTECTED]> wrote: > On Sun, 9 Dec 2007 10:55:16 +0100, "Markus Gritsch" > <[EMAIL PROTECTED]> wrote: > > >Hi, > > > >when using bind variables I get a huge performace drop compared to > >using a plain string. The query is demonstrated in the attached file > >"prob

Re: [sqlite] Huge performance drop when using prepared statement

2007-12-09 Thread Kees Nuyt
On Sun, 9 Dec 2007 10:55:16 +0100, "Markus Gritsch" <[EMAIL PROTECTED]> wrote: >Hi, > >when using bind variables I get a huge performace drop compared to >using a plain string. The query is demonstrated in the attached file >"problematic_query.py". Problematic SELECT: > c.execute("""SELECT * FRO

[sqlite] Huge performance drop when using prepared statement

2007-12-09 Thread Markus Gritsch
Hi, when using bind variables I get a huge performace drop compared to using a plain string. The query is demonstrated in the attached file "problematic_query.py". The database used can be downloaded from http://xile.org/le/prepared_statement.zip (1.75 MB) or generated by using the attached f