Indeed the query in the new code is prepared once and then reset and rebound between queries, but maybe I must go double check if that was the case with the code that performed poorly. I remember asking the developer if he did it like that so I presumed it was done correctly when the answer was yes.
Regardless I am glad that SQL is back on the menu as it makes configuring non trivial lookups, well, trivial. Your affinity suggestion is an interesting one and I will look into that more carefully. With the new over designed code the affinities are kept the same where possible but the previous concept code might have missed such technicalities. I do believe though that the affinities are mostly sorted out when the statement is prepared so from the lookup point of view it might not have made a difference at all. I might be wrong on this. I do however extract all data with sqlite3_column_text even though some are INTEGERS, but I think somewhere it is mentioned that sqlite uses a simple snprintf to accomplish this; something that I would have had to do anywais. p Andrew Finkenstadt wrote: > > I'm speaking purely from an intellectual knowledge of "reading the docs" > (and having been an Oracle database developer since 1990), but I would do > two things in your coding of the SQL query: > > 1. use a bind variable instead of inlining the SQL if you aren't already, > so > that you can prepare the statement once, and then bind & execute for each > different sought key value. I realize that you were giving examples of > queries rather than actual code fragments, so this may be a moot point. > > 2. double-check the affinity of the column for text versus numeric > values, > and bind the corresponding type. If the column has text affinity, then my > understanding is that if the bound parameter will be converted to text > prior > to execution beginning, a perhaps unnecessary overhead. > > --a > > > > On 4/11/07, pompomJuice <[EMAIL PROTECTED]> wrote: >> >> >> Done coding it back to using SQL to do the lookups and there seems to be >> no >> performance issues whatsoever O_o. Although I did not actually write the >> previous implementation using SQL to do the lookups I remember going >> over >> the code and all seemed fine. No idea what went wrong there but I am >> happy >> I >> dont have to mess around with the btree anymore, its a bit complicated. >> >> Thanks for the tip. >> >> >> drh wrote: >> > >> > pompomJuice <[EMAIL PROTECTED]> wrote: >> >> I could get a maximum of 300-400 lookups per second using >> >> a conventional "select * from table where column = key" type query. >> > >> > I would guess, then, that either (1) table.column is not indexed >> > or else (2) you are running on very, very slow hardware (a 386?), >> > or (3) you are doing something terribly wrong in the query. I >> > just ran a quick test and I'm getting around 25000 queries/second >> > using the Tcl interface (i.e. using a scripting language rather >> > than calling the APIs directly) using a query of the form: >> > >> > SELECT * FROM table WHERE rowid=? >> > >> >> >> >> Is there some other aspect such as for example "incKey" that causes >> btree >> >> lookups to function differently? I desperately need help here. >> >> >> > >> > There were several subtle but important differences in the Btree >> > layer interface going from 3.3.13->3.3.15. But you need to understand >> > that the Btree is a non-exported internal-use-only interface. It is >> > unsupported and is likely to change in strange and incomprehensible >> > ways from one point release to the next, as indeed it has in each >> > of the previous two releases. I strongly discourage you from >> > pursuing this path. >> > >> > -- >> > D. Richard Hipp <[EMAIL PROTECTED]> >> > >> > >> > >> ----------------------------------------------------------------------------- >> > To unsubscribe, send email to [EMAIL PROTECTED] >> > >> ----------------------------------------------------------------------------- >> > >> > >> > >> >> -- >> View this message in context: >> http://www.nabble.com/sqlite3BtreeMoveto-broke-between-3.3.12-%3E3.3.14-15-tf3547873.html#a9937387 >> Sent from the SQLite mailing list archive at Nabble.com. >> >> >> >> ----------------------------------------------------------------------------- >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> ----------------------------------------------------------------------------- >> >> > > -- View this message in context: http://www.nabble.com/sqlite3BtreeMoveto-broke-between-3.3.12-%3E3.3.14-15-tf3547873.html#a9939877 Sent from the SQLite mailing list archive at Nabble.com. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
