MikeW wrote: > Andrew Gatt <[EMAIL PROTECTED]> writes: > > >> I'm not sure if i'm missing something, but is there an efficient way of >> retrieving multiple rows based on different conditions in order. For >> example i have a table with rows of ids, i want to select multiple rows >> at a time. At present i am doing a "SELECT name FROM table WHERE id = x" >> for each row i want and then stitching it all together. But i'm finding >> this is quite slow even on a moderately small database (2000 entries). >> >> I'm guessing my SQL is the worst way of doing things so i've been trying >> to find a better method. I stumbled across "SELECT name FROM table WHERE >> id IN (x,y,z) however this doesn't allow me to specify the order the >> rows are returned, which i must have. >> >> The only other option i can find is using UNION ALL in between multiple >> SELECT statements, but would this give me a large performance increase >> over doing this progammatically as i've got it? >> >> Unless i've missed something obvious which could well be the case! >> >> Andrew >> > > Since performance is the issue, wouldn't it just be better to get all the > rows 'as they come' - into a hash or similar lookup-optimised structure, > and then get them from that in the required order ? > > I would have thought that all those DB queries would be slow > when you can get a smaller subset into an efficiently accessed > container in memory ... > > Incidentally, I trust your query is > "SELECT name FROM table WHERE id = ?" and you are doing a sqlite3_bind() > each time, no need to re-prepare the statement every time !! > > Regards, > MikeW > > > Thanks for everyones suggestions. The order of the ids will be chosen by the user at run time and so will be truly arbitrary. As this could be run in several threads at the same time on the database i'm not sure i'd like to add temporary tables or extra columns - i guess i would have to create temporary tables with different names to avoid conflicts? The bind api is not something i'd come across and will look into.
With the index in place accessing the database in the way described is an order of magnitude faster (which is probably obvious to most people) and will suffice for now. Andrew _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
