Hi -- I'm doublechecking my assertion. I removed the index generation from the code and regenerated the table and it does indeed seem to be taking 30 minutes. I believe the code that I use to insert a record may somehow check to see if it's already there first, which might explain why the index would have improved performance. I haven't actually profiled this code, so I don't know, but I have the option to add profiling if it turns out we need it to troubleshoot. I may be able to figure it out simply by tracing through the code, however. But I want to do this after my currently running timing test is done.
Also I'm relatively new here, so I don't know the policy on sharing internal artifacts externally. Want to clarify this before sending you anything. Regards, Mike -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Friday, October 25, 2013 9:42 AM 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 9:27 AM, Mike Clagett <mike.clag...@mathworks.com>wrote: > > We are seeing something strange, however, that I was wondering if you > had any thoughts on. Our program generates a schema programmatically > at the beginning of a run, if it isn't already there, and then inserts a > total of > about 300,000 records in various tables. On subsequent runs it does > mostly reading from these tables. We added code to generate the extra > index on the necessary tables and indeed its seems to have brought the > time for this initial data generation down from around 30 minutes to > about 8 minutes (not 100% sure that the time hadn't already been reduced, but > I > believe it was after adding this index). However, on subsequent runs of > the program the queries that were taking 18ms were still taking 18ms. > It was as if there was no index added at all. > Adding an index should increase INSERT times, not decrease. So this is puzzling. Can you share the schema and especially the troublesome index with us? > > I double checked this in the firefox sqlite tool and the indexes were > definitely there and appeared to be constructed correctly, but issuing > the same queries interactively in the firefox tool also took about 18ms to > execute. Then on a lark I decided to drop the index and recreate it in > the firefox tool. Once I did that, the query execution time dropped > to 1 ms and the overall program execution time dropped from about 20 minutes > to > under 2. Any thoughts on what might be happening here? > > Thanks again for all your help. > > Regards, > > Mike > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto: > sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp > Sent: Thursday, October 24, 2013 10:58 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Trying to figure out how to circumvent > sqlite3_win32_mbcs_to_utf8 > > On Thu, Oct 24, 2013 at 10:54 AM, Mike Clagett > <mike.clag...@mathworks.com>wrote: > > > Hi -- > > > > This is indeed exactly what is happening. On many occasions the > > mechanism is only interested in the first row of a result set and issues > > sqlite3_reset() before the result set is completely processed. Given > that > > this is what is occurring, is there any way around this -- > > essentially, I guess, any way to still have that step statement > > executed > or at least the > > profile callback invoked? Because at the end of the day, it is the > > numerous select statements that we probably need to be profiling. > > > > We have your request to enhance the sqlite3_profile() mechanism to > invoke the profile callback on an early sqlite3_reset(). > Unfortunately, there are several higher-priority enhancement requests > in queue in front of this, so it might be a while... > > > -- > 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
