[Lazarus] RE : SQLite performance problem. Simple query takes toolong.

[Howard Lee Harkness]

Thank you for your reply. I found it very interesting. I switched the
code that I posted to the following (I refactored this so that I could
call from several different places, for inserts, updates, and
deletes):

procedure TfrmMain.ExecSQL(sql:string);
begin
     FormDatabase.SQLite3Connection.ExecuteDirect(sql);
     FormDatabase.SQLite3Connection.ExecuteDirect('commit');
     FormDatabase.SQLite3Connection.ExecuteDirect('begin transaction');
end;

I suppose that I could use a query component in there, but I don't
know what difference that would make (this code is not presenting any
performance issues; see below), and I'm a bit confused about the
proper usage. There are some properties of TSQLQuery for which I have
been unable to locate usage examples.

DeleteSQL
InsertSQL
UpdateSQL

I also don't know how to use the IndexDefs, IndexFieldNames, or
IndexName properties. Every permutation of guesses I've tried either
get deleted by the IDE, or don't actually change anything that I can
detect. I have found some documentation on
http://free-pascal-general.1045716.n5.nabble.com/New-local-indexes-support-for-sqldb-td2817867.html
which might shed some light on those parameters.

A search on TSQLQuery.DeleteSQL brought up a reference to
http://community.freepascal.org:10000/bboards/message?message_id=275621&forum_id=24081
which partially answers a question *I* posed 4 years ago on that topic
(that was for a prior iteration of this same program, which I managed
to kluge together, then not look at again until last week). I see that
the wiki was updated to include a mention of the fact that DeleteSQL,
InsertSQL, and UpdateSQL can take parameters -- which is helpful
information (parameters can help performance), but the performance hit
that I am seeing is happening in a simple lookup (for which the
transaction stuff is not needed). Here's the code:

 qClients.Close;
 qClients.SQL.Text:=sql;
 qClients.Open;

That part is pretty much in the form that you suggested. The Close is
taking 1 to 2 seconds, and the Open is taking a similar amount of
time. That does not seem reasonable, especially since the same code
using PostgreSQL executed those three statements on the same database
before a fast typist could hit the next key (I am incrementally
building the lookup sql). That makes me wonder if I need to change how
qClients is initialized. Here is the current setup of qClients:

  object qClients: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    AutoCalcFields = False
    Database = FormDatabase.SQLite3Connection
    Transaction = FormDatabase.SQLTransaction
    ReadOnly = False
    Params = <>
    left = 504
    top = 64
  end

I'm not seeing anything in there that I would recognize as a performance issue.

Here is the database form (user and password omitted):

object FormDatabase: TFormDatabase
  Left = 473
  Height = 240
  Top = 169
  Width = 184
  Caption = 'FormDatabase'
  LCLVersion = '0.9.30'
  object SQLite3Connection: TSQLite3Connection
    Connected = False
    LoginPrompt = False
    DatabaseName = 'C:\pruett\PascalDB\PruettClients.s3db'
    KeepConnection = True
    Transaction = SQLTransaction
    Options = []
    left = 53
    top = 123
  end
  object SQLTransaction: TSQLTransaction
    Active = False
    Action = caNone
    Database = SQLite3Connection
    left = 53
    top = 67
  end
end

I found it interesting that the program could open and use the
database without the full path included, but the design-time part of
the IDE requires a full pathname to do the "active at design time"
trick. Not sure exactly how I guessed that.

I think I have a (ugly) workaround for the performance issue, but I
would like to do better. Further suggestions are welcome, and don't
worry about hurting my feelings; I realize that I'm not yet very
proficient in Lazarus, since I have used it only sporadically.

On Fri, Jul 29, 2011 at 9:13 AM, Ludo Brands <ludo.brands at free.fr> wrote:
>> I have established that the problem is not in SQLite, but has
>> to be in my code somewhere. I suspect it is related to the
>> transaction/commit problem I posted a few days ago, and I
>> plan to track that down today if I can. I need to finish this
>> application by tomorrow.
>>
>
> I read your previous messages again. By the looks of it you're using a very
> hybrid way of using SQLite3Connection trying to do things yourself that the
> components are supposed to do (starting transactions for example).
> Here is a basic example of inserting data with direct sql queries:
>
> -drop a TSQLite3Connection, a TSQLTransaction and a TSQLQuery on a form, a
> data module or create them in code.
> -set TSQLite3Connection properties to connect to your database
> -assign TSQLTransaction.Database to the TSQLite3Connection instance
> -assign TSQLQuery.Database to the TSQLite3Connection instance and
> TSQLQuery.Transaction to the SQLTransaction instance. Active:= False,
> SQL.Text:=''.
>
> Now to execute an insert query (assuming
> TSQLite3Connection.Connected:=true):
>    SQLQuery.SQL.Text:=sql;
>    SQLQuery.ExecSQL;
>    SQLTransaction.CommitRetaining;
>
> That's it. SQLTransaction.CommitRetaining; does the COMMIT and a BEGIN to
> start a new transaction.
>
> Just to be complete, I'm using lazarus 0.9.31 svn with fpc 2.5.1 svn.
>
> Ludo



-- 
Howard Lee Harkness
(214) 390-4896