John, I read your previous post and unfortunately, your conversation with Richard didn't reveal much details. I worked with scenario similar tou yours (Delphi + statically linked sqlite) for several years and during this period an advanced admin/console form was developed that allows dynamical loading of different versions of sqlite, reporting vfs bandwidth, times and even the number of memory requests. So consider sharing more information about your db and queries here or contact me directly if you wish, I hope I can help at least a little.
As a quick guess I remember that fts was one of the places that allocated heavily with some queries, but I can't recall right now. Max On Sat, Jan 14, 2012 at 1:49 AM, John Elrick <john.elr...@fenestra.com>wrote: > Richard and all, > > On January 6, I wrote a posting (Problems encountered while upgrading > Sqlite from 3.6.7 to 3.7.9) concerning a slow down noticed in upgrading our > Delphi application from using 3.6.17 to 3.7.9. Richard asked for some more > specific information including a replicatable case, which has proven > problematic. Here are the results of my week long investigation. > > I created a logging system which took a specific set of data and converted > all of the automatically run queries to an SQL script which I could use in > a test application. When testing this particular script using a test > program which uses our Delphi wrappers the following times are observed: > > Test Application Run Batch Script > > 3.6.17: 14 seconds > 3.7.9: 10 seconds > > This clearly demonstrates that the newer version of Sqlite is, all things > being equal, superior in performance to the older. However, tests inside > our Delphi application demonstrate that reaching the exact same point of > the database result in the following times: > > Live Application > > 3.6.17: 16 seconds > 3.7.9: 58 seconds > > Extensive profiling of the application finally turned up an unusual and > inexplicable difference between the console application and our regular > application. I am hoping someone on this group may have some ideas. > > I created a Pascal unit whose sole purpose was to delegate to our C > standard library unit. By having delegates in place it becomes easy to > profile the application. This unit is used only by the Sqlite libraries. > > When I execute the application with the same data as above, the following > are the top consumers of time: > > 3.6.17 > > Procedure % Time Time Calls > _sqlite3_step 58.4% 1.79 5,052 > _memcpy 22.8% 0.69 1,342,957 > _memset 7.8% 0.24 465,299 > ... > _malloc 1.9% 0.06 95,505 > > > 3.7.9 > > Procedure % Time Time Calls > _malloc 56.9% 44.39 6,975,335 > _sqlite3_step 30.4% 23.68 5,052 > _memcpy 4.8% 3.70 7,710,259 > > > > So, obviously the problem is that _malloc is being called a much larger > number of times by 3.7.9 in this particular circumstance -- roughly 70 > times as often. _memcpy demonstrates roughly 6 times as many calls I ran > a test with a much smaller dataset tracking every call to malloc back to > the main source line. The results were as follows: > > 3.6.17, > _sqlite3_exec calls _malloc 1,101 times > _sqlite3_step calls _malloc 1,812 times > > 3.7.9 > _sqlite3_exec calls _malloc 65,227 times > _sqlite3_step calls _malloc 47,109 times > > At this point I've exhausted my avenues of research. Does anyone have any > further ideas as to what may be going on which could cause this increase in > memory management activity? Or any suggestions of where to look next? > > > -- > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
