I will have to get back to you on this. While running tests against isolated queries, I noticed an unusual circumstance. When I isolate the queries into a test program, running through our present libraries, 3.7.9 is roughly 4 times faster executing the exact same queries where it is running roughly 5 times slower in the context of the application. As those queries do not execute in the same order, I suspect that page swapping and caching issues may be involved. I'm proceeding on that assumption.
On Mon, Jan 9, 2012 at 12:11 PM, Richard Hipp <d...@sqlite.org> wrote: > Thank you for sending the sample database file. > > Rather than forcing me to understand your log database, and fill in > parameters with reasonable values, I wonder if you could simply email one > or more queries (with the parameters filled in) which you find to be slower > in 3.7.9 versus 3.6.7? > > On Mon, Jan 9, 2012 at 11:13 AM, Richard Hipp <d...@sqlite.org> wrote: > >> Please send private email to d...@sqlite.org. CC to d...@sqlite.org and >> j...@sqlite.org. >> >> >> On Mon, Jan 9, 2012 at 11:09 AM, John Elrick <john.elr...@fenestra.com>wrote: >> >>> Sorry "The combined files are 2.4 MB zipped" >>> >>> On Mon, Jan 9, 2012 at 11:09 AM, John Elrick <john.elr...@fenestra.com> >>> wrote: >>> > Richard, >>> > >>> > I have the information available for you. Unfortunately, it appears >>> > that I cannot actually reduce the case, however, the issues appear to >>> > be fairly confined. >>> > >>> > The test database is 6 MB and I have a database showing the >>> > performance tracking for each of the queries which is about 140KB. >>> > The combined files are 2.4 MB. How would you prefer I send this file >>> > to you? >>> > >>> > On Fri, Jan 6, 2012 at 4:51 PM, John Elrick <john.elr...@fenestra.com> >>> wrote: >>> >> Yes. Look for it on Monday. I'm about to leave for the day. The >>> >> queries are automatically generated and will require some mining to >>> >> get specific examples. I should be able to reduce the range to a >>> >> simple repeatable case but it may take a while. >>> >> >>> >> Thanks very much and have a great weekend. >>> >> >>> >> On Fri, Jan 6, 2012 at 4:42 PM, Richard Hipp <d...@sqlite.org> wrote: >>> >>> Can you publish for us: >>> >>> >>> >>> (1) A sample database >>> >>> (2) The specific queries that are causing your problems >>> >>> >>> >>> So that we can investigate further? >>> >>> >>> >>> On Fri, Jan 6, 2012 at 4:31 PM, John Elrick <john.elr...@fenestra.com >>> >wrote: >>> >>> >>> >>>> Background >>> >>>> ========= >>> >>>> We have been using Sqlite3 in a project since 2007. Because of the >>> >>>> release cycles involved, we normally upgrade infrequently. We >>> >>>> recently upgraded from 3.6.7 to 3.7.9 and encountered two serious >>> >>>> issues. We need assistance in narrowing down the causes. >>> >>>> >>> >>>> We are developing using Delphi. We are using the amalgamation and >>> are >>> >>>> compiling it using the free Borland BCC complier. The result is an >>> >>>> obj file which is statically linked into our application. We have >>> >>>> been using this technique since we began using Sqlite. >>> >>>> >>> >>>> In the tests below, the strategy for changing between versions is to >>> >>>> change which obj file is statically linked into the application. The >>> >>>> application was recompiled between each test. >>> >>>> >>> >>>> Issues Encountered >>> >>>> =============== >>> >>>> >>> >>>> 1. After changing there is an order of magnitude performance >>> >>>> reduction. Using GP Profile to monitor operations, here are the >>> >>>> results for identical application uses: >>> >>>> >>> >>>> Operation executes _sqlite3_step 49,152 times >>> >>>> >>> >>>> 3.6.7 time 5.24 seconds >>> >>>> 3.7.9 time 41.19 seconds >>> >>>> >>> >>>> 2. During testing we encountered a bug which had not been >>> encountered >>> >>>> before. Research by our CTO established that in 3.7.9 a row was >>> >>>> returned from this query which contained empty values. In 3.6.7 >>> there >>> >>>> were no results from the query. >>> >>>> >>> >>>> Summation >>> >>>> ========= >>> >>>> Given how throughly Sqlite is tested I have difficulty believing >>> there >>> >>>> is an actual bug in Sqlite. I believe an interaction is occurring >>> >>>> which is a result of changes to the Sqlite code base which are >>> >>>> producing unexpected resulted when compiled into our application. To >>> >>>> help resolve the problem, I would like to know if there is an >>> >>>> historical record of the amalgamations. If so, I can carry out >>> >>>> testing to determine in what version the problem manifests. >>> >>>> >>> >>>> If anyone has any other recommendations I would be pleased to hear >>> >>>> them. For the moment we are reverting to 3.6.7 as a temporary >>> >>>> solution. >>> >>>> >>> >>>> -- >>> >>>> John Elrick >>> >>>> Fenestra Technologies >>> >>>> 540-868-1377 >>> >>>> _______________________________________________ >>> >>>> 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 >>> >> >>> >> >>> >> >>> >> -- >>> >> John Elrick >>> >> Fenestra Technologies >>> >> 540-868-1377 >>> > >>> > >>> > >>> > -- >>> > John Elrick >>> > Fenestra Technologies >>> > 540-868-1377 >>> >>> >>> >>> -- >>> John Elrick >>> Fenestra Technologies >>> 540-868-1377 >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> >> >> -- >> D. Richard Hipp >> d...@sqlite.org >> > > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- John Elrick Fenestra Technologies 540-868-1377 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
