On Fri, Sep 12, 2008 at 10:52 AM, dan <[EMAIL PROTECTED]> wrote: > On Fri, 12 Sep 2008 10:24:58 +0530, Amit Saxena wrote: > > > The only issue with this approach is that two queries needs to be run > > for the same. > > > > Considering the tables containing 1 million (and more) rows, this two > > pass approach will not be good. > > > > What others say ? > > It's precisely because you can have so many rows that this 2-pass approach > is best. In the 1st query, where all your 'where' conditions are processed, > the database server does all the collecting of the rows ( well, primary > keys ) that you want in 1 go. Once it's done, that's it ... you only do it > once. Also because you're only fetching the primary keys and not the entire > recordset, it's actually quite fast to transfer the keyset from the DB > server to the client. Also your memory requirements on the DB server are > MUCH lower as the server only has to hold a list of primary keys ( numeric > ) in memory, instead of all the rest of the columns as well, which could > contain strings and other memory-hungry columns. I did extensive testing ( > MySQL, SQL Server, SQLite ) before going down this path, trust me ... > > Then you've got an array of indexes ( the keyset ) that you send to the DB > server ( ie in the where clause ), and it makes accessing all pages *very* > fast, as you're using the primary keys to fetch data. If you don't do it > like this, you're making the DB server reprocess your complicated where > clause each time. If you're got millions of records, this is incredibly CPU > & memory intensive for the DB server, and not feasible if you've got > multiple clients hitting the DB server. Also as noted in my previous post, > if you're not very careful with sorting, you'll can end up getting > duplicate records. > > Anyway, if you find a better way, I'd like to hear it :) > > Dan >
Thanks Dan for the detailed explanation :-). The benefits of the two-pass approach is now clear to me. Regards, Amit Saxena
