[yunqa.de] Re: Using API calls for speed
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Sun, 11 May 2008 14:19:40 +0200
Michael Hooker wrote:
>My function needs to extract information from the database as quickly as
>possible to keep up with the flow of data through the port and I can't stress
>enough that there is a lot of it - several Gigabytes a day! So I decided to
>try using the native API for speed,
In my experience, the native API is just marginally faster than the
TDISQLite3DataBase / TDISQLite3Statement wrapper. This is because the wrappers
are very lean, and because most of the time is spent on disk access / memory
access. But if you must really to push performance to the limits, you might try
the native API for an extra 1 or 2 percent.
>I found it very difficult but following some of the demo examples,
I am sorry for that. Any suggestions to improve the code and make it more
readable and informative are highly welcome!
>this is what I came up with for test purposes. Clicking the FindBtn sends the
>contents of an edit control to the function "GetAcDetails" and the csv string
>is returned in another edit control. And it works!
:-)
>My queries follow the code.
I skipped the code, but it can see nothing wrong with it except:
sqlite3_prepare just once, and reuse multiple times (see last paragraph for
details).
>First, after a bit of investigation, I found that the result of
>sqlite3_check(sqlite3_step(Stmt_H), DB_H) always seems to be 100 if the item
>is found and 101 if not.
Correct, but not quite. Please look up sqlite3_step in the DISQLite3 help index
for possible return values, which can be SQLITE_BUSY, SQLITE_DONE, SQLITE_ROW,
SQLITE_ERROR, or SQLITE_MISUSE, all constants defined in DISQLite3Api.pas.
>Is it safe to hard-code this into the function to quickly indicate whether or
>not the item was found, as I have done?
Please never use the numeric values, always use the constants instead.
Otherwise you will have to update your code in case the constants ever change
(which they should never do, but better be safe than sorry). It also makes your
code more readable.
>Second, does using "sqlite3_check" provide any real benefit in this scenario?
>It may just be wasting precious time.
sqlite3_check is a convenience function. It checks the return value and raise
an exception if something goes wrong. sqlite3_check has nearly zero overhead,
but if your measurements indicate that it does slow down your app, you can
replace and inline it by your own error-checking code.
>Third, is sqlite3_step the right function to use when you only expect one (or
>no) row to be returned? There is no "stepping" to do, is there?
DISQLite3 always steps into the result set, even for no or just a single row.
It returns SQLITE_ROW for each row found. If there are zero rows in the result
set, it returns SQLITE_DONE immediately. For a single row, it returns
SQLITE_ROW followed by SQLITE_DONE when you call it a second time.
If you know there will be just one row, call sqlite3_step just once.
>Fourth, this function will be running 24/7 and mostly unattended. The last
>thing I want is a message that pops up saying that the database is locked and
>waiting for me to press a button to continue! I need something that tries
>again until the database is unlocked and success is achieved.
After calling sqlite3_step, check for the SQLITE_BUSY return value and repeat
until you succeed.
> I have a feeling I should use a try...except statement and a way of catching
> the error and using it to repeat the call to the function. I see there is a
> class that returns error numbers and descriptions (ESQLite3). However, in 15
> years of messing about with Delphi I have never had to do anything like this
> and have no idea where to start (the Help is not very enlightening). As the
> result code for a Locked table seems to be 6, then my first instinct is to
> say:
>
> Res := sqlite3_check(sqlite3_open(PAnsiChar(DBName), @DB_H), DB_H);
> If Res := 6 then begin
> Result := 'Locked';
> Exit; {I presume Exit works with functions, as with procedures}
> end;
If you omit sqlite3_check, you can get away without try...except.
Also, you should not need to check for SQLITE_LOCKED unless your application
itself accesses the database in an improper way: SQLITE_LOCKED (6) is distinct
from SQLITE_BUSY (5). SQLITE_BUSY means that another database connection
(probably in another process) is using the database in a way that prevents you
from using it. SQLITE_LOCKED means the source of contention is internal and
comes from the same database connection that received the SQLITE_LOCKED error.
>I don't have any way of testing this until the error arises! And of course it
>would only work if I could somehow avoid the error message popping up.
You can test this by writing your specific test application which blocks the
database so the application to be tested must deal with SQLITE_BUSY.
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. This technique is demonstrated in the
DISQLite3_Prepare_SQL and DISQLite3_Drive_Catalog
(DISQLite3_Drive_Catalog_DB.pas) projects.
Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
