libsql-discussion

[libsql] sqlite 80x faster on transactions

[<re...@du...>]

Hi,

Inspired by cross-platform usage of sqlite vs transaction and threading 
issues, i've been looking why sqlite is so slow with transactions on my 
system (XPsp2, amd2500+, 250GB HD, NTFS). A transaction takes about 80ms 
on my system, with lot of disk head movements involved.
Since i need sqlite especially to be fast for one of my projects (speed 
is more important than possible data loss in case of an operating system 
crash) i've been investigating how to speed up sqlite. To accomplish 
that goal, i've recompiled sqlite3.dll and added an extra pragma to 
sqlite that disables disk flushing on each transaction.

If you want to test (please do not use for production at the moment!), 
you can download a modified sqlite dll from sourceforge:
http://sourceforge.net/project/showfiles.php?group_id=103463&package_id=131328

to disable disk flush, set the pragma win32_flush_buffers to off (it 
defaults to on):
PRAGMA win32_flush_buffers=off

below the readme of the zip, including a simple benchmark.

regards,

rene

-------------------------------------

Important information on this library
This is a custom build of SQLite 3 dynamic link library (dll) for windows.

WARNING: THIS BUILD CANNOT GUARANTEE SQLite ACID behaviour in case of OS 
crash!

Version: Sqlite 3.27 (modified)
Compiler: dev-c++ 4.9.9.2 (minGW)
Modifications: Added pragma for disabling file buffer flush on win32 
(Rene Tegel)

Usage:
Set the sqlite pragma win32_flush_buffers to on or off (defaults to on 
(safe)):
PRAGMA win32_flush_buffers=off

Motivation:
SQLite is very slow and disk-intensive when completing a transaction. 
This is mainly causes by calling the windows api FlushFileBuffers which 
does, probably, a lot more than intended (flushing file buffer to disk).
In case of an application crash, there should be no difference in ACID. 
In case of an operating system crash or power failure, the effects may 
be noticable.
However, by disabling buffer flush a transaction is handled about 80 
times faster on my system! Benchmarks below.

Recommended use:
NTFS file systems
Data logging applications
Other applications that involve numerous transactions but need perfarmance.
Make a backup of your database if you can't risk total data loss but 
still need the performance

Important notice:
I am not resposible for data loss. What is more: Richard Hipp (SQLite 
main developer) is not responsible for data loss or any modifications in 
this library. Do NOT contach him and/or the sqlite mailing list for 
issues caused by flushbuffers switched to off. The rest of the library 
should be identical.

Future compilations:
I will try to patch, compile and release newer versions. After testing, 
i may contact the sqlite mailing list and ask Richard Hipp to include 
the patch.



Benchmarks:
AS tested with sqlite 3.2.7 compiled with dev-c++ on win32
platform: win XP AMD 2500+



sqlite327 standard, non-optimized:

inserting 1 records
Seperate transactions 79 ms
Single transaction 78 ms

inserting 10 records
Seperate transactions 672 ms
Single transaction 79 ms

inserting 100 records
Seperate transactions 6719 ms
Single transaction 78 ms

inserting 1000 records
Seperate transactions 77359 ms
Single transaction 156 ms

inserting 10000 records
Seperate transactions 789453 ms
Single transaction 781 ms



sqlite3.27 optimized, disabled buffer flush:

inserting 1 records
Seperate transactions 0 ms
Single transaction 0 ms

inserting 10 records
Seperate transactions 16 ms
Single transaction 0 ms

inserting 100 records
Seperate transactions 156 ms
Single transaction 0 ms

inserting 1000 records
Seperate transactions 969 ms
Single transaction 78 ms

inserting 10000 records
Seperate transactions 9828 ms
Single transaction 594 ms

Re: [libsql] sqlite 80x faster on transactions

[<re...@du...>]

Hi,

I should have read sqlite's documentation better. Setting pragma=20
synchronous to OFF does about the same (and thus speeding up=20
transactions dramastically). Sorry for any confusion.

Rene



Ren=E9 Tegel wrote:

