Calling UPPER() for each field/value while querying is a pain, if you can
assume the names are all in uppercase
(forcing this in your program before passing them to SQlite), you could
speed up things a lot. But you need to
analyze what´s happening inside SQlite to check if indices are used, etc.
SQlite command line app has a command that does this (does not remember if
it´s this:
".explain ON|OFF Turn output mode suitable for EXPLAIN on or off.\n")
'ANALYZE' SQlite command also performs some kind of prefetching of indexes
and loads results in a internal
table, can help also to speed up.
Also, try to avoid JOINS like this
'SELECT a.* FROM globalRankingTable a, friendTable b WHERE upper(b.player) =
upper('?') AND upper(b.friend) =
upper(a.name))'
It´s better to explicit use LEFT JOIN / INNER JOIN always.
-----Original Message-----
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns
Sent: sábado, 10 de julho de 2010 17:39
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query critique
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 07/09/2010 01:54 AM, Ian Hardingham wrote:
> And here is my query (written in a script language):
Are you actually getting correct answers from this?
> upper('?')
Unless your script language includes a SQL parser, that does not do what you
think it does. It is seeing if the value matches upper cased question mark.
It looks like you wanted to supply bound parameters so the question mark
should not be in quotes.
I'd also suggest using a better wrapper or scripting language since you are
providing three bindings but the query is only using one of them. It should
complain.
> - yes I know I'm an idiot with the upper stuff, a refactor is needed on
> that - I know all of my "name" fields should really be integers.
Or look into indices and collations.
In any event you should always post your exact actual query so we don't have
to figure out issues like the question marks are intentional or
transcription errors.
Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkw42kcACgkQmOOfHg372QSgxQCfTtgfYFMZ9a4nHtbJn8gI7XEK
7wYAn3ghSgc3wPgl0bFGLq8oA2moTJcs
=aCUo
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
