Many thanks, Ralf. As usual you are very helpful. I hope my further comments make sense.
Ralf wrote:
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 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 :)
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.
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" :)
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! 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.
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).
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.
Thanks again Michael Hooker----- Original Message ----- From: "Delphi Inspiration" <delphi@xxxxxxxx>
To: <yunqa@xxxxxxxxxxxxx> Sent: Sunday, May 11, 2008 1:19 PM Subject: [yunqa.de] Re: Using API calls for speed
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
_______________________________________________ Delphi Inspiration mailing list yunqa@xxxxxxxxxxxxx //www.freelists.org/list/yunqa
