Thank you, all for your responses. I did not have access to table definition so I used collate no-case within statements. However, I still have very slow performance of the DB. I ran my application using Intel's VTune performance analyzer and about 90% of time is being spent in the file sqlite.c. Some of the high-consuming functions are listed below:
sqlite3VdbeExec = 16.87% sqlite3VdbeMemReleaseExternal = 5.26% sqlite3ValueText = 4.68% isLookaside = 4.45% sqlite3DbFree= 4.40% sqlite3VdbeChangeEncoding = 3.83% sqlite3_free= 3.23% patternCompare = 2.77% sqlite3VdbeMemNulTerminate = 2.77% sqlite3VdbeMemRelease = 2.66% sqlite3Utf8Read = 2.40% sqlite3VdbeMemStoreType = 2.23% likeFunc = 2.11% btreeParseCellPtr = 2.10% sqlite3BtreeNext = 2.04% I need to spend time to investigate this further and do optimize but would be grateful if anyone could offer some insight. Thanks! ~r -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich Sent: Tuesday, March 30, 2010 8:13 PM To: Tom Holden Cc: General Discussion of SQLite Database Subject: Re: [sqlite] Case-sensitivity, performance and LIKE On Tue, Mar 30, 2010 at 10:59:49PM -0400, Tom Holden scratched on the wall: > > ----- Original Message ----- From: "Jay A. Kreibich" <j...@kreibi.ch> > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Sent: Tuesday, March 30, 2010 9:26 PM > Subject: Re: [sqlite] Case-sensitivity, performance and LIKE > > >> On Tue, Mar 30, 2010 at 08:41:09PM -0400, Tom Holden scratched on the >> wall: >>> >>> ----- Original Message ----- From: "Simon Slavin" >>> <slav...@bigfraud.org> >> >>> > columnName TEXT COLLATE NOCASE >>> > >>> > then all sorting and SELECT queries on it will ignore case. >>> >>> You don't even have to change the defined collation as you can impose the >>> NOCASE collation in the SELECT statement as: >>> >>> SELECT * from Customers WHERE LastName = 'Shaw' COLLATE NOCASE AND >>> FirstName >>> = 'Gioia' COLLATE NOCASE; >> If either/both of these columns has/have indexes, it is best to change >> it in the table definition. Otherwise you also need to define it in the >> index definition, as well as everywhere you expect to use the index. >> This is all automatic if everything is built with the collation in >> the table definition. > Maybe it works OK because the custom collation is a superset of the > integral NOCASE collation and my data does not lie outside the latter. Sorry... I didn't mean to imply this was wrong. Using a specific collation in a query is perfectly OK, and should always result in the correct answer. The issue is that a query will only use an index if the query collation and the index collation match. Given the OP's performance motivations, it seemed important. Hence, for general situations where performance is a concern, it is best to just declare the whole column with whatever collation you want to use. Otherwise, it is all too easy to miss something somewhere and end up with a much slower query. For one-off specific instances when you want to use a specific collation (or, as in your situation when you cannot re-define the table) it is perfectly valid to simply add the collation to the query. However, that will result in a full table-scan, even if an index is otherwise available. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ 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
