Thanks for the helpful reply - it does seem like it'll probably be be
better and easier to compile a new statement every time - a possible
optimization I'm thinking of making is that I'll cache the fields
selected in the previous query - that way I'll only need to recompile
the query every time the fields needed changes (which for my purposes
could potentially be never or rarely). It also allows me to use the =
operator to ensure that the id3 tags match exactly.
Dennis Cote wrote:
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]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
