Error in filling up dataset with many number of rows

[manju]

Hi All,

In my ASP.Net application am trying to read into a dataset many number of rows using DataAdapter.Fill method.

The query returns as many as 60,000 rows.

When the numbers of rows are less I dont have any problem. But when there are many rows being returned, it gives me either a "QueryTimeout Expired" error
OR
"WebServer is unavailable check the application event log of the web server".

When I check the event log this is what has been recorder for the error
aspnet_wp.exe (PID: 3764) was recycled because memory consumption exceeded the 301 MB (60 percent of available RAM).


Any help will be appreciated.

[jas4th]

Do you need them all in memory simultaneously? do you need all fields/columns?
How long does the SQL the adapter uses take to run on its own.
Could you briefly explain why you need all 60000 rows?

[manju]

The project being done is for a telecom industry client and the amount of information at times can go upto 4million rows as well. I was reading the other day "The maximum number of rows that a DataTable can store is 16, 777, 216".

Yes the query that I submit needs all those columns and so many rows are needed at times.
When I submit the query in SQL Query manager the results are back in 52seconds.

Any help will be appreciated..

[jas4th]

It may be able to store that many but do you have enough memory on the IIS server machine - and do you need to hold them all at once in memory?

[manju]

Thanks for the response jas4th.
How can I increase the memory of IIS??

I am loading all the results into a dataset and eventually displaying them in a datagrid from where users can download into textfiles.

[jas4th]

Do you need to hold them all in memory at once because as this is a web based app you are presumably going to multiply the server resource usage by the number of simulataneous users and whatever other apps are running.
You can put more physiacal memory and/or available disk space - not sure what settings are available to state how much max resources are available to it.
But I'm surprised you don't filter your queries in your SQL and SP's.

[manju]

Thanks for the reply jas4th.

The queries are filtered and then only we reach the number of 60,000. There are billions of rows on which the queries run against and retrieve the data.

The database is not SQL server but Teradata to handle so much data.

[jas4th]

How are you establishing the connection to the datasource?

[manju]

odbc connection

[jas4th]

Ouch - don't know this would be good idea to access so much data. Perhaps others could advise whether this is practical.
You could double check the timeout in your conn string.

[manju]

Is there any restriction on the amount of data retrieved when we connect using odbc connection????

[billcoupe]

I'm curious, it's been suggested that the number of returned rows be 'limited' somehow. If Mike did that, wouldn't he have to continually 're-query' the server as the user attempted to page through their results?

When there are potentially billions of rows in the source data, and you're allowing users to decide by what criteria their results are returned, how is Mike's program supposed to 'decide' what should actually be returned?

Now, I can understand returning the first (or top) say 500 rows to allow the user to verify that what they 'thought' they asked for, is what they got back... but when they want to export those results to a 'TXT' or 'XML' file, he's still going to have to return all the rows at some point.

In his situation, I'd want to see 2Gb (or more) of RAM on the server, along with a few 100Gb of free disk 'workspace'... I'd set the timeout on the SQL connection to 3 or 4 times what he's experiencing in SQL times in testing to avoid the 'TimeOut' error.

Also, I'd want to trap the Timeout error and return a 'Server Busy' type message to the user, requesting they try again in a few minutes.

[jas4th]

Sorry missed the bit about downloading to client machine as file.
Can you use a readonly datareader rather than full dataset to hold it (I may be barking up this tree too much but it goes against the grain to routinely hold so much data in the dtaaset for me).

[manju]

So instead of using an ODBCDataAdapter you want me to use a DataReader to fill my dataset??

The results are displayed to the user in a DataGrid in a new window and from there on he has an option to download them to text files or he may save the IE page as it is.

[jas4th]

MS:

Use the DataReader in your application if you:

Do not need to cache the data.
Are processing a set of results too large to fit into memory.
Need to quickly access data once, in a forward-only and read-only manner.

[manju]

Thanks jas4th.

But I dont understand how does it matter if you use an adapter or reader?

also can you please tell me how I can return back my datareader data from a function.
Right now I am returning back the DataSet.

How can I return back the DataReader from my ClassLibrary into my WebApplication??? As I need to close my DataReader at end of function??

Thanks for all the help.

[jas4th]

I would suggest you write a test of this to see if it solves the reading of the 60000 rows - basically it reduces the overheads of the dataset that you are creating in your current solution - however it has less functionality elsewhere.
But first we need to determine where the problem lies.
When you open the datareader you should be able to cycle through and read each row before the connecvtion has brought across all the results.
Let us know how you get on.

[billcoupe]

jas4th... I couldn't agree more about not holding large datasets, I try to avoid that as well, whenever possible. I'm wondering how Manju can cover his presentation/client interaction needs with a DataReader as I've never tried to use one in an 'interactive' situation like a grid.

The problem I have with a datareader, in this context, is that it's a one way, forward read item, how can he present the data to the client, and allow them to scroll around within it in a reader?

