Is entire SQL result set loaded into memory before display?

CaptSkinny

I'm attempting to use LINQPad as an SSMS replacement, but my queries will time out (or I give up waiting) unless I limit the result set to a certain number of rows with Take(). Does the app load the entire result set into memory before displaying it in either rich text or grid output? SSMS will display results in chunks, adding records to the output grid as they become available from the server. Is LINQPad capable of doing something similar? I see an option in preferences for limiting rich text results to a certain number of rows, but even when I have it set to the default of 1000 my simple query (from o in tbCompanyObservation select o) hasn't produced results when I give up waiting after 5 minutes, while adding Take(1000) to the same query will return the results in less than a second.

CaptSkinny

Wow, almost 500 views and no comments. I suppose it's either a really poor question or a really good one.

Cross-posted (and revised) to http://stackoverflow.com/questions/17196656/does-linqpad-load-entite-sql-result-set-into-memory-before-displaying-it

JoeAlbahari

Does the app load the entire result set into memory before displaying it in either rich text or grid output?

Yes, the short answer is that LINQPad loads results into memory before rendering them (the exception to this is if the sequence implements IObservable, in the latest beta).

but even when I have it set to the default of 1000 my simple query (from o in tbCompanyObservation select o) hasn't produced results when I give up waiting after 5 minutes, while adding Take(1000) to the same query will return the results in less than a second.

This is a bug/limitation in ADO.NET's SqlDataReader. When you dispose a data reader after reading only a portion of the rows, it "cleans" the reader by enumerating all remaining data. It certainly is annoying, so I'm looking into whether it's possible to detect that condition and cancel the underlying command.

JoeAlbahari

I've just uploaded a new beta:
http://www.linqpad.net/beta.aspx

This should work around the SqlDataReader limitation. So if you dump a table of a million rows or so in rich text mode, it should quickly complete with the first 1000 rows.

Let me know how you get along.

CaptSkinny

It works great, thanks!

Can I make a feature request for a similar threshold option for the grid?

JoeAlbahari

Yes - that's in the pipeline. Check out tomorrow's build