Hi Amit,
Generally, what Dan says is correct, but his suggestion isn't *the* answer that will work for everyone. He is correct about the size of a data column and the problem of retrieving all of the data. Also, an index is usually build on the primary key, so the first fetch should be fast.
However, one reason to not do two fetches is that the database might change between fetches. For example, you get all of the primary keys, and then someone inserts or deletes a row. Of course, the second SQL statement won't fail, but you will end up with 100 rows in one result set and 98 (or 102) in another. (I am presuming you are implementing some kind of "next page" feature...which is why you want "n" records at a time.)
Another problem is in a client/server set up, the bottleneck might be the communications line between them. So, it might be acceptable to get all 1,000,000 records in one go, store them locally "somehow" and then do whatever local parsing, sorting, etc. you need rather than opening the communications line repeatedly.
Yet another point to consider is how often someone will ask for 1,000,000 records and whether all of them will be used. Take Google as an example (which is of course not a database). Suppose you type in a keyword and get 1,000,000 web page hits. Now, most people won't want all 1,000,000...they are usually content with a few pages (my patience limit is actually the first page before I want to type in another keyword combination :-) ). In this scenario, though, it wouldn't make sense to ask for all 1,000,000 and store them locally on my computer.
SO, Dan's suggestions are good, but you need to ask yourself what it is you want to do. There is never one single choice that will work for everyone...
Ray 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 ?
-- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/
