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] -----------------------------------------------------------------------------