> Hi,
>
> Inspired by cross-platform usage of sqlite vs transaction and=20
> threading issues, i've been looking why sqlite is so slow with=20
> transactions on my system (XPsp2, amd2500+, 250GB HD, NTFS). A=20
> transaction takes about 80ms on my system, with lot of disk head=20
> movements involved.
> Since i need sqlite especially to be fast for one of my projects=20
> (speed is more important than possible data loss in case of an=20
> operating system crash) i've been investigating how to speed up=20
> sqlite. To accomplish that goal, i've recompiled sqlite3.dll and added=20
> an extra pragma to sqlite that disables disk flushing on each=20
> transaction.
>
> If you want to test (please do not use for production at the moment!),=20
> you can download a modified sqlite dll from sourceforge:
> http://sourceforge.net/project/showfiles.php?group_id=3D103463&package_=
id=3D131328=20
>
>
> to disable disk flush, set the pragma win32_flush_buffers to off (it=20
> defaults to on):
> PRAGMA win32_flush_buffers=3Doff
>
> below the readme of the zip, including a simple benchmark.
>
> regards,
>
> rene
>
> -------------------------------------
>
> Important information on this library
> This is a custom build of SQLite 3 dynamic link library (dll) for=20
> windows.
>
> WARNING: THIS BUILD CANNOT GUARANTEE SQLite ACID behaviour in case of=20
> OS crash!
>
> Version: Sqlite 3.27 (modified)
> Compiler: dev-c++ 4.9.9.2 (minGW)
> Modifications: Added pragma for disabling file buffer flush on win32=20
> (Rene Tegel)
>
> Usage:
> Set the sqlite pragma win32_flush_buffers to on or off (defaults to on=20
> (safe)):
> PRAGMA win32_flush_buffers=3Doff
>
> Motivation:
> SQLite is very slow and disk-intensive when completing a transaction.=20
> This is mainly causes by calling the windows api FlushFileBuffers=20
> which does, probably, a lot more than intended (flushing file buffer=20
> to disk).
> In case of an application crash, there should be no difference in=20
> ACID. In case of an operating system crash or power failure, the=20
> effects may be noticable.
> However, by disabling buffer flush a transaction is handled about 80=20
> times faster on my system! Benchmarks below.
>
> Recommended use:
> NTFS file systems
> Data logging applications
> Other applications that involve numerous transactions but need=20
> perfarmance.
> Make a backup of your database if you can't risk total data loss but=20
> still need the performance
>
> Important notice:
> I am not resposible for data loss. What is more: Richard Hipp (SQLite=20
> main developer) is not responsible for data loss or any modifications=20
> in this library. Do NOT contach him and/or the sqlite mailing list for=20
> issues caused by flushbuffers switched to off. The rest of the library=20
> should be identical.
>
> Future compilations:
> I will try to patch, compile and release newer versions. After=20
> testing, i may contact the sqlite mailing list and ask Richard Hipp to=20
> include the patch.
>
>
>
> Benchmarks:
> AS tested with sqlite 3.2.7 compiled with dev-c++ on win32
> platform: win XP AMD 2500+
>
>
>
> sqlite327 standard, non-optimized:
>
> inserting 1 records
> Seperate transactions 79 ms
> Single transaction 78 ms
>
> inserting 10 records
> Seperate transactions 672 ms
> Single transaction 79 ms
>
> inserting 100 records
> Seperate transactions 6719 ms
> Single transaction 78 ms
>
> inserting 1000 records
> Seperate transactions 77359 ms
> Single transaction 156 ms
>
> inserting 10000 records
> Seperate transactions 789453 ms
> Single transaction 781 ms
>
>
>
> sqlite3.27 optimized, disabled buffer flush:
>
> inserting 1 records
> Seperate transactions 0 ms
> Single transaction 0 ms
>
> inserting 10 records
> Seperate transactions 16 ms
> Single transaction 0 ms
>
> inserting 100 records
> Seperate transactions 156 ms
> Single transaction 0 ms
>
> inserting 1000 records
> Seperate transactions 969 ms
> Single transaction 78 ms
>
> inserting 10000 records
> Seperate transactions 9828 ms
> Single transaction 594 ms
>
>
>
>
>
>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by:
> Power Architecture Resource Center: Free content, downloads, discussion=
s,
> and more. http://solutions.newsforge.com/ibmarch.tmpl
> _______________________________________________
> libsql-discussion mailing list
> lib...@li...
> https://lists.sourceforge.net/lists/listinfo/libsql-discussion

[libsql] Advice on SQL DELETEs inside a SELECT result loop

[Andy C. <and...@bt...>]

Hi,

I've come across what I expect is operator error on my part, so I was
hoping for some quick advice as to the direction I should be going. The SQL task I'm performing I suspect can
be re-written as a compound SQL statement, but while I learn SQL fully, I'm performing it in a couple of stages in Delphi.
This is where I have a query.....

