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
