On 2 Feb 2011, at 11:28pm, Dave White wrote:
> Our old selection method queries indexes and returns the equivalent of a list
> of rowids that we then use to access records as needed. I've managed to mimic
> this behavior but it is very slow. My select statement is something like
> "Select rowid from T01 where ref_uid=100". I then step through all rows, read
> in each rowid, assemble them into an array, and forward the array to a client
> which will then request the records by rowid one at a time.
Is there really any point in doing this ? If you feed your second operation
like this:
SELECT thisCol, thatCol FROM T01 WHERE ref_uid=100
you can skip the first stage entirely. However, it's possible that whatever
your second operation is doing will interfere with the SELECT so ...
> This takes about 10x longer than our old selections. It appears to me that
> this is happening because each step to the next row is paging in data so the
> rowid can be read. I expected index trees would store rowids and make data
> reads unnecessary, but this doesn't seem to be the case.
If you have an index on the ref_uid column, then the SQL operations involved in
this won't take too long. However you can dramatically reduce the number of SQL
operations for this particular operation using an aggregate function:
http://www.sqlite.org/lang_aggfunc.html
So try something like
SELECT group_concat(rowid) FROM T01 WHERE ref_uid=100
should return just one row, with all the rowids concatenated into one long
string, separated by commas.
Note: I just thought up the above code, I haven't actually tested it. You
should.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
