And using Dan's downloads 3.7.14.1 took 30.4 seconds 3.6.22 took 40.94 (there was a lot of idle time towards the end here...disk I/O I assume) Re-did my compilation again... 3.7.14.1 took 26.8 Recompiled under Visual Studio Express 2010 "cl /O2 sqlite3.c shell.c" 3.7.14.1 took 26.2 seconds
I'm not on an SSD but that shouldn't matter much for this. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Dan Kennedy [danielk1...@gmail.com] Sent: Thursday, October 18, 2012 9:50 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22 On 10/18/2012 09:49 PM, Dan Kennedy wrote: > On 10/18/2012 03:32 PM, Imanuel wrote: >> Ok, here it is (45mb): >> http://www.file-upload.net/download-6707980/CREATE_INDEX_test.7z.html > > On Linux here 3.6.22 takes around 61 seconds. Against 23 for a new > version. Are you able to reproduce the performance regression with > these two? > > http://www.sqlite.org/sqlite-shell-win32-x86-3062200.zip > http://www.sqlite.org/sqlite-shell-win32-x86-307140100.zip Second link is incorrect. They should be: http://www.sqlite.org/sqlite-shell-win32-x86-3062200.zip http://www.sqlite.org/sqlite-shell-win32-x86-3071401.zip Dan. > > Dan. > > > > > >> >> Imanuel >> >> >> Am 18.10.2012 00:37, schrieb Imanuel: >>> No, the performance stays the same. >>> I have also tried using a big cache_size, but that didn't change >>> anything, too. >>> >>> Yes, I can share the database - it is currently uploading, I will mail >>> the link tomorrow. >>> >>> Imanuel >>> >>> >>> Am 17.10.2012 22:08, schrieb Dan Kennedy: >>>> On 10/18/2012 01:32 AM, Imanuel wrote: >>>>> Hello >>>>> >>>>> I tested this on an SSD with a database with one single table with >>>>> 5,553,534 entries: >>>>> CREATE TABLE Namen(id INTEGER PRIMARY KEY, geonameid INTEGER, lang >>>>> TEXT, >>>>> name TEXT, pref INTEGER DEFAULT 0, short INTEGER DEFAULT 0, coll >>>>> INTEGER >>>>> DEFAULT 0, historic INTEGER DEFAULT 0, sort INTEGER DEFAULT 7) >>>>> >>>>> When running this command: >>>>> CREATE INDEX idx_namen_name ON Namen(name) >>>>> >>>>> Version 3.6.22 (from here: http://indasoftware.com/sqlite/) takes 36 >>>>> seconds, while 3.7.14.1 (as DLL, too) takes 279 seconds. >>>>> Indexing the column "geonameid" makes 24 vs. 312 seconds. >>>>> Neither of the both columns are presorted. >>>> If you set "PRAGMA temp_store = memory" in 3.7.14.1 is the >>>> performance the same as in 3.6.22? >>>> >>>> Are you able to share the database? >>>> >>>> _______________________________________________ >>>> 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 >>> >> >> _______________________________________________ >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
