CODEWITHSUNDEEP
c#ado.net
InfoLearner
InfoLearner

Reputation: 15608

Which is the Fastest Data Collection?

In a web application, I have to select ~1 million rows from a SQL view. These million rows are then used to display a bar chart with drill down functionality.

I am bringing all of the data in one go and letting the user drill down on the data collection that is held in the memory.

I am using ADO.Net (SqlCommand) to get data from the database.

How do I store the data in the data collection that provides fastest data access? Is DataTable better or SqlReader or Arrays or List? Please suggest

Upvotes: 0

Views: 496

Answers (1)

3Dave
3Dave

Reputation: 29051

Why do you need to let the user drill down through ~1M records?

Typically, you'd retrieve an unfiltered dataset if the user needs to see all of it at once, and drill down in the UI. However, with so many records, there's no way that they'd be able to take in the whole set unless, perhaps, you're displaying them graphically, like GIS or other graphical data points.

You may be better off asking yourself if you can initially show them a summary/aggregate view (which you could obtain through a stored procedure), and let them drill down through that by requerying the database with whatever filter parameters they select.

The SQL server is very good at handling this type of operation. Let your investment work for you.

Upvotes: 1

Related Questions