On Wed, Oct 26, 2011 at 11:35:33PM +0200, Tobias Mohrl?der scratched on the wall: > On Wednesday, Oct 26, 2011 at 02:26PM Jay A. Kreibich wrote: > > > On Tue, Oct 25, 2011 at 11:51:46PM +0200, Tobias Mohrl?der scratched on the > > wall: > >> Hello everybody, > >> > >> I strongly believe that my setup is somehow wrong, I just can't figure > >> out how. > > > > > >> sqlite3_prepare_v2(db, "SELECT * FROM mytable WHERE MyKey IS ?", -1, > >> &stmt, 0); > > > > Don't use "IS", use "=". The two operations are quite different. > > > > -j > > That's it, thanks! > > I'm still wondering why there is such a huge performance hit. Judging from the > documentation I would have expected some if-statements worth of overhead, > nothing serious.
The use of IS is causing the query optimizer to use a full table scan, essentially turning the query into a O(n) operation. This has to do with how IS differs from = in the handling of NULLs. Since it is possible bind a NULL to the query parameter (and one tends to only use IS when looking for NULLs, so this is a valid assumption), the optimizer likely assumes the condition in question may return "hit" for a significant percentage of the table rows. This, and some more subtle interactions, makes a table scan more appropriate unless you know the size and diversity of the column in question. Further, the optimizer cannot replace the IS with a = internally, despite the fact MyKey is known to be an I.P.K. (other any other column with a NOT NULL constraint). -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
