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
