Vitali Lovich wrote:
Yeah, I know I can do that, and that's my backup option. I just
wasn't sure how much performance suffers in this situation because
this has to keep re-compiling the SQL statements (whereas right now,
all my SQL statements are only prepared once at runtime and then the
appropriate values are simply bound). I was hoping that I could
bypass figuring out the precise performance hit if I could figure out
a clever way of doing this (right now sqlite only accounts for about
16% of the time spent).
Vitali,
Now I see why you are trying to do this. It is an interesting
optimization idea, but it will only work if the time to execute all the
wildcard comparisons is less that the time it takes to compile the more
specific query.
To get an idea how long it takes sqlite's like function to do a wildcard
match I ran the following test. I created a database with 8M rows
containing the string "1234567890". I then timed the execution of the
following two queries.
select count(*) from t;
select count(*) from t where a like '%';
The only difference is that sqlite will execute a call to the like
function for each row. Each comparison will succeed and the same result
is returned. The first query took 1.59 seconds, and the second took 6.29
seconds. The difference, 4.7 seconds, is the time it took to execute the
like comparisons. This gives an execution time of about 560 ns per
wildcard like comparison.
If your table has 2000 songs and each song has 6 ID3 tags, and you are
trying to match only one of them, you will be executing 10K (5 * 2000)
unnecessary wildcard like comparisons. This should take about 5.6ms. So
if sqlite takes less time than this to compile your query it would be
faster to use a specific query that only looked at the one column you
are trying to match. You will have to write some test code to time
preparing your queries.
Regardless of which way you build your queries, you will still have to
escape any wildcard and escape characters in the user supplied (or
selected) strings that you are using with the like operator for the
columns that you are trying to match.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
