mORMot Open Source

w5m

Member

Registered: 2014-11-12

Posts: 7

Delay executing query when SQLite3 db resides in non-writable folder

Hi there,

I am using the code below to successfully execute 2 very simple SELECT queries on a SQLite3 database (away from mORMot). I recently discovered that if the database resides in a non-writable folder, the TSQLDBConnectionProperties.Execute function (and more specifically the NewThreadSafeStatementPrepared function within it) takes ~1500 milliseconds to complete for each query, whereas the same database residing in a folder with Write permissions enabled, does not experience a delay when running the same code (i.e. executing the same SELECT queries against it).

Can you think of any reasons why not having Write permission on a folder would cause this delay in query execution? Could the use of prepared statements be attempting to write something back to the folder? Is there a way to run a query without using a prepared statement?

Any thoughts on this would be gratefully received.

Many thanks,
Clive

function GetSqliteResultset(const ADB: String; const APassword: String = ''; const ASQL: String = ''): String;
var
  conn: TSQLDBSQLite3ConnectionProperties;
  db: TSQLDBSQLite3Connection;

  procedure GetResult;
  var
    rows: ISQLDBRows;
  begin
    rows := conn.Execute(ASQL, []);
    while rows.Step do
      ShowMessage(rows.ColumnString('Description') + ', ' + rows.ColumnString('Index'));
  end;

begin
  // Establish sqlite connection, execute passed query and fetch results
  conn := TSQLDBSQLite3ConnectionProperties.Create(ADB, '', '', APassword);
  try
    try
      db := conn.MainConnection as TSQLDBSQLite3Connection;
      GetResult;
    except
      ShowMessage('Error executing SQL or processing results.');
    end;
  finally
    conn.Free;
  end;
end;

procedure TForm1.But_ConnectClick(Sender: TObject);
var
  sql: String;
begin
  sql := 'SELECT TypeDescription AS ''Description'', BranchIndex AS ''Index'' FROM tblBranchOne';
  GetSqliteResultSet(Ed_DbFilename.Text, Ed_Password.Text, sql);

  sql := 'SELECT TypeDescription AS ''Description'', BranchIndex AS ''Index'' FROM tblBranchTwo';
  GetSqliteResultSet(Ed_DbFilename.Text, Ed_Password.Text, sql);
end;

Last edited by w5m (2019-07-16 09:24:42)

w5m

Member

Registered: 2014-11-12

Posts: 7

Re: Delay executing query when SQLite3 db resides in non-writable folder

I have since come across this SQLite documentation which talks about the creation of temporary files (to faciliate rollbacks amongst other things). I believe that the delay I described in my previous post is SQLite attempting to write -journal (and perhaps -wal) files to the non-writable folder where the database resides.

I have been attempting to execute...

PRAGMA journal_mode=OFF;

...but I'm not convinced it's taking effect.

I modified the TSQLDBSQLite3Connection.Connect procedure as follows...

procedure TSQLDBSQLite3Connection.Connect;
var Log: ISynLog;
begin
  Log := SynDBLog.Enter;
  Disconnect; // force fTrans=fError=fServer=fContext=nil
  fDB := TSQLDatabase.Create(UTF8ToString(Properties.ServerName),Properties.PassWord);
  fDB.ExecuteNoException('PRAGMA journal_mode=OFF;');
  //fDB.SetWalMode(true); // slower INSERT in WAL mode for huge number of rows
  inherited Connect; // notify any re-connection
end;

I have also tried modifying TSQLDBConnection.NewStatementPrepared to include this line immediately before the function result is assigned in both places...

Stmt.Execute('PRAGMA journal_mode=OFF;', False);

This doesn't seem to make any difference to the delay I'm experiencing, so I'm unclear whether the setting hasn't taken effect, or perhaps it has but isn't the source of the delay.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,242

Website

Re: Delay executing query when SQLite3 db resides in non-writable folder

I guess you should use SQLITE_OPEN_READONLY as parameter in the aOpenV2Flags optional parameter of the TSQLDataBase.Create constructor.
As it is documented.

w5m

Member

Registered: 2014-11-12

Posts: 7

Re: Delay executing query when SQLite3 db resides in non-writable folder

So I've run ProcessMonitor and can see that attempts are still being made to create -journal and -wal files in the same directory as the database, even though I've attempted to issue each of the following in separate test runs...

PRAGMA journal_mode=MEMORY;
PRAGMA journal_mode=OFF;

Any ideas why these are not being applied?

w5m

Member

Registered: 2014-11-12

Posts: 7

Re: Delay executing query when SQLite3 db resides in non-writable folder

A huge thank you to you Arnaud. As you probably realised, I hadn't stumbled across SQLITE_OPEN_READONLY before. I can confirm that your suggestion has resolved the delay.