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? -- 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? -- 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). That's about I guess, for now. Any help and hints are highly appreciated. Thanks. -zoltan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
