[sqlite] Very slow processing of some SELECT statements
Hello, Running under Windows XP, using sqlite3.exe version: 3.7.8 2011-09-19 14:49:19 3e0da808d2f5b4d12046e05980ca04578f581177 As follows: sqlite3 foo.db The following SQL: DROP TABLE IF EXISTS rooms; DROP TABLE IF EXISTS exits; CREATE TABLE IF NOT EXISTS rooms ( roomidINTEGER PRIMARY KEY AUTOINCREMENT, uid TEXT NOT NULL -- unique room ID ); CREATE TABLE IF NOT EXISTS exits ( exitid INTEGER PRIMARY KEY AUTOINCREMENT, fromuid STRING NOT NULL -- exit from which room (in rooms table) ); CREATE INDEX IF NOT EXISTS fromuid_index ON exits (fromuid); SELECT * FROM exits WHERE fromuid = '2E515665758C87202B281C7FC'; Takes over 2 seconds to execute (in particular, the SELECT statement). Two changes make it much faster. Either: * Delete the CREATE INDEX line or * Change the select statement to: SELECT * FROM exits WHERE fromuid = 'x2E515665758C87202B281C7FC'; I'm not sure what is going on, but it appears that somewhere internally SQLite3 is trying to calculate the very large number 2e515665758 (2 times 10 to the power 515665758). And somehow the index is influencing this behaviour. Trying under Mac OS/X (Lion) does not appear to exhibit this problem. The string I am searching for is a hex hash string, generated by hashing various other things (not shown here). Occasionally it would appear, the hash "looks like" a decimal number with an exponent. I draw your attention to the fact that the string being searched for is quoted, and that it is declared as a text field in the database. Any suggestions welcomed. - Nick Gammon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very slow processing of some SELECT statements
On 17/10/2011, at 8:33 PM, Nick Gammon wrote: > ... > The following SQL: > > ... > SELECT * FROM exits WHERE fromuid = '2E515665758C87202B281C7FC'; > > > Takes over 2 seconds to execute (in particular, the SELECT statement). > Further to the above, changing the column type from STRING to TEXT also fixes it. However isn't over 2 seconds a bit much for evaluating a number, regardless of the column type? - Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very slow processing of some SELECT statements
On 17 Oct 2011, at 10:33am, Nick Gammon wrote: > Running under Windows XP, using sqlite3.exe version: > > 3.7.8 2011-09-19 14:49:19 3e0da808d2f5b4d12046e05980ca04578f581177 [snip] > > Trying under Mac OS/X (Lion) does not appear to exhibit this problem. Just a note that the version of the sqlite3 binary included with Lion is currently SQLite version 3.7.5 Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very slow processing of some SELECT statements
On 10/17/2011 04:33 PM, Nick Gammon wrote: Hello, Running under Windows XP, using sqlite3.exe version: 3.7.8 2011-09-19 14:49:19 3e0da808d2f5b4d12046e05980ca04578f581177 ... Trying under Mac OS/X (Lion) does not appear to exhibit this problem. No problem with that version on Linux either. Did you download the binary from the website or build it yourself? If the latter, which compiler are you using? And what level of optimization is enabled? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very slow processing of some SELECT statements
On 17/10/2011, at 9:55 PM, Dan Kennedy wrote: > Did you download the binary from the website or build it yourself? > If the latter, which compiler are you using? And what level of > optimization is enabled? I initially observed the problem with version 3.7.7.1, as embedded in my application using Visual C++ version 6.0. I have "maximum speed" optimization selected. When a user reported the problem, and I confirmed it, I downloaded the latest command-line utility from the SQLite site (version 3.7.8), as follows: http://www.sqlite.org/sqlite-shell-win32-x86-3070800.zip So the test was confirmed using sqlite3.exe, from the SQLite3 site, as distributed. - Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very slow processing of some SELECT statements
On 10/18/2011 03:17 AM, Nick Gammon wrote: On 17/10/2011, at 9:55 PM, Dan Kennedy wrote: Did you download the binary from the website or build it yourself? If the latter, which compiler are you using? And what level of optimization is enabled? I initially observed the problem with version 3.7.7.1, as embedded in my application using Visual C++ version 6.0. I have "maximum speed" optimization selected. When a user reported the problem, and I confirmed it, I downloaded the latest command-line utility from the SQLite site (version 3.7.8), as follows: http://www.sqlite.org/sqlite-shell-win32-x86-3070800.zip So the test was confirmed using sqlite3.exe, from the SQLite3 site, as distributed. Now fixed here: http://www.sqlite.org/src/ci/59bb999c8b?sbs=0 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very slow processing of some SELECT statements
On 18/10/2011, at 3:38 PM, Dan Kennedy wrote: > Now fixed here: > > http://www.sqlite.org/src/ci/59bb999c8b?sbs=0 Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very slow processing of some SELECT statements
Is it normal that fromuid of the table exits is STRING ? I think it should be TEXT to be surely processed as text and not float ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very slow processing of some SELECT statements
On 18/10/2011, at 4:46 PM, Sylvain Pointeau wrote: > Is it normal that fromuid of the table exits is STRING ? > I think it should be TEXT to be surely processed as text and not float That was an error. However it shouldn't take SQLite 2.5 seconds to handle *any* numeric literal. Especially as it was quoted. For example, in C you don't expect: x = "123E45678942"; ... to go through any sort of numeric conversion. Now I know this isn't C, but the "let's see if we can turn a string into a number, and take two to three seconds to do so" is not right, IMHO. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very slow processing of some SELECT statements
On Tue, Oct 18, 2011 at 8:30 AM, Nick Gammon wrote: > > On 18/10/2011, at 4:46 PM, Sylvain Pointeau wrote: > > > Is it normal that fromuid of the table exits is STRING ? > > I think it should be TEXT to be surely processed as text and not float > > That was an error. However it shouldn't take SQLite 2.5 seconds to handle > *any* numeric literal. Especially as it was quoted. For example, in C you > don't expect: > > x = "123E45678942"; > > ... to go through any sort of numeric conversion. Now I know this isn't C, > but the "let's see if we can turn a string into a number, and take two to > three seconds to do so" is not right, IMHO. > > it has something to do with type affinity. I don't think you would have had this slow down if your type was TEXT, because (I think) sqlite would have not tried to convert it. Anyway seems to be improved now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very slow processing of some SELECT statements
On 18 Oct 2011, at 6:46am, Sylvain Pointeau wrote: > Is it normal that fromuid of the table exits is STRING ? > I think it should be TEXT to be surely processed as text and not float If you're writing your database especially for SQLite you should specify 'TEXT' not 'STRING'. However, SQLite has a process where it looks at your 'type' and guesses the affinity it has that's most useful: section 2.2 in http://www.sqlite.org/datatype3.html As you can see it does not specifically recognise 'STRING' as meaning 'TEXT'. 'STRING' is not one of the types that was ever part of the SQL design so there's really no excuse for anyone using it. Nevertheless, the delay of two seconds was obviously a bug and kudos to the fixers for fixing it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
