mORMot and Open Source friends
Check-in [18d0522f29]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
SHA1:18d0522f29c5fa36140863e9593dc930bbebaa04
Date: 2013-06-14 07:45:39
User: abouchez
Comment:added TSQLDataBase.CacheSize and LockingMode properties for performance tuning
Tags And Properties
Context
2013-06-14
07:45
[6cd5759660] added TSQLDBSQLite3Connection.LockingMode property for performance tuning (user: abouchez, tags: trunk)
07:45
[18d0522f29] added TSQLDataBase.CacheSize and LockingMode properties for performance tuning (user: abouchez, tags: trunk)
2013-06-13
07:16
[93292f21a4] proper function TGDIPages.GetLineHeight() computation - from kln feedback (user: abouchez, tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to SynSQLite3.pas.

137
138
139
140
141
142
143

144
145
146
147
148
149
150
....
1741
1742
1743
1744
1745
1746
1747

















1748
1749
1750
1751
1752
1753
1754
....
2125
2126
2127
2128
2129
2130
2131




2132
2133
2134
2135
2136
2137
2138
....
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
....
2355
2356
2357
2358
2359
2360
2361

















2362
2363
2364
2365
2366
2367
2368
....
3242
3243
3244
3245
3246
3247
3248


3249
3250
3251
3252
3253
3254
3255
....
3256
3257
3258
3259
3260
3261
3262












3263
3264
3265
3266


3267
3268
3269
3270
3271
3272
3273
3274















3275
3276
3277
3278
3279
3280
3281
  - TSQLDataBase.DBClose returns now the sqlite3_close() status code
  - "rowCount": is added in TSQLRequest.Execute at the end of the non-expanded
    JSON content, if needed - improves client parsing performance
  - added TSQLRequest.FieldDeclaredType() method
  - added TSQLRequest.BindS()/FieldS()/FieldDeclaredTypeS() methods for direct
    string process
  - now TSQLRequest.Bind(col,'') will bind '' void text instead of null value

  - added TSQLDatabase.LogResultMaximumSize property to reduce logged extend
  - added TSQLDataBase.LockAndFlushCache method to be used instead of Lock('')
  - set SQLITE_TRANSIENT_VIRTUALTABLE constant, to circumvent Win64 Sqlite3 bug
  - TSQLStatementCached.Prepare won't call BindReset, since it is not mandatory;
    see http://hoogli.com/items/Avoid_sqlite3_clear_bindings().html
  - fixed ticket [f79ff5714b] about potential finalization issues as .bpl in IDE

................................................................................
  // it has handed data off to the operating system. If the application running
  // SQLite crashes, the data will be safe, but the database might become
  // corrupted if the operating system crashes or the computer loses power
  // before that data has been written to the disk surface. On the other hand,
  // some operations are as much as 50 or more times faster with synchronous OFF.
  TSQLSynchronousMode = (smOff, smNormal, smFull);


















  TSQLDatabase = class;

  TSQLBlobStream = class;

  PSQLRequest = ^TSQLRequest;

  /// wrapper to a SQLite3 request
................................................................................
    procedure SetBusyTimeout(const ms: Integer);
    function GetUserVersion: cardinal;
    procedure SetUserVersion(const Value: cardinal);
    procedure SetWALMode(Value: Boolean);
    function GetWALMode: boolean;
    procedure SetSynchronous(const Value: TSQLSynchronousMode);
    function GetSynchronous: TSQLSynchronousMode;




  public
    /// enter the TRTLCriticalSection: called before any DB access
    // - provide the SQL statement about to be executed: handle proper caching
    // - is the SQL statement is void, assume a SELECT statement (no cache flush)
    procedure Lock(const aSQL: RawUTF8);
    /// flush the internal statement cache, and enter the TRTLCriticalSection
    procedure LockAndFlushCache;
................................................................................
    // - this pointer is thread-safe updated, inside a critical section
    property InternalState: PCardinal read fInternalState write fInternalState;
    /// return TRUE if a Transaction begun
    property TransactionActive: boolean read fTransactionActive;
    /// sets a busy handler that sleeps for a specified amount of time
    // (in milliseconds) when a table is locked, before returning an error
    property BusyTimeout: Integer read fBusyTimeout write SetBusyTimeout;
    {$ifdef WITHLOG}
    {/ access to the log instance associated with this SQLite3 database engine }
    property Log: TSynLog read fLog;
    /// sets a maximum size (in bytes) to be logged as sllResult rows
    // - by default, is set to 512 bytes, which sounds a good compromise
    // since it does not make sense to log all the JSON content retrieved from
    // the database engine, when a huge SELECT is executed
    property LogResultMaximumSize: integer read fLogResultMaximumSize write fLogResultMaximumSize;
    {$endif}
    {/ query or change the Write-Ahead Logging mode for the database
      - beginning with version 3.7 of the SQLite3 engine, a new "Write-Ahead Log"
        option (hereafter referred to as "WAL") is optionaly available
      - WAL might be very slightly slower (perhaps 1% or 2% slower) than the
        traditional rollback-journal approach in applications that do mostly reads
        and seldom write; but WAL provides more concurrency as readers do not block
        writers and a writer does not block readers. Reading and writing can
................................................................................
    /// query or change the SQlite3 file-based syncrhonization mode, i.e. the
    // way it waits for the data to be flushed on hard drive
    // - default smFull is very slow, but achieve 100% ACID behavior
    // - smNormal is faster, and safe until a catastrophic hardware failure occurs
    // - smOff is the fastest, data should be safe if the application crashes,
    // but database file may be corrupted in case of failure at the wrong time
    property Synchronous: TSQLSynchronousMode read GetSynchronous write SetSynchronous;

















    {/ retrieve or set the user_version stored in the SQLite3 database file
      - user-version is a 32-bit signed integer stored in the database header
      - it can be used to change the database in case of format upgrade (e.g.
        refresh some hand-made triggers) }
    property user_version: cardinal read GetUserVersion write SetUserVersion;
  end;

................................................................................
  sqlite3.create_function_v2(DB,'RAWUTF8DYNARRAYCONTAINSCASE',2,SQLITE_ANY,nil,
    InternalRawUTF8DynArray,nil,nil,nil);
  sqlite3.create_function_v2(DB,'RAWUTF8DYNARRAYCONTAINSNOCASE',2,SQLITE_ANY,
    @UTF8ILComp,InternalRawUTF8DynArray,nil,nil,nil);
  // reallocate all TSQLDataBaseSQLFunction for re-Open (TSQLRestServerDB.Backup)
  for i := 0 to fSQLFunctions.Count-1 do
    TSQLDataBaseSQLFunction(fSQLFunctions.List[i]).CreateFunction(DB);


end;


function TSQLDataBase.GetUserVersion: cardinal;
var tmp: Int64;
begin
  ExecuteNoException('PRAGMA user_version',tmp);
................................................................................
  result := tmp;
end;

procedure TSQLDataBase.SetUserVersion(const Value: cardinal);
begin
  ExecuteNoException('PRAGMA user_version='+Int32ToUTF8(Value));
end;













procedure TSQLDataBase.SetSynchronous(const Value: TSQLSynchronousMode);
begin
  ExecuteNoException('PRAGMA synchronous='+Int32ToUTF8(ord(Value)));


end;

function TSQLDataBase.GetSynchronous: TSQLSynchronousMode;
var tmp: Int64;
begin
  ExecuteNoException('PRAGMA synchronous ',tmp);
  result := TSQLSynchronousMode(tmp);
end;
















procedure TSQLDataBase.SetWALMode(Value: Boolean);
const CMD: array[boolean] of RawUTF8 = ('DELETE;','WAL;');
begin
  ExecuteNoException('PRAGMA journal_mode='+CMD[value]);
end;








>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>







 







|
|
|
|
|
|
|
|
|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>







 







>
>
>
>
>
>
>
>
>
>
>
>




>
>








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
....
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
....
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
....
2348
2349
2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
....
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
....
3281
3282
3283
3284
3285
3286
3287
3288
3289
3290
3291
3292
3293
3294
3295
3296
....
3297
3298
3299
3300
3301
3302
3303
3304
3305
3306
3307
3308
3309
3310
3311
3312
3313
3314
3315
3316
3317
3318
3319
3320
3321
3322
3323
3324
3325
3326
3327
3328
3329
3330
3331
3332
3333
3334
3335
3336
3337
3338
3339
3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
  - TSQLDataBase.DBClose returns now the sqlite3_close() status code
  - "rowCount": is added in TSQLRequest.Execute at the end of the non-expanded
    JSON content, if needed - improves client parsing performance
  - added TSQLRequest.FieldDeclaredType() method
  - added TSQLRequest.BindS()/FieldS()/FieldDeclaredTypeS() methods for direct
    string process
  - now TSQLRequest.Bind(col,'') will bind '' void text instead of null value
  - added TSQLDataBase.CacheSize and LockingMode properties for performance tuning
  - added TSQLDatabase.LogResultMaximumSize property to reduce logged extend
  - added TSQLDataBase.LockAndFlushCache method to be used instead of Lock('')
  - set SQLITE_TRANSIENT_VIRTUALTABLE constant, to circumvent Win64 Sqlite3 bug
  - TSQLStatementCached.Prepare won't call BindReset, since it is not mandatory;
    see http://hoogli.com/items/Avoid_sqlite3_clear_bindings().html
  - fixed ticket [f79ff5714b] about potential finalization issues as .bpl in IDE

................................................................................
  // it has handed data off to the operating system. If the application running
  // SQLite crashes, the data will be safe, but the database might become
  // corrupted if the operating system crashes or the computer loses power
  // before that data has been written to the disk surface. On the other hand,
  // some operations are as much as 50 or more times faster with synchronous OFF.
  TSQLSynchronousMode = (smOff, smNormal, smFull);

  /// available file-level database connection locking-mode
  // - lmNormal locking-mode (the default unless overridden at compile-time using
  // SQLITE_DEFAULT_LOCKING_MODE), a database connection unlocks the database
  // file at the conclusion of each read or write transaction.
  // - when the locking-mode is set to lmExclusive, the database connection
  // never releases file-locks. The first time the database is read in
  // lmExclusive mode, a shared lock is obtained and held. The first time the
  // database is written, an exclusive lock is obtained and held. Database locks
  // obtained by a connection in lmExclusive mode may be released either by
  // closing the database connection, or by setting the locking-mode back to
  // lmNormal using this pragma and then accessing the database file (for read
  // or write). Simply setting the locking-mode to lmNormal is not enough - locks
  // are not released until the next time the database file is accessed.
  // - lmExclusive gives much better write performance, and could be used when
  // needed, in case of a heavy loaded mORMot server
  TSQLLockingMode = (lmNormal, lmExclusive);

  TSQLDatabase = class;

  TSQLBlobStream = class;

  PSQLRequest = ^TSQLRequest;

  /// wrapper to a SQLite3 request
................................................................................
    procedure SetBusyTimeout(const ms: Integer);
    function GetUserVersion: cardinal;
    procedure SetUserVersion(const Value: cardinal);
    procedure SetWALMode(Value: Boolean);
    function GetWALMode: boolean;
    procedure SetSynchronous(const Value: TSQLSynchronousMode);
    function GetSynchronous: TSQLSynchronousMode;
    procedure SetLockingMode(const Value: TSQLLockingMode);
    function GetLockingMode: TSQLLockingMode;
    function GetCacheSize: cardinal;
    procedure SetCacheSize(const Value: cardinal);
  public
    /// enter the TRTLCriticalSection: called before any DB access
    // - provide the SQL statement about to be executed: handle proper caching
    // - is the SQL statement is void, assume a SELECT statement (no cache flush)
    procedure Lock(const aSQL: RawUTF8);
    /// flush the internal statement cache, and enter the TRTLCriticalSection
    procedure LockAndFlushCache;
................................................................................
    // - this pointer is thread-safe updated, inside a critical section
    property InternalState: PCardinal read fInternalState write fInternalState;
    /// return TRUE if a Transaction begun
    property TransactionActive: boolean read fTransactionActive;
    /// sets a busy handler that sleeps for a specified amount of time
    // (in milliseconds) when a table is locked, before returning an error
    property BusyTimeout: Integer read fBusyTimeout write SetBusyTimeout;
    /// auery or change the suggested maximum number of database disk pages
    // that SQLite will hold in memory at once per open database file
    // - default suggested cache size is 2000 pages
    // - when you change the cache size using the cache_size pragma, the change
    // only endures for the current session. The cache size reverts to the
    // default value when the database is closed and reopened
    // - we do not handle negative values here (i.e. KB of RAM), since it won't
    // work if the linked SQLite3 library is version 3.7.9 and earlier 
    property CacheSize: cardinal read GetCacheSize write SetCacheSize;
    {/ query or change the Write-Ahead Logging mode for the database
      - beginning with version 3.7 of the SQLite3 engine, a new "Write-Ahead Log"
        option (hereafter referred to as "WAL") is optionaly available
      - WAL might be very slightly slower (perhaps 1% or 2% slower) than the
        traditional rollback-journal approach in applications that do mostly reads
        and seldom write; but WAL provides more concurrency as readers do not block
        writers and a writer does not block readers. Reading and writing can
................................................................................
    /// query or change the SQlite3 file-based syncrhonization mode, i.e. the
    // way it waits for the data to be flushed on hard drive
    // - default smFull is very slow, but achieve 100% ACID behavior
    // - smNormal is faster, and safe until a catastrophic hardware failure occurs
    // - smOff is the fastest, data should be safe if the application crashes,
    // but database file may be corrupted in case of failure at the wrong time
    property Synchronous: TSQLSynchronousMode read GetSynchronous write SetSynchronous;
    /// query or change the SQlite3 file-based locking mode, i.e. the
    // way it locks the file
    // - default lmNormal is ACID and safe
    // - lmExclusive gives better performance in case of a number of write
    // transactions, so can be used to release a mORMot server power: but you
    // won't be able to access the database file from outside the process (like
    // a "normal" database engine)
    property LockingMode: TSQLLockingMode read GetLockingMode write SetLockingMode;
    {$ifdef WITHLOG}
    {/ access to the log instance associated with this SQLite3 database engine }
    property Log: TSynLog read fLog;
    /// sets a maximum size (in bytes) to be logged as sllResult rows
    // - by default, is set to 512 bytes, which sounds a good compromise
    // since it does not make sense to log all the JSON content retrieved from
    // the database engine, when a huge SELECT is executed
    property LogResultMaximumSize: integer read fLogResultMaximumSize write fLogResultMaximumSize;
    {$endif}
    {/ retrieve or set the user_version stored in the SQLite3 database file
      - user-version is a 32-bit signed integer stored in the database header
      - it can be used to change the database in case of format upgrade (e.g.
        refresh some hand-made triggers) }
    property user_version: cardinal read GetUserVersion write SetUserVersion;
  end;

................................................................................
  sqlite3.create_function_v2(DB,'RAWUTF8DYNARRAYCONTAINSCASE',2,SQLITE_ANY,nil,
    InternalRawUTF8DynArray,nil,nil,nil);
  sqlite3.create_function_v2(DB,'RAWUTF8DYNARRAYCONTAINSNOCASE',2,SQLITE_ANY,
    @UTF8ILComp,InternalRawUTF8DynArray,nil,nil,nil);
  // reallocate all TSQLDataBaseSQLFunction for re-Open (TSQLRestServerDB.Backup)
  for i := 0 to fSQLFunctions.Count-1 do
    TSQLDataBaseSQLFunction(fSQLFunctions.List[i]).CreateFunction(DB);
  // tune up execution speed
  ExecuteNoException('PRAGMA cache_size=10000');
end;


function TSQLDataBase.GetUserVersion: cardinal;
var tmp: Int64;
begin
  ExecuteNoException('PRAGMA user_version',tmp);
................................................................................
  result := tmp;
end;

procedure TSQLDataBase.SetUserVersion(const Value: cardinal);
begin
  ExecuteNoException('PRAGMA user_version='+Int32ToUTF8(Value));
end;

function TSQLDataBase.GetCacheSize: cardinal;
var tmp: Int64;
begin
  ExecuteNoException('PRAGMA cache_size',tmp);
  result := tmp;
end;

procedure TSQLDataBase.SetCacheSize(const Value: cardinal);
begin
  ExecuteNoException('PRAGMA cache_size='+Int32ToUTF8(Value));
end;

procedure TSQLDataBase.SetSynchronous(const Value: TSQLSynchronousMode);
begin
  ExecuteNoException('PRAGMA synchronous='+Int32ToUTF8(ord(Value)));
  if Value=smOff then
    ExecuteNoException('PRAGMA locking_mode=EXCLUSIVE');
end;

function TSQLDataBase.GetSynchronous: TSQLSynchronousMode;
var tmp: Int64;
begin
  ExecuteNoException('PRAGMA synchronous ',tmp);
  result := TSQLSynchronousMode(tmp);
end;

procedure TSQLDataBase.SetLockingMode(const Value: TSQLLockingMode);
const CMD: array[TSQLLockingMode] of RawUTF8 = ('NORMAL;','EXCLUSIVE;');
begin
  ExecuteNoException('PRAGMA locking_mode='+CMD[value]);
end;

function TSQLDataBase.GetLockingMode: TSQLLockingMode;
var tmp: RawUTF8;
begin
  ExecuteNoException('PRAGMA locking_mode',tmp);
  if IdemPropNameU(tmp,'EXCLUSIVE') then
    result := lmExclusive else
    result := lmNormal;
end;

procedure TSQLDataBase.SetWALMode(Value: Boolean);
const CMD: array[boolean] of RawUTF8 = ('DELETE;','WAL;');
begin
  ExecuteNoException('PRAGMA journal_mode='+CMD[value]);
end;