[yunqa.de] Re: Using API calls for speed

  • From: Delphi Inspiration <delphi@xxxxxxxx>
  • To: yunqa@xxxxxxxxxxxxx
  • Date: Mon, 12 May 2008 14:08:01 +0200

Michael Hooker wrote:

>I simply followed what you yourself say in the Help file: "The native API 
>creates the smallest and fastest code and is therefore best suited for 
>time-critical applications."  So my first suggestion is that you amend the 
>Help to say what the real difference is :)

Suggested amendment:

Compared to the native API, the object layer adds a small executable 
footprint and minor execution time overhead. Most applications will not notice 
this overhead with average to large sized databases because disk access will 
claim most system resources. The object layer performance penalty will hardly 
exceed 1 or 2 percent, even for very small database sizes.

>Ralf wrote:
>>If you know there will be just one row, call sqlite3_step just once.<
>
>Well that's what I did. I just wondered if there was a better way. According 
>to the Help SQLITE_ROW means "sqlite3_step has *another* row ready."  This is 
>one of the reasons I find using _step confusing for accessing a single record. 
>"step" implies stepping through a set of records, and "another" clearly 
>implies that one has been found already and another, ie a second result, is 
>available.  Maybe the Help should simply say "sqlite3 has returned a row of 
>data" if that is what it in fact means.

You refer to just the SQLITE_ROW documentation, but there is more: The 
sqlite3_step() function covers roughly two printed pages. I believe that just 
this paragraph explains it fairly well:

"If the SQL statement being executed returns any data, then SQLITE_ROW is 
returned each time a new row of data is ready for processing by the caller. The 
values may be accessed using the column access functions. sqlite3_step() is 
called again to retrieve the next row of data."

>Ralf wrote:
>>sqlite3_check is a convenience function...If you omit sqlite3_check, you can 
>>get away without try...except.<
>
>Er.. Let's see if I have this right.  sqlite3_check is a system that causes 
>returned error codes to result in exceptions, and an exception will normally 
>produce a modal message box which interupts your program until you press OK. 
>Try...except causes exceptions arising in the try block to skip over any 
>remaining statements to the except block before they manifest themselves. If 
>user code in the except block catches the error, the user code can substitute 
>a process which will replace the process which would normally happen (ie the 
>message box).  If omitting sqlite3_check avoids the modal dialog box popping 
>up in the first place, then it seems to me that it's more convenient to omit 
>the "convenience function" :)

Large parts of Delphi are based on exceptions, and their usage is well 
described in the Delphi help. Therefore I believe that library authors can just 
assume that Delphi programmers are familiar with the concept.

You might not find exceptions convenient, but most people do. Therefore 
DISQLite3 makes it easy to raise exceptions on errors via sqlite3_check(). For 
them, sqlite3_check() is a concenience function. Btw: sqlite3_check() is not a 
new concept, you fill find similar functions in other libraries, including the 
VCL.

If you are seeing a unwanted message box as a result of an exception, it means 
that your application does not handle exceptions properly. Unhandled exceptions 
usually indicate some sort of error and should not happen unintentionially.

You are welcome to omit sqlite3_check() to avoid potential message popups. But 
if you do, you are responsible to add your own error checks! Depending on what 
you are aiming for, this can indeed be more convenient.

>Ralf wrote:
>>Last, but not least: A big time-saver is to avoid sqlite_prepare for each 
>>query you are running. Instead, prepare ahead of time and sqlite3_bind new 
>>values and reuse the statment as needed. When done with it, immediately call 
>>sqlite3_reset to remove any database locks. sqlite3_finalize only before you 
>>close the database / application<
>
>OK...  But I need more help on sqlite_prepare_v2 and sqlite3_bind please!

These functions are documented at length in the DISQLite3 Help. You can look it 
up through the index easily!

> I can't make head nor tail of the Drive_Catalog example, it's much too 
> complex for me, and the Help is too obscure. Could we please have a <simple> 
> example of how to prepare and use 'SELECT * FROM TableName WHERE SearchColumn 
> = "SearchString"', where SearchString is an ordinary Delphi string variable? 
> And then an example of how to bind the string in to the prepared statement 
> when required. It doesn't have to be a working program, just a couple of 
> template code snippets will do.

The basic principle is simple. Please take a look at the small demo project 
attached.

>Ralf wrote:
>>Any suggestions to improve the code and make it more readable and informative 
>>are highly welcome!<
>
>My suggestion is that code examples are included in the Help, as in the Delphi 
>Help, or in another document. Trying to demonstrate a simple concept by hiding 
>it in a working program just does not work for many of us. It is too much to 
>expect us to work out how the entire program works just to see how one aspect 
>of it is coded.  Personally, I simply cannot find the bits I need in 
>Drive_Catalog.  Code snippets, as published on many helpful Delphi websites, 
>really do explain things a lot better, and you can try them out in your own 
>skeleton programs which you understand because you wrote them. This minimises 
>the learning curve for those of us who don't learn so quickly now our hair has 
>gone white (or gone completely).  You can teach an old dog new tricks, but 
>it's much easier if you teach it one trick at a time in an environment it is 
>comfortable with :)  Having set out the snippets and explained them, you can 
>then refer to the demo programs in which they are used to see a working 
>example (and give the line numbers).

