-1

I am using devexpress grid view. I have 2,800,000 records in sql database. I gave a query like below:

SELECT 
   YEAR(Time) Year
  ,MONTH(Time) Month
  ,[StoreID]
  ,[TransactionNumber]
  ,[BatchNumber]
  ,[CustomerID]
  ,[CashierID]
  ,[Total]
FROM [HQMatajer].[dbo].[Transaction]
where YEAR(Time)=2015

this query executed and it tooks 15 sec to give result in sql server. But when i try to run in asp.net grid view either by using sqldatasource or dynamically it gave me system.OutOfMemoryException. this exception occur when run with sqldatasource.

second error

This error for dynamically executed

this is will not display all the records in single page. This result will display in 1000's of pages

mohamed faisal
  • 1
  • 3
  • 2
    The solution is to not display 2.8 million records on a single page. – DavidG Dec 08 '16 at 13:08
  • Add more memory, display less records. I don't think adding more memory is a feasible option ^^ – Kritner Dec 08 '16 at 13:10
  • Are you expecting your users to scroll though a grid with 2.8M records? How long did it take you to view all the data? What are users going to do with this data? – Dan Guzman Dec 08 '16 at 13:14
  • To overcome this you must implement paging both to your grid and to your query. If you are using SQL Server 2012 or newer check out this [link](https://msdn.microsoft.com/en-us/library/ms188385.aspx). – S.Karras Dec 08 '16 at 13:16
  • @DavidG sorry for late reply, this will not diplay all the records in a single page. It will display 50 records on first page. It has paging – mohamed faisal Dec 08 '16 at 13:39
  • You say you have 2.8m records but its not clear how many of them have a year of 2015? ok it is now :) – Fred Dec 08 '16 at 13:39
  • @mohamedfaisal yes, but are you doing paging on the client, with the client having all of the data available, or are you doing paging on the server, so when you select a different page the client has to then fetch that page's data? if you're doing the former, that's likely the issue – Kritner Dec 08 '16 at 13:42
  • @Kritner Yes, you are right. Will it work if i fetch 30k records?. I changed the query which can fetch less than 30k. thats too it shows that kind of error. and i already retrieved nearly 29k records in a different page. that page is worked. why in this page? – mohamed faisal Dec 08 '16 at 13:58
  • just fetch the number of records that are going to be on a single page – Kritner Dec 08 '16 at 14:00

1 Answers1

1

Well, an OutOfMemoryException is hard to solve in an easy way. That is, the reason for the exception.

If you look closely to your approach you'll see that you are trying to fetch 2.8 million rows and put it on a website. That means you are trying to build a page with 2.8 million rows, not talking about the overhead of the rest of the page. If you would use the 2.8 million rows in a code snippet that is 5 lines long, you'll end up with 5x2.8 million lines.. That's too much to display.

Best approach would be to redesign your webpage. Do you need 2.8 million rows in 1 page? My answer would be HELL NO! It's too much too process on a machine, it's too much to process for a human being, it's just too much.

Work with smaller sets!

Mark Kremers
  • 1,669
  • 12
  • 20