Loading Data on Demand
-
I'm using ADO.NET. Entity Framework is not an option. Suppose I have an
IDataReaderinstance that was created from calling a stored procedure that returns 150,000 rows. If I create aDataTable, and then callDataTable.Load(IDataReader), does it: 1. Read all available rows into the table at once? 2. Read only those rows that are necessary to return whatever rows I read from theDataTable? 3. Some hybrid of the above two scenarios? I'm interested in implementing a solution that only reads data from the server upon demand. IOW, I don't want my form to only appear on screen after all 150,000 rows have been pulled from SQL Server. Thank you very much for your insight.The difficult we do right away... ...the impossible takes slightly longer.
-
I'm using ADO.NET. Entity Framework is not an option. Suppose I have an
IDataReaderinstance that was created from calling a stored procedure that returns 150,000 rows. If I create aDataTable, and then callDataTable.Load(IDataReader), does it: 1. Read all available rows into the table at once? 2. Read only those rows that are necessary to return whatever rows I read from theDataTable? 3. Some hybrid of the above two scenarios? I'm interested in implementing a solution that only reads data from the server upon demand. IOW, I don't want my form to only appear on screen after all 150,000 rows have been pulled from SQL Server. Thank you very much for your insight.The difficult we do right away... ...the impossible takes slightly longer.
#1 - it loads all of the rows into memory at once. DataTable.Load | Reference Source[^] Displaying 150,000 rows on screen in one hit is not a good idea. How is the poor user ever going to find the one row they're interested in? There must be some way to summarise, filter, or page the data to make it easier to consume.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I'm using ADO.NET. Entity Framework is not an option. Suppose I have an
IDataReaderinstance that was created from calling a stored procedure that returns 150,000 rows. If I create aDataTable, and then callDataTable.Load(IDataReader), does it: 1. Read all available rows into the table at once? 2. Read only those rows that are necessary to return whatever rows I read from theDataTable? 3. Some hybrid of the above two scenarios? I'm interested in implementing a solution that only reads data from the server upon demand. IOW, I don't want my form to only appear on screen after all 150,000 rows have been pulled from SQL Server. Thank you very much for your insight.The difficult we do right away... ...the impossible takes slightly longer.
-
#1 - it loads all of the rows into memory at once. DataTable.Load | Reference Source[^] Displaying 150,000 rows on screen in one hit is not a good idea. How is the poor user ever going to find the one row they're interested in? There must be some way to summarise, filter, or page the data to make it easier to consume.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Thanks for your response. I only chose 150,000 as a theoretical number to make my point. There would never be that many rows, but in our main LOB application, some developers in the past have loaded up around 10,000 rows, making the form rather slow to open!
The difficult we do right away... ...the impossible takes slightly longer.
-
I agree with Richard, 150k records to the UI is only valid if you are doing a data dump and there are more efficient way to do that.
Never underestimate the power of human stupidity RAH
Yes, I agree with you and Richard. I'm working on creating a generic way to load the data only on demand. It must be a business object agnostic way. Thanks. :)
The difficult we do right away... ...the impossible takes slightly longer.
-
I'm using ADO.NET. Entity Framework is not an option. Suppose I have an
IDataReaderinstance that was created from calling a stored procedure that returns 150,000 rows. If I create aDataTable, and then callDataTable.Load(IDataReader), does it: 1. Read all available rows into the table at once? 2. Read only those rows that are necessary to return whatever rows I read from theDataTable? 3. Some hybrid of the above two scenarios? I'm interested in implementing a solution that only reads data from the server upon demand. IOW, I don't want my form to only appear on screen after all 150,000 rows have been pulled from SQL Server. Thank you very much for your insight.The difficult we do right away... ...the impossible takes slightly longer.
Richard Andrew x64 wrote:
IOW, I don't want my form to only appear on screen after all 150,000 rows have been pulled from SQL Server.
Then how? You mentioned a "form", so I'd assume WinForms with a DataGridView. In which case you don't use a datatable, but a list. You add to the list from a different thread, in batches of 50. Virtualize the DataGridView. Start the actions as soon as the form is displayed.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
Yes, I agree with you and Richard. I'm working on creating a generic way to load the data only on demand. It must be a business object agnostic way. Thanks. :)
The difficult we do right away... ...the impossible takes slightly longer.
If you are using SQL Server I think there is a method of loading a "page" of data which my be valid. Obviously I have never used it, I limit UI lists to a max of 2k. After that filters become mandatory.
Never underestimate the power of human stupidity RAH
-
If you are using SQL Server I think there is a method of loading a "page" of data which my be valid. Obviously I have never used it, I limit UI lists to a max of 2k. After that filters become mandatory.
Never underestimate the power of human stupidity RAH
We have one form that displays Purchase Orders, and the data goes back to 2009. There are thousands upon thousands of records. How can you limit how many rows you load, and yet still allow the user to type in a column filter string in order to find any of the PO's from any point in time?
The difficult we do right away... ...the impossible takes slightly longer.
-
Richard Andrew x64 wrote:
IOW, I don't want my form to only appear on screen after all 150,000 rows have been pulled from SQL Server.
Then how? You mentioned a "form", so I'd assume WinForms with a DataGridView. In which case you don't use a datatable, but a list. You add to the list from a different thread, in batches of 50. Virtualize the DataGridView. Start the actions as soon as the form is displayed.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
Eddy Vluggen wrote:
You add to the list from a different thread, in batches of 50
Thank you. That's one of the avenues I am exploring. I have to find some way to make our application more responsive while still enabling access to thousands of rows.
The difficult we do right away... ...the impossible takes slightly longer.
-
I'm using ADO.NET. Entity Framework is not an option. Suppose I have an
IDataReaderinstance that was created from calling a stored procedure that returns 150,000 rows. If I create aDataTable, and then callDataTable.Load(IDataReader), does it: 1. Read all available rows into the table at once? 2. Read only those rows that are necessary to return whatever rows I read from theDataTable? 3. Some hybrid of the above two scenarios? I'm interested in implementing a solution that only reads data from the server upon demand. IOW, I don't want my form to only appear on screen after all 150,000 rows have been pulled from SQL Server. Thank you very much for your insight.The difficult we do right away... ...the impossible takes slightly longer.
ADO.Net support paging the data into the dataset if you use an Adapter. Paging Through a Query Result[^] DbDataAdapter.Fill Method (DataSet, Int32, Int32, String) (System.Data.Common)[^]
Wrong is evil and must be defeated. - Jeff Ello
-
Eddy Vluggen wrote:
You add to the list from a different thread, in batches of 50
Thank you. That's one of the avenues I am exploring. I have to find some way to make our application more responsive while still enabling access to thousands of rows.
The difficult we do right away... ...the impossible takes slightly longer.
Loading from a background-thread sounds ideal then; the IDataReader will return an array of objects, and stuffing them into a list is fast. List<object[]> :) Walkthrough: Implementing Virtual Mode in the Windows Forms DataGridView Control[^] - fastest way to display a huge amount of data. ..and searching the list of object-arrays could also be done on a background-thread, simply by going through the list :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
ADO.Net support paging the data into the dataset if you use an Adapter. Paging Through a Query Result[^] DbDataAdapter.Fill Method (DataSet, Int32, Int32, String) (System.Data.Common)[^]
Wrong is evil and must be defeated. - Jeff Ello
Thank you, this looks very promising. :)
The difficult we do right away... ...the impossible takes slightly longer.
