Problem with ASP Page trying to display large number of rows

[abhijitkolhatkar]

hi,
My ASP Page is executing a query which is fetching around 18,000 rows. Each having 61 columns in it.
The page displays only about 300 rows and breaks.
It displays just one line message as bellow:

line : 440

I guessed this is a performance issue and I set Server.ScriptTimeout property to 9000.
After this, the page displays an error message (after taking hell long time) as below:
"
Communication Error
No details given
"

It seems like the server which is processing this page is encountering some problems when it processes such a huge data.
Any one have any suggestions about this?

thanks
abhijit

 

[QatQat]

That is not a huge amount of data to process, it is however a huge amount of data to display and I do not think that any human would find comfortable reading a table with 60 or more columns over the INternet.

To solve the 60 columns problem, not display them all by default but create a page that allows users to select or deselect accessory information to display.

To address the problem of 18000 rows, if that is the normal amount of records that your system returns after a query, and you cannot further refine/filter it, than you should look at using the msowc.dll (MS Office WebComponents) and pivot your results before displaying it. You can use two methods, one which pushes a CLient side CAB file that usually clashes with Internet Explorer security settings, or the other one, completely server side which does not look as good but I recommend it.

Not the easiest route but I used it several times and not impossible after you come to terms with it.

It needs however a good knowledge about Pivot tables.

QatQat

Life is what happens when you are making other plans.

 

[abhijitkolhatkar]

Hi QatQat
Thanks for the suggestions.
I don't have any experience with the msowc.dll. Also, to avoide the platform dependancy, I should use it completely on the serverside.
Can you give me some examples so that it will be clear to me how to use pivot tables and all???

Thanks

 

[QatQat]

First of all, have you got any experience with Pivot tables?

QatQat

Life is what happens when you are making other plans.

 

[DotNetGnat]

i would suggest you to paginate your recordset showing only the 10 most important columns and provide a link for each record to view further details about it...

-DNG

 

[schase]

QatQat and DotNetGnat have good points with limiting what's displaying & paging.

How are you retrieving the data? normal loop through the recordset, or are you using getrows or getstring?

"Ever stop to think, and forget to start again?"

Stuart
A+, Net+, Security+

 

[ksbigfoot]

Howdy abhijit,
I set this parameter at the top of my webpage so that my page could process large amounts of data.

Server.ScriptTimeout = 6000

But as suggested above, maybe rethink what you want to display.

Hope this helps.

 

[Tarwn]

Another method that will speed up your processing by about 7-10 times if your not using it already is the GetRows() method of the recordset object. This function returns a two dimensional array of the data in the recordset.
If your displaying every line exactly from the recordset without doing any processing on the values, you could also look into GetString() which allows you to define a column and row delimiter and is even faster in most tests then GetRows().

I have a page that can return up to 23,000 rows from an access database if you setup the search just right. It uses GetRows and does some minor conditional processing on one of the fields. I just ran it and got a server processing time of 9 seconds and change, with a browser load/rendering time of 63+ seconds.

 

[abhijitkolhatkar]

Hi All,
thanks a ton to ur replies.
Currently, this is how I am doing it.
I open the recordset and display first 2000 rows.
I display a MORE button.
One user clicks on it, I display next 200 rows and so on.
More button will be enabled only when there are still some records to be displayed.

Also,
I have a question regarding the recordset:
CAN I KEEP A RECORDSET IN A SESSION VARIABLE???

thanks
Abhijit

 

[theniteowl]

Definitely look into the GetRows and GetString methods.

If you look up each row then use response.write to build your table row before moving to the next row in the recordset you are spending a LOT of time just in the display. The Response.Write method is very slow. If you are building the whole row then using Response.Write then you are doing it 2000 times per database read and that is a lot. But if you use Response.Write to output each column in your row then you are doing it over 120,000 times to display those 2,000 rows of data.

Using GetString you would retrieve your rows all at once, format them for HTML output and then use a single Response.Write to do the whole thing at once.

Your performance issues may very well be your processing and display of the data, not the retrieval from the database.


Paranoid? ME?? WHO WANTS TO KNOW????

 

[schase]

Yeah exactly.

You can response.flush to be able to get around your timeout - to some degree anyway. But what you are trying to do is made for Getrows or getstring.


Explanation as to why Getrows:

http://www.learnasp.com/advice/whygetrows.asp

Getrows & getstring

http://www.learnasp.com/freebook/asp/dbtablegetrows.aspx

Getrows & GetString are tutorialized there. There is also Getrows numbered to give you a greater degree of visual control on the display.

http://www.learnasp.com/freebook/asp/dbtablegetrowsnonum.aspx

"Ever stop to think, and forget to start again?"

Stuart
A+, Net+, Security+

 

[QatQat]

I still think that returning 2000 rows to screen all at once, whether you do it fast or slow, it still not viable.

A cheap solution, instead of pivot table, would be to output the report to excel and make it available for download; people can hen apply filters and view the report in a more convenient way.

QatQat

Life is what happens when you are making other plans.

 

[abhijitkolhatkar]

Excellent,
U all guys are just great...

I managed to solve the problem by using Pagination.

Thanks A ton

Abhijit