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"
> To: "General Discussion of SQLite Database"
> 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"
>>>
>>
>>> > 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
