Hi -- Once again thank you for all your help. It was instrumental in helping identify the problem. Once I added an empty while loop that completed the processing of some key query results, the SELECT statements showed up in my profiling output and we immediately identified the one that was missing a key index, thereby triggering a table scan. Query time dropped from about 18ms to about 1 ms and overall processing time from about 20 minutes to under 2. Not bad for a day's work.
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. 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
