I am trying to select all the columns in my table which have about 2.5million records.
But it throws me the above exception after some time of execution.How to solve this problem
adapter.SelectCommand = new SqlCommand("SELECT * from Dwh_staging_table", con1);
adapter.Fill(DataSet2, "Transformed_Table");
I guess you are dealing with some custom build Data Warehouse solution, that means huge amounts of data. Whatever you try to do, you shouldn't be loading all the data from database to application in order to calculate some numbers in staging table.
The best thing you can do is to calculate whatever you need before you put data to Dwh_staging_table, so the problem is solved before it happens. If this it not possible and you already loaded data to database, you should do all the processing in place, in database (e.g. using hated Stored Procedures).
In general, when you are dealing with huge amounts of data, moving the data around is your biggest enemy. Try to solve all your problems at the place where the data are now, without unnecessary transfer.
If you want to anyway load the data back to c# code (what I don't advice), try to do everything without materialising all the data in memory. Create repository function which returns IEnumerable, which will be internally using yield return, so the whole collection of data is never materialised.
And if you still insist on materializing data in some collection (what I don't advice even more), look at some collections which are not using sequential blocks of memory. Using collections like array, List or DataSet will result in higher chance of out of memmory exception. Try to use something like LinkedList or even better some chunked LinkedList of arrays (almost like paging which was suggested in other post).
EDIT: From what you said
i have some missing values in the table i want to fill some columns afterwards using the avg techninque
it sounds to me like something what should be possible just by one UPDATE statement of the staging table in database. Not sure what exactly you want (e.g. I want to set to AvgMetric averaged value of Metric column grouped over Category column). In that case it would look like:
WITH t AS (
SELECT st.[Category]
,st.[AvgMetric]
,AVG(st.[Metric]) OVER (PARTITION BY [st.Category] AS [CalculatedAvgMetric]
FROM [Dwh_staging_table] st
)
UPDATE t
SET [AvgMetric] = [CalculatedAvgMetric]
The obvious answer would be to reduce the data set returned. Do you need all the columns?
How about paging the data? Check out the row_number() function.
Use Custom Paging.Fetch Only limited record in one time
