Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22
Have you tried making your own DLL from each source and comparing them when
they are compiled the same?
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 Imanuel [my_mailings_addr...@gmx.de]
Sent: Monday, October 22, 2012 4:07 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
Hi Dan
I don't use a wrapper in the application (at least not in the test
application), and I don't issue any PRAGMAs. I have tried changing
cache_size, but that didn't change the results (I have tried 128
(default), 2000 and 10).
I don't know how to intercept calls to the VFS interface, so I guess I
can deny that question, too ;)
All I do is run the three imported dll functions: sqlite3_open,
sqlite3_exec and sqlite3_close.
The time is measured directly before and after sqlite3_exec so there
should be nothing else interfering with it.
I just tested the same command with the Firefox addon "SQLite Manager",
which uses 3.7.13. It took 67 seconds to create the index, which is way
faster than my delphi test application with 3.7.14.1, but still slower
than delphi and 3.6.22.
Also, I don't think that delphi "just makes SQLite slow", because 3.6.22
is way faster than 3.7.14.1 - so it has to be related to any change that
was made.
I'll make more tests if it is only CREATE INDEX that is slowlier, or
other commands, too.
SELECT statements on indexed columns are very fast with both, but maybe
they are simply too fast to notice any difference.
Imanuel
Am 22.10.2012 06:35, schrieb Dan Kennedy:
> On 10/21/2012 03:48 PM, Imanuel wrote:
>> is there any way to have a fast SQLite
>> 3.7.14.1 via DLL or at least to tell why it's that slow?
>
> I don't think it's the dll itself that is the problem, just
> something about the way it is used in Delphi. And I don't
> actually know anything about Delphi..
>
> Is the wrapper or the application issuing any PRAGMA statements
> to SQLite? Does the wrapper intercept calls to the VFS interface?
>
> Dan.
>
>
>
>
>> Imanuel
>>
>>
>> Am 18.10.2012 17:49, schrieb Imanuel:
>>> No, I can't - 26s vs 15s (old vs new).
>>>
>>> But when I run the test in my Delphi test application, 3.7.14.1 takes
>>> 285 seconds (tested again right now).
>>> All the time, CPU usage is 25% (on a quad core).
>>>
>>>
>>> This is my test code:
>>>
>>> sqlite3_open('test.db', handle);
>>> t0:=now();
>>> sqlite3_exec(handle, PAnsiChar('CREATE INDEX idx_namen_name ON
>>> Namen(name)'), nil, nil, nil);
>>> showmessage(floattostr((now()-t0)*86400));
>>> sqlite3_close(handle);
>>>
>>>
>>> The DLL is referenced in this unit:
>>> https://raw.github.com/plashenkov/SQLite3-Delphi-FPC/09d8674805c73d1ab0fa05832750cc0f727102f0/Source/SQLite3.pas
>>>
>>>
>>> I simply have replaced the dll without changing the linking source code
>>> to test with 3.7.14.1 - I hope that's ok.
>>>
>>> Imanuel
>>>
>>>
>>>
>>>
>>> Am 18.10.2012 16:49, schrieb Dan Kennedy:
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
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?
Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22
Hi Dan
I don't use a wrapper in the application (at least not in the test
application), and I don't issue any PRAGMAs. I have tried changing
cache_size, but that didn't change the results (I have tried 128
(default), 2000 and 10).
I don't know how to intercept calls to the VFS interface, so I guess I
can deny that question, too ;)
All I do is run the three imported dll functions: sqlite3_open,
sqlite3_exec and sqlite3_close.
The time is measured directly before and after sqlite3_exec so there
should be nothing else interfering with it.
I just tested the same command with the Firefox addon "SQLite Manager",
which uses 3.7.13. It took 67 seconds to create the index, which is way
faster than my delphi test application with 3.7.14.1, but still slower
than delphi and 3.6.22.
Also, I don't think that delphi "just makes SQLite slow", because 3.6.22
is way faster than 3.7.14.1 - so it has to be related to any change that
was made.
I'll make more tests if it is only CREATE INDEX that is slowlier, or
other commands, too.
SELECT statements on indexed columns are very fast with both, but maybe
they are simply too fast to notice any difference.
Imanuel
Am 22.10.2012 06:35, schrieb Dan Kennedy:
> On 10/21/2012 03:48 PM, Imanuel wrote:
>> is there any way to have a fast SQLite
>> 3.7.14.1 via DLL or at least to tell why it's that slow?
>
> I don't think it's the dll itself that is the problem, just
> something about the way it is used in Delphi. And I don't
> actually know anything about Delphi..
>
> Is the wrapper or the application issuing any PRAGMA statements
> to SQLite? Does the wrapper intercept calls to the VFS interface?
>
> Dan.
>
>
>
>
>> Imanuel
>>
>>
>> Am 18.10.2012 17:49, schrieb Imanuel:
>>> No, I can't - 26s vs 15s (old vs new).
>>>
>>> But when I run the test in my Delphi test application, 3.7.14.1 takes
>>> 285 seconds (tested again right now).
>>> All the time, CPU usage is 25% (on a quad core).
>>>
>>>
>>> This is my test code:
>>>
>>> sqlite3_open('test.db', handle);
>>> t0:=now();
>>> sqlite3_exec(handle, PAnsiChar('CREATE INDEX idx_namen_name ON
>>> Namen(name)'), nil, nil, nil);
>>> showmessage(floattostr((now()-t0)*86400));
>>> sqlite3_close(handle);
>>>
>>>
>>> The DLL is referenced in this unit:
>>> https://raw.github.com/plashenkov/SQLite3-Delphi-FPC/09d8674805c73d1ab0fa05832750cc0f727102f0/Source/SQLite3.pas
>>>
>>>
>>> I simply have replaced the dll without changing the linking source code
>>> to test with 3.7.14.1 - I hope that's ok.
>>>
>>> Imanuel
>>>
>>>
>>>
>>>
>>> Am 18.10.2012 16:49, schrieb Dan Kennedy:
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
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
>
Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22
On 10/21/2012 03:48 PM, Imanuel wrote:
is there any way to have a fast SQLite
3.7.14.1 via DLL or at least to tell why it's that slow?
I don't think it's the dll itself that is the problem, just
something about the way it is used in Delphi. And I don't
actually know anything about Delphi..
Is the wrapper or the application issuing any PRAGMA statements
to SQLite? Does the wrapper intercept calls to the VFS interface?
Dan.
Imanuel
Am 18.10.2012 17:49, schrieb Imanuel:
No, I can't - 26s vs 15s (old vs new).
But when I run the test in my Delphi test application, 3.7.14.1 takes
285 seconds (tested again right now).
All the time, CPU usage is 25% (on a quad core).
This is my test code:
sqlite3_open('test.db', handle);
t0:=now();
sqlite3_exec(handle, PAnsiChar('CREATE INDEX idx_namen_name ON
Namen(name)'), nil, nil, nil);
showmessage(floattostr((now()-t0)*86400));
sqlite3_close(handle);
The DLL is referenced in this unit:
https://raw.github.com/plashenkov/SQLite3-Delphi-FPC/09d8674805c73d1ab0fa05832750cc0f727102f0/Source/SQLite3.pas
I simply have replaced the dll without changing the linking source code
to test with 3.7.14.1 - I hope that's ok.
Imanuel
Am 18.10.2012 16:49, schrieb Dan Kennedy:
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
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
___
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
Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22
Sorry if I'm annoying you, but is there any way to have a fast SQLite
3.7.14.1 via DLL or at least to tell why it's that slow?
Imanuel
Am 18.10.2012 17:49, schrieb Imanuel:
> No, I can't - 26s vs 15s (old vs new).
>
> But when I run the test in my Delphi test application, 3.7.14.1 takes
> 285 seconds (tested again right now).
> All the time, CPU usage is 25% (on a quad core).
>
>
> This is my test code:
>
> sqlite3_open('test.db', handle);
> t0:=now();
> sqlite3_exec(handle, PAnsiChar('CREATE INDEX idx_namen_name ON
> Namen(name)'), nil, nil, nil);
> showmessage(floattostr((now()-t0)*86400));
> sqlite3_close(handle);
>
>
> The DLL is referenced in this unit:
> https://raw.github.com/plashenkov/SQLite3-Delphi-FPC/09d8674805c73d1ab0fa05832750cc0f727102f0/Source/SQLite3.pas
>
> I simply have replaced the dll without changing the linking source code
> to test with 3.7.14.1 - I hope that's ok.
>
> Imanuel
>
>
>
>
> Am 18.10.2012 16:49, schrieb Dan Kennedy:
>> 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
>>
>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22
No, I can't - 26s vs 15s (old vs new).
But when I run the test in my Delphi test application, 3.7.14.1 takes
285 seconds (tested again right now).
All the time, CPU usage is 25% (on a quad core).
This is my test code:
sqlite3_open('test.db', handle);
t0:=now();
sqlite3_exec(handle, PAnsiChar('CREATE INDEX idx_namen_name ON
Namen(name)'), nil, nil, nil);
showmessage(floattostr((now()-t0)*86400));
sqlite3_close(handle);
The DLL is referenced in this unit:
https://raw.github.com/plashenkov/SQLite3-Delphi-FPC/09d8674805c73d1ab0fa05832750cc0f727102f0/Source/SQLite3.pas
I simply have replaced the dll without changing the linking source code
to test with 3.7.14.1 - I hope that's ok.
Imanuel
Am 18.10.2012 16:49, schrieb Dan Kennedy:
> 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
>
> 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
Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22
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
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
Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22
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 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
Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22
Ok, here it is (45mb): http://www.file-upload.net/download-6707980/CREATE_INDEX_test.7z.html 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
Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22
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
Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22
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] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22
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. When I stumbled upon this for the first time, I experienced values of 18 vs 380 seconds but I can't reproduce them now because I don't have that version of the database any more. Is there anything I can change? Imanuel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
