Zoltan Bencik wrote: > 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, but it will also increase your database size, and slow your inserts and updates, since each index will also have to be updated. You may want to create a temp index on the one column that the user has selected for sorting as needed. This index would be dropped and recreated as the sort column changes. There would be a delay as the index is created, but that only happens once when the sort column changes. This index would not be stored in your database file so its size will not be affected. For a table with 10,000 rows creating the index should be quite fast. > -- 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 resultset 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? No. But it probably doesn't matter after you read about selecting a subset of the list below. > -- What's the best way to approach scrolling of the list view, which > backended with this table data? In order not to query all the rows, > but just the viewable are of the list view. The list view can give all > information about viewing area(top position, bottom position). See http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor for additional info. HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