If I do a "SELECT track_id FROM main WHERE checked='N'" and store the resulting list of numbers in an array or list, then
perform the delete command "DELETE FROM main WHERE track_id=%d", using this list of numbers, this works fine with no problems.

But if I try to write it all in one loop, it always fails to perform the DELETE commands.
No errors reported and it still runs down the full list of results inside the loop. 
Both ways below fail in the same way..... (maybe the odd typo as I'm writing partially from memory)



Using one DB object, but 2 result sets
--------------------------------------

rs1 := TResultSet.Create (Ldb);
rs2 := TResultSet.Create (Ldb);
Ldb.StartTransaction;
rs1.FormatExecute('SELECT track_id FROM main WHERE checked=%s',['N']);
while rs1.FetchRow do begin
   rs2.FormatQuery('DELETE FROM mp3_stats WHERE stats_id=%d',[temp]);
   rs2.FormatQuery('DELETE FROM main WHERE track_id=%d',[temp]);
end;
Ldb.Commit;
rs2.free;
rs1.free;




Using two DB objects 
--------------------
Ldb2.StartTransaction;
handle:=Ldb.FormatExecute('SELECT track_id FROM main WHERE checked=%s',['N']);
while Ldb.FetchRow(handle,row) do begin
   temp:=row['track_id'];
   Ldb2.FormatQuery('DELETE FROM mp3_stats WHERE stats_id=%d',[temp]);
   Ldb2.FormatQuery('DELETE FROM main WHERE track_id=%d',[temp]);
end;
Ldb.FreeResult(handle);
Ldb2.Commit;




Who'd like be first in line to call me a lemon ;-)
Thanks all.

Re: [libsql] Advice on SQL DELETEs inside a SELECT result loop

[<re...@du...>]

Hi,

[Assuming you use SQLite]
You appear to delete records from the same table as you are fetching 
from (i.e. have an open SQLite VM handle). Allowing this would lead to 
inconsistent queries.

The error in libsql is that you don't get an error message (as you say). 
I will look into this issue. Just fetch the rows in advance (using 
FormatQuery) and then delete them. Also, it appears to me that you mix 
stats_id and track_id?

regards,

Rene

Andy Chandler wrote:

>Hi,
>
>I've come across what I expect is operator error on my part, so I was
>hoping for some quick advice as to the direction I should be going. The SQL task I'm performing I suspect can
>be re-written as a compound SQL statement, but while I learn SQL fully, I'm performing it in a couple of stages in Delphi.
>This is where I have a query.....
>
>If I do a "SELECT track_id FROM main WHERE checked='N'" and store the resulting list of numbers in an array or list, then
>perform the delete command "DELETE FROM main WHERE track_id=%d", using this list of numbers, this works fine with no problems.
>
>But if I try to write it all in one loop, it always fails to perform the DELETE commands.
>No errors reported and it still runs down the full list of results inside the loop. 
>Both ways below fail in the same way..... (maybe the odd typo as I'm writing partially from memory)
>
>
>
>Using one DB object, but 2 result sets
>--------------------------------------
>
>rs1 := TResultSet.Create (Ldb);
>rs2 := TResultSet.Create (Ldb);
>Ldb.StartTransaction;
>rs1.FormatExecute('SELECT track_id FROM main WHERE checked=%s',['N']);
>while rs1.FetchRow do begin
>   rs2.FormatQuery('DELETE FROM mp3_stats WHERE stats_id=%d',[temp]);
>   rs2.FormatQuery('DELETE FROM main WHERE track_id=%d',[temp]);
>end;
>Ldb.Commit;
>rs2.free;
>rs1.free;
>
>
>
>
>Using two DB objects 
>--------------------
>Ldb2.StartTransaction;
>handle:=Ldb.FormatExecute('SELECT track_id FROM main WHERE checked=%s',['N']);
>while Ldb.FetchRow(handle,row) do begin
>   temp:=row['track_id'];
>   Ldb2.FormatQuery('DELETE FROM mp3_stats WHERE stats_id=%d',[temp]);
>   Ldb2.FormatQuery('DELETE FROM main WHERE track_id=%d',[temp]);
>end;
>Ldb.FreeResult(handle);
>Ldb2.Commit;
>
>
>
>
>Who'd like be first in line to call me a lemon ;-)
>Thanks all.
>
>
>
>-------------------------------------------------------
>This SF.Net email is sponsored by xPML, a groundbreaking scripting language
>that extends applications into web and mobile media. Attend the live webcast
>and join the prime developer group breaking into this new coding territory!
>http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
>_______________________________________________
>libsql-discussion mailing list
>lib...@li...
>https://lists.sourceforge.net/lists/listinfo/libsql-discussion
>  
>

Re[2]: [libsql] Advice on SQL DELETEs inside a SELECT result loop

[Andy C. <and...@bt...>]

Hi Rene,


   You're right, I'm using SQLite!
I can see my mistake now in assuming that the two methods below would be OK
because I was reading the results from libsql's buffer and not from SQLite.
Of course, not the case with Execute (versus Query).

In my test tables, I have named the id's differently, but in the final
database, the track_id will be consistent across tables as one single
table would have too many columns to be comfortable to handle.

Thanks again, very much appreciated.
I'll look at rewriting this shortly.


Rgs,
Andy




Sunday, March 12, 2006, 9:35:42 AM, you wrote:

> Hi,

> [Assuming you use SQLite]
> You appear to delete records from the same table as you are fetching
> from (i.e. have an open SQLite VM handle). Allowing this would lead to
> inconsistent queries.

> The error in libsql is that you don't get an error message (as you say).
> I will look into this issue. Just fetch the rows in advance (using 
> FormatQuery) and then delete them. Also, it appears to me that you mix
> stats_id and track_id?

> regards,

> Rene

> Andy Chandler wrote:

>>Hi,
>>
>>I've come across what I expect is operator error on my part, so I was
>>hoping for some quick advice as to the direction I should be
>>going. The SQL task I'm performing I suspect can
>>be re-written as a compound SQL statement, but while I learn SQL
>>fully, I'm performing it in a couple of stages in Delphi.
>>This is where I have a query.....
>>
>>If I do a "SELECT track_id FROM main WHERE checked='N'" and store
>>the resulting list of numbers in an array or list, then
>>perform the delete command "DELETE FROM main WHERE track_id=%d",
>>using this list of numbers, this works fine with no problems.
>>
>>But if I try to write it all in one loop, it always fails to perform the DELETE commands.
>>No errors reported and it still runs down the full list of results inside the loop.
>>Both ways below fail in the same way..... (maybe the odd typo as
>>I'm writing partially from memory)
>>
>>
>>
>>Using one DB object, but 2 result sets
>>--------------------------------------
>>
>>rs1 := TResultSet.Create (Ldb);
>>rs2 := TResultSet.Create (Ldb);
>>Ldb.StartTransaction;
>>rs1.FormatExecute('SELECT track_id FROM main WHERE checked=%s',['N']);
>>while rs1.FetchRow do begin
>>   rs2.FormatQuery('DELETE FROM mp3_stats WHERE stats_id=%d',[temp]);
>>   rs2.FormatQuery('DELETE FROM main WHERE track_id=%d',[temp]);
>>end;
>>Ldb.Commit;
>>rs2.free;
>>rs1.free;
>>
>>
>>
>>
>>Using two DB objects 
>>--------------------
>>Ldb2.StartTransaction;
>>handle:=Ldb.FormatExecute('SELECT track_id FROM main WHERE checked=%s',['N']);
>>while Ldb.FetchRow(handle,row) do begin
>>   temp:=row['track_id'];
>>   Ldb2.FormatQuery('DELETE FROM mp3_stats WHERE stats_id=%d',[temp]);
>>   Ldb2.FormatQuery('DELETE FROM main WHERE track_id=%d',[temp]);
>>end;
>>Ldb.FreeResult(handle);
>>Ldb2.Commit;
>>
>>
>>
>>
>>Who'd like be first in line to call me a lemon ;-)
>>Thanks all.
>>
>>
>>
>>-------------------------------------------------------
>>This SF.Net email is sponsored by xPML, a groundbreaking scripting language
>>that extends applications into web and mobile media. Attend the live webcast
>>and join the prime developer group breaking into this new coding territory!
>>http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
>>_______________________________________________
>>libsql-discussion mailing list
>>lib...@li...
>>https://lists.sourceforge.net/lists/listinfo/libsql-discussion
>>  
>>



> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting language
> that extends applications into web and mobile media. Attend the live webcast
> and join the prime developer group breaking into this new coding territory!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
> _______________________________________________
> libsql-discussion mailing list
> lib...@li...
> https://lists.sourceforge.net/lists/listinfo/libsql-discussion