Okay, let me try that. I will report back. By the way the tool I am using is a Firefox add in called Sqlite Manager (https://code.google.com/p/sqlite-manager/). Didn't know there was a tool that you guys support.
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Monday, October 28, 2013 9:13 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8 On Mon, Oct 28, 2013 at 9:02 AM, Mike Clagett <mike.clag...@mathworks.com>wrote: > Well, the text for the index issued from the program is constructed > programmatically, so I'm going to provide the actual text as seen in > the Visual Studio debugger just before it is sent to Sqlite. > > The actual text, as seen in VisualStudio's debugger, is: > > CREATE INDEX IDX_C$SimulinkBlockParameters$Property_COLLECTIONID ON > C$SimulinkBlockParameters$Property (COLLID) > > The program code that creates this index eventually executes a > sqlite3_prepare_v2 followed by a call to the step() function. > > Here's the text of the resulting index as reported by the Sqlite tool: > > CREATE INDEX IDX_C$SimulinkBlockParameters$Property_COLLECTIONID ON > C$SimulinkBlockParameters$Property (COLLID) > > When I dropped and recreated it, I copied this text and pasted it back > into the Execute SQL pane of the Sqlite tool. > And what "SQLite tool" are you using? What you describe shouldn't be happening. An index is an index. It should provide the same benefit regardless of when or how it was created. My only guess is that the "Sqlite tool" that you are using is doing something extra behind the scenes. Do you get the same results using the "sqlite3.exe" command-line tool provided on the SQLite website? The "sqlite3.exe" command-line tool is officially supported by the developers. Any other "Sqlite tool" you are using is not. > > I just ran the experiment again and the average execution time of a > SELECT statement that uses this index is about 18 or 19ms when run > immediately after the database (and the index in question ) is generated and > the > records initially populated. After manually dropping and recreating the > index (as described above) statement execution time dropped to about 1 ms. > > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto: > sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp > Sent: Friday, October 25, 2013 8:14 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Trying to figure out how to circumvent > sqlite3_win32_mbcs_to_utf8 > > On Fri, Oct 25, 2013 at 6:54 PM, Mike Clagett > <mike.clag...@mathworks.com > >wrote: > > > Yet the following oddity still remains: when we create the index > > programmatically before adding the rows, it doesn't seem to have any > > effect. Both interactively in the Firefox tool and programmatically > > from the second phase of my application it takes about an average > > 18ms to do a select -- the same amount of time without the index > > even being there at all. Only when I manually drop and recreate the > > index from within the Firefox tool does the index kick in and > > subsequently reduce select time to about 1ms. > > > > > > Do you have any thoughts on this? > > > > My guess: The index created by the program and the index you create > using Firefox are not the same. The difference might be subtle - > perhaps a change in collating sequence or something - but it is enough > to make the difference. > > Send us the exact text of the index (both the program version and the > Firefox version) and its associated table and maybe also the query, > then we can tell. > > Exact text: No changes please. > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ 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
