March 27, 2012 at 2:19 am
Hi All,
I there any memory allotted for Result Set. Means if we are fetching lacs of records. will get slow...?
Anybody please give me suggestions
March 27, 2012 at 9:56 am
SQL Server will deliver results to the client as fast as the client can consume the data. The "client" in this scenario is anything beyond the interface SQL Server is communicating on, including the network between the server machine and client machine.
In general all data SQL Server returns is first buffered, i.e. read into the memory-resident buffer pool from disk, so yes, the entire resultset passes through memory on the SQL Server.
However I am unsure whether that is to say all data required to deliver a resultset must reside in the buffer pool at one time. In other words, in the case of a select query with no where-clause, I suspect SQL Server may be coded smartly enough such that the first portion of the resultset may be paged into memory, delivered to the client, and then released from the buffer pool to make room for more data for the latter portions of the same resultset. I cannot find specific info confirming that point, however it seems necessary considering we can bcp multi-GB tables out of a database to a file on servers with smaller buffer pools than the size of said table. Suffice it to say though, that the larger the resultset the more time it will take to deliver, and depending on the query, especially poorly written ones, the resultset-size to time-to-deliver ratio will not always grow linearly.
Are you have a specific problem with a large query?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 4, 2012 at 4:24 am
Thanks for your valuable reply... Really it is useful....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply