Where/When is the use of RBAR buffering related to ASYNC_NETWORK_IO waits negotiated between the Client/SQL Server, or Is it always utilized as in that is just how SQL Server spools data to the client?
Is "Row By Agonizing Row (RBAR)" a feature of ODBC or .NET Data Components, also, does anyone know if it can be turned off or overridden?
This stems from a SP that was tuned to run in 5 minutes, but when put in a report it falls in and out of ASYNC_NETWORK_IO for another 5 minutes for a total time of 10 minutes. In the worst case scenario, 800K records are returned, however, I would think the data would be "pushed" more quickly than what I am seeing. This is based on the fact that a similar report with no Column grouping seems to pull data quickly and returns the same amount of data. I know that grouping brings reports to a crawl, but please help me work with this issue if possible. Why would one query on a report with no grouping,with the same amount of records,"dumped" to a excel file, finish in 2 minutes, whereas, column grouping causes the client to buffer. Is there a way to force SSRS to buffer all the data locally, then render?
I found that using a "shared memory connection" will share the buffer, however, that is only valid if the client and SQL Server share the same server.
Also, the query plan for the SP is the size of a roadmap but it is a good plan. It uses no cursors and is entirely a set based query.