On Wed, Mar 12, 2008 at 11:50:57PM +0500, Zoltan Bencik scratched on the wall: > Hello all, > > I'm trying to enhance startup load and sorting in my application. In > order to speed up sorting I'm thinking of using sqlite. Since the data > I'm using gets sometimes in 10,000 rows, and using some list control > for GUI, sorting by list columns gets a bit nasty and slow. > I would like to get some useful comments if I the way I use sqlite > will really help me. > > I've a single table: > > CREATE TABLE tblObjectInfo ( > filename char(10) PRIMARY KEY NOT NULL, > priority integer NOT NULL DEFAULT 0, > signed boolean NOT NULL DEFAULT false, > crypted boolean NOT NULL DEFAULT false, > mdate timestamp, > cdate timestamp, > "owner" text, > "group" text > ); > > Then, I create index for each column, except the primary key: > > CREATE INDEX tblObjectInfo_Idx1 > ON tblObjectInfo > (priority); > CREATE INDEX tblObjectInfo_Idx2 > ON tblObjectInfo > (signed); > ... > and so on. > Then, depending on what column user hits to be sorted, I just query > the table using: > SELECT filename FROM tblObjectInfo ORDER BY [selected column name] > > and re-order my list view.
> My questions are: > -- Will be this way of indexing speed up my ordered retrieval of data? Yes, although if you have an index on every column your inserts will be pretty slow. If you can, it would be best to batch them in a transaction to help with that. > -- Is there anyway, I can get the information where new inserted row > will be located, so I could correctly insert this item visually into > list view? I suppose it will be at the end of the table, but the point > is, I would like to know where in ordered result set I've just queried > it would be inserted. Or, will I have to query the table once again to > get the visual update with correct ordered list of items? I think you're missing a conceptual point: Tables have no inherent ordering. Tables are just a group collection of rows, with no explicit order or structure. If you do a SELECT without a ORDER BY, there is no guarantee about the order of the records returned. Technically, you could run the exact same SELECT statement two times in a row and get a different ordering of rows-- although this doesn't happen often in practice. Because tables have no inherent ordering, it doesn't really make sense to speak of "where new inserted row will be located." It will just be in the table, like a record thrown into a bucket. If you know what column(s) you're sorting over with ORDER BY, you should be able to figure out where a new row might go in a set of sorted output (output != table), although be sure you're using the *exact* same sorting conditions as the database (easy with numbers, not always so easy with text). This does bring up another issue, however... since the row input order is unsorted, if you ORDER BY a single non-unique column, your rows can still shift around. To get around this, you can put a unique column (like filename) as the last expression in the ORDER BY clause. Also consider that most GUIs that provide "click to sort" columns give the user a stable-sort experience. For example, in a music player application, if I click on "track number", and then "album" and then "band name," I expect to see things ordered by band name, followed by album, followed by track number. If you're pulling this out of a database and the application only provides ORDER BY with the *last* column the user clicked on, I'd get band names in order, but everything beyond that would random. Things work a bit differently if you're sorting an in-memory data structure with a stable sort, but that's not the case if you keep going back to the database for you records. Depending on how many of your columns allow duplicate entries (looks like most), your application might need to build some pretty interesting ORDER BY clauses to get the experience the user expects. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
