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 100000).
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
>>>>> 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
