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
