[sqlite] Odd indexing behavior, 2nd request for help
Hello. I sent this 2 days a go but have not received an explanation or solution. Anybody had this problem? Hello and Happy New year! I have an indexing problem with a very simple query. The correct index is not used or no index is used, according to Explain Query Plan. In the following query, all columns but num have indexes, using default. num is integer; filetype is Varchar; time1 is Datetime; name is varchar. This query: select * from tbl where num = 2563351070 and filetype = 'cpp' and time1 > '2003-01-01' and time1 < '2006-05-05' returns 18k rows in 600ms and index for time1 is used. If I 'plus' the time1 columns, the exec time drops to 420ms and no index is used. If I use 'name' instead of 'filetype' with time1 "plussed", no index is used. Also, if I use 'order by name', no index is used. To summarize, in this query with indexes on most columns, SQLite will only use time1 index and is slower doing so. Could someone please explain to me why this happens? Thanks. Michael - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] odd indexing behavior
Thanks for the response, Joe. Unfortunately, since I have no way of knowing a priori what the relative number of returned rows is (without doing another query), using the plus is not really a work-around, it is a "don't use indexes" rule. I can't just arbitrarily assign the "plus" mark. Also, no other index was used, despite the plus on the time1 column. And 'order by' did not use the index. Has anyone else seen these problems? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] odd indexing behavior
Hello and Happy New year! I have an indexing problem with a very simple query. The correct index is not used or no index is used, according to Explain Query Plan. In the following query, all columns but num have indexes, using default. num is integer; filetype is Varchar; time1 is Datetime; name is varchar. This query: select * from tbl where num = 2563351070 and filetype = 'cpp' and time1 > '2003-01-01' and time1 < '2006-05-05' returns 18k rows in 600ms and index for time1 is used. If I 'plus' the time1 columns, the exec time drops to 420ms and no index is used. If I use 'name' instead of 'filetype' with time1 "plussed", no index is used. Also, if I use 'order by name', no index is used. To summarize, in this query with indexes on most columns, SQLite will only use time1 index and is slower doing so. Could someone please explain to me why this happens? Thanks. Michael - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Way to "Bind" Columns in Prepare
Of course. Thanks.
[sqlite] Way to "Bind" Columns in Prepare
I need to embed variable column names in my queries, such as 'colX > '2004-01-01' where 'X' is a variable. bind_text will not do this. Is there another way? Thanks. Michael
RE: [sqlite] SQLiteSpy vs. SQLite.dll speed comparison
Thanks to Christian and John for the pointers regarding compilers. I have not compiled the sqlite sources myself but have used the supplied binary. Could either one you give me some tips for compiling the sqlite sources for either vs 6 or 8? John, I will follow your advice on inline functions. The absolute last worry I have is the size of my exe. My concerns are speed (1) and runtime memory (2), i.e., in-mem db size. BTW, here are some times to demonstrate what I am facing: A table with 14 columns, 8 indexes, only 4k rows. This is a secondary(subset) table - not a view - that I created since running this query against my complete table of 440k rows was impossibly slow. The following query takes 75ms in my code - just the SQL_exec and callbacks - while SQLiteSpy takes 20ms, including display. select * from (select f1, f2, f3, f4, f5 from table where f6 = 2563351070 and f2 <='2006-01-01' and f2 >= '2004-01-01') order by f1 limit 32 offset 855; This qry is used to refresh a scrolling display where any of the constants are actually variables. The subquery returns about 1000 rows. Note that a LIMIT = 1 is only marginally faster than 32. Also, as the offset increases, the exec. time increases about 50% as fast, which I do not understand, since this would merely seem to be an index into the result set that should be low cost.
RE: [sqlite] SQLiteSpy vs. SQLite.dll speed comparison
Daniel: Thanks for the suggestion. I wasn't aware that the prepare statement gained you that much for one-table select queries. I use it for multi-100k inserts (along with trans.) and it saves quite a bit of time. This is my sql for the present problem: select * from (select f1, f2, f3, f4, f5 from Table where f2 = 'abc' and f3 = 2563351070 and f4 >= '2004-01-01'and f4 <='2006-01-01' ) order by f1 limit 32 offset 900; Do you think that prepare would be helpful here? Regards, Michael
RE: [sqlite] SQLiteSpy vs. SQLite.dll speed comparison
Brannon: Thank you for your thoughts. To be clear, the 'optimize for speed' setting in MY release is actually slower than MY debug version - I know nothing about Ralf's settings. That issue is separate from SQLiteSpy - I didn't mean to conflate them. And the issue is not which version of VS I'm using. If I simply put a 'return 0' in my callback, the time is within 2%. Thus, I am timing the dll only and I am using the pre-compiled version, not a VS 6 compiled version. Ralf is compiling his own version, in Borland, and gets 2.5 times my speed. Therefore, I believe the speed difference lies in the differences between the two compiled versions. Michael
