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 -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/
