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).


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.

Dennis Cote -----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]

To unsubscribe, send email to [EMAIL PROTECTED]

Reply via email to