Manju, the reader is 'like' a dataset in terms of using it, in the sense it's an object, as is the dataset, the exception is that it can only be read beginning to end, not interactively. Also it's read only (although that's not an issue in this case)...

DataReaders are very fast and extremely 'lean' in operation... If jas4th has some insight into using them in the presentation layer, he's going to allow me to really speed up a couple of my apps! I'll admit I've never tried using one there as I understood they wouldn't work in that setting... I jumped into this thread in hopes I could see my understanding is wrong!! I have a couple apps that would seriously benefit from a speed boost like this!

I regularly use datareaders to extract information I intend to process further, and in ways that are not possible within a standard SQL statement. I've not attempted to display a DataReader in a grid, so I'm unsure how that actually works.

Thanks for letting me hop in here.

[jas4th]

What I would say generally is that if I was trying to display 60000 rows I would assume the grid must be paged and therefore grab the relevant portions as the user pages through rather than the complete set (if its such an overhead). Your quite right datareaders are functionally restricted as I think I mentioned.
My line was to see where the issue is - it could be the connection transferring data from the datasource or perhaps in the IIS process running out of resources once the dataset is being filled. The trouble with ODBC is that it isn't as sophisticated and packs in under stress.

[manju]

Thanks a lot jas4th and Bill.

I tried retrieving results using a DataReader and it worked fine though they were pauses in the while myReader.Read loop but it did loop thru all the 60,000 rows.

As jas4th suggested I can get blocks of data onto datagrid. But thats when user is viewing datagrid in browser. What if he wants to download the entire results onto a textfile - which I tell you 90% of the users will do --

In that case I need to get all the results at one go..
Is there any other way I can go about this problem??

The reason am not using oledb connection is cos Teradata has some issues pending with oledb connections to the database.

Thanks for all your help and time in helping me out. I really appreciate it.

[jas4th]

Could you write to an XML file perhaps and they download that (not thought this through yet)?

[manju]

hmm thats a good suggestion.. my minds just blocked at this time
I shall try getting the results into a datagrid and saving the entire resultset into an xml file.

Shall let you know how it goes in couple of hours time.

Thanks a bunch.

[jas4th]

As its 5pm here don't be offended if I don't reply till Monday.
Have a good weekend.

[manju]

thanks and have a great weekend..

[billcoupe]

Originally posted by manju
As jas4th suggested I can get blocks of data onto datagrid. But thats when user is viewing datagrid in browser. What if he wants to download the entire results onto a textfile - which I tell you 90% of the users will do --

In that case I need to get all the results at one go..
Is there any other way I can go about this problem??

I saw jas4th suggested writing to an XML file, that would be my 1st suggestion as well! I use XML a lot to write off resulting datasets for later use (export) by the user... if you're able to 'page' the results I'd love to see how you do that!... like I said, if I can use the datareader and bind it to a grid for display... I could really speed up a few of my apps!

Thanks for this topic, it could prove very useful in the long run!

[billcoupe]

Interestingly enough, I've just found a similar problem, but with the SQL (OleDb) dataAdapter.

I've got an app that reads data from a call accounting system, and presents it in a datagrid for review by the user before they decide to further summarize it, send it to a Crystal Report, or export it to XML.

It's been working fine for some time... most SQL results were in the 10-15K rows range.

I added access to the actual phone 'state' files this week, and when experimenting with looking at records from the first of the year until now the recordset being returned was in excess of 100K rows.

The app returns and processes the initial request just fine. But, if you attempt to run a second, even if it's smaller, the .Fill fails with an 'Unhandled SQL Exception'

I've tried a Dataset.Clear(), clearing all the datasources and bindings from the grids... and it still fails.

I'm thinking this is also a memory issue, but can't think of what else I should be doing to get .Net to release the resource areas so the subsequent SQL's don't fail.

I'd appreciate any thoughts you folks might have.

[manju]

hi,

Am still figuring out how to pass my datareader from my class library to my web application and populate a datagrid.. any ideas???? I would not like to write code to access my database in the code behind aspx.cs file.

also am facing a weird kinda problem at times. When I submit my query it gives me the message 'Query Time out Expired' within 30seconds.

Anyone have an idea why it happens??
When i submit the same query in SQL Manager I get the results!!!!

[billcoupe]

Hi manju -

I've experienced that 'Time-out' problem from time to time, in most cases I've been able to get around it by altering the SQL. It seems, to me, to be most prevelant when I'm running SQL statements that create fields for the resulting dataset. I know there's a 'Timeout' setting you can 'tweak' in the connection/dataadapter settings I think the default is fairly low, like 30-40 seconds.

I should have an opportunity to test this 'rows' issue later today. I'm doing a demo and beta install this afternoon and a couple of the testing machines have more than a Gig of memory.

I will let you know what I find.

This al does trouble me however as I regularly present large amounts of data to users in a 'grid' view as they're very accustomed to looking at data that way. Most often it's just a review, rarely any type of 'update'. I'd never seen, or heard of this until you mentioned it... now I'm experiencing it too!

For what it's worth, my app is a WindowsForms App, not a web app, but the problem is very similar.