I used 3.7.14.1 Compiled thusly with Visual Studio Express 2008 cl /O2 sqlite3.c shell.c
CREATE INDEX idx_namen_name ON Namen(name); Took 26.6 seconds and one CPU was pegged the whole time. I'm on a 3Ghz 8-core machine. 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
