Well, with your 5 gig table, the select statement needs to still compile a set of results somehow, be it data or pointers to the data, and remember which row has been read. As I said, I've never looked or traced the code, but something has be be created somewhere that says "This is the next record". If I update the CURRENT record, the order in which the records are retrieved/stored shouldn't change. Maybe if just doing a [select * from MyTable] will yeild a simple pseudo thought of "Start from the first physical record, then go to the next physical record when needed" but if I change the statement to [select * from MyTable order by SortOrder] it can't go by the first physical record in that table, of course, but some sort of compiled list stating "The first record is physical row B, second is physical row Z..." etc.
I'm not look'n to piss anyone off, of course, I'm just trying to figure out how it works in the background. On Mon, Nov 8, 2010 at 5:25 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > > > In other words, when I make a SELECT statement, the results that come > back > > would point to the physical locations of where the raw data exists, or, > > return the data and stores it in memory, or however the mechanism works. > > Updating should not affect what rows have been called up. > > So you require two copies of the data: one which is the data as it was when > you started the SELECT command and another will all the updates that have > taken place since then, until _finalize() is called. This means that the > SELECT command must reserve enough memory for the entire results of the > SELECT command, and copy all the data into it to process the initial SELECT > command. > > So if I had a table which took 5 Gig of disk space and did a "SELECT * FROM > myTable" my application would suddenly need to reserve 5 Gig of memory to > store the results. This would make every SELECT very slow and use a lot of > memory, useless on platforms which need to respond to button-presses in > realtime or have limited memory. So I can understand why SQLite doesn't > allow it. > > You can do this yourself, of course: perform the entire SELECT and store > the results in one or more variables, then use the contents of those > variables to decide which UPDATE commands to do. Or you can just use the > UPDATE command with a WHERE clause, which does both commands in one go and > is far more efficient. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