Interesting point, I will think more about this!

>I have the same trouble with nearly all 3rd party components.  The authors 
>often use the demos to show off all the complicated things their component 
>will do, and it is like trying to learn to swim in the deep end of the 
>swimming pool. 

Writing good demos is not easy because they must be a feature and a learning 
experience IMO. I agree that the DISQLite3_Drive_Catalog is quite advanced, but 
DISQLite3 has some really unique features and they must be demonstrated 
somewhere for users to see them!

On the simple side, I have added a few small and lean projects, too, like the 
DISQLite3_StringGrid and DISQLite3_StringList. What's maybe really missing is a 
demo overview document to help users pick the right demo for their level of 
experience.

Many thanks for sharing your suggestions. I hope to find the time to put them 
into practice soon!

Ralf 
{ How to reuse a prepared SELECT statement with DISQLite3. A statement is
  prepared just once, but is used for different queries by binding different
  values to it.

  Run the DISQLite3_Create_Table demo first to create the required table.

  Visit the DISQLite3 Internet site for latest information and updates:

    http: //www.yunqa.de/delphi/

  Copyright(c)2005 - 2008 Ralf Junker, the Delphi Inspiration < Delphi@xxxxxxxx 
>

-------------------------------------------------------------------------------}

program DISQLite3_SELECT_with_bind;

{$I DI.inc}
{$I DISQLite3.inc}

{$APPTYPE CONSOLE}

uses
  SysUtils, DISQLite3Api,
  DISQLite3_Demos_Common in 
'..\DISQLite3_Common_Units\DISQLite3_Demos_Common.pas';

//------------------------------------------------------------------------------

{ This procedure just prints out the results returned from a prepared
  statement. After all results are printed, the statement is reset. }
procedure ExecutePreparedStatement(const Stmt: TDISQLite3StatementHandle);
var
  i: Integer;
begin
  while sqlite3_check(sqlite3_step(Stmt)) = SQLITE_ROW do
    begin
      for i := 0 to sqlite3_column_count(Stmt) - 1 do
        begin
          if i > 0 then Write('|');
          { We optimize the data output according to the column type. }
          case sqlite3_column_type(Stmt, i) of
            SQLITE_INTEGER:
              Write(sqlite3_column_int64(Stmt, i));
            SQLITE_FLOAT:
              Write(sqlite3_column_double(Stmt, i));
            SQLITE_TEXT:
              Write(sqlite3_column_str(Stmt, i));
            SQLITE_BLOB:
              Write('BLOB'); // Write BLOB indicator.
            SQLITE_NULL:
              Write('NULL'); // Write NULL indicator.
          end;
        end;
      WriteLn;
    end;

  { Reset the statement immediately after it has been used. This clears any
    pending database locks and prepares the statement for another round of
    sqlite3_step(). }
  sqlite3_check(sqlite3_reset(Stmt));
end;

var
  Stmt: TDISQLite3StatementHandle;
begin
  WriteLn('SQLite Version ', sqlite3_libversion); WriteLn;

  { Disable FPU exceptions. No need to restore, setting is process specific. }
  Set8087CW($133F);

  try
    Open_Demo_Database;
    try
      { Pepare an SQL statment for later use. Notice that this statement is not
        finalized until just before the database closes. This allows to reuse
        it during the application lifetime without repreparing. Since preparing
        SQL statements is quite time consuming, reusing statements speeds up
        performance, especially when running it many times. }
      sqlite3_check(sqlite3_prepare_v2(
        DB, // Handle of the Demo.db3 database file.
        'SELECT FirstName, LastName FROM People WHERE FirstName = ?',
        -1, // Length of SQL statement, pass -1 to autodetect
        @Stmt, // Variable for the prepared SQL statement
        nil), // Variable to store beginning of next SQL statement or nil if 
not needed.
        DB);
      try

        { Bind a value to the 1st parameter of the prepared statement. This
          effectively replaces the ? parameter with the string value 'Albert'. }
        sqlite3_bind_str(Stmt, 1, 'Albert');
        ExecutePreparedStatement(Stmt);

        { Do like above, but use the string value 'Leonardo' this time. }
        sqlite3_bind_str(Stmt, 1, 'Leonardo');
        ExecutePreparedStatement(Stmt);

      finally
        { All prepared statements must eventually be finalized
          before closing the database. }
        sqlite3_finalize(Stmt);
      end;
    finally
      Close_Demo_Database;
    end;

  except
    on e: Exception do
      WriteLn(e.Message);
  end;

  WriteLn;
  WriteLn('Done - Press ENTER to Exit');
  ReadLn;
end.

Other related posts: