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