can ASP table display 200 columns, 500,000 rows?

[Rich]

I'm sure it sounds kinda nutty to display 200 columns and
500,000 rows of data. But I have been pulling data from a
Lotus Notes database into Sql Server for a while now, but
Lotus Notes is starting to crack, columns getting
corrupted. Can't handle the volume of data and number of
columns. Sql Server has no problem. But displaying the
data is the big deal. The end users want to be able to
scroll acros a page to the colum of their choice, or be
able to scroll up or down. I was thinking of breaking up
the table into section, but this would reqire additional
clicking to get to the next section of the table. Would
it be feasable to have a 200 column table in asp? or
should I stick with the section idea? I have to tell my
dept something. They started out with Lotus Notes but
couldn't query the data. I have been suggesting to
migrate the project entirely to Sql Server/IIS. Notes can
at least display all the columns. Suggestions appreciated.

Thanks,
Rich

 

[Ray at]

I'd say it's just a matter of personal preference after considering the
drawbacks of having that large or a response and putting that extra strain
on the server. When you want a drink of water, do you fill a glass and take
that with you back to your sofa, or do you pick up the entire river and try
to lug that back to your sofa?

Ray at work

 

[Bob Barrows]

I'm sure it sounds kinda nutty to display 200 columns and
500,000 rows of data. But I have been pulling data from a
Lotus Notes database into Sql Server for a while now, but
Lotus Notes is starting to crack, columns getting
corrupted. Can't handle the volume of data and number of
columns. Sql Server has no problem. But displaying the
data is the big deal. The end users want to be able to
scroll acros a page to the colum of their choice, or be
able to scroll up or down. I was thinking of breaking up
the table into section, but this would reqire additional
clicking to get to the next section of the table. Would
it be feasable to have a 200 column table in asp? or
should I stick with the section idea? I have to tell my
dept something. They started out with Lotus Notes but
couldn't query the data. I have been suggesting to
migrate the project entirely to Sql Server/IIS. Notes can
at least display all the columns. Suggestions appreciated.

Thanks,
Rich

I like the "section" idea. The user would have to click something anyways to
do the scroll. What difference if they click a scrollbar or a button?

You might be able to sell it by giving them a dropdown of column names -
when they select one, it will display the appropriate "section"

I would be very leary of trying to stream 500,000 rows to a the client in
one fell swoop.It's kind of ridiculous, don't you think? How is having that
many rows on the screen at one time of any use to anyone?

Bob Barrows

 

[Aaron Bertrand - MVP]

I'm sure it sounds kinda nutty to display 200 columns and

500,000 rows of data.

Why would you subject a user to this cruel torture?

 

[Phill. W]

I'm sure it sounds kinda nutty to display 200 columns and
500,000 rows of data.

You Betcha!!

Just consider the HTML needed to display it:

"<table>", plus
500,000 lots of "<tr>" with
200 lots of "<td>" and "</td>" within that, plus
500,000 lots of "</tr>", plus the final
"</table>"

that's:

7 + 500,000 * ( 4 + 200 * ( 4 + 5 ) + 5 ) + 8 bytes
= 904,500,015 bytes
~= 0.83 Gigabytes!!

and that's without *any* actual data in it!!

Transmission time: (not counting /building/ it on the server)

100Mbit ethernet 72 seconds
56Kbit modem: just shy of 36 hours.

Not quite in Our Friends in Redmond's league - just under seven
*days* for the /single-file/ download of Visual Studio 2003 - but
getting there... ;-)

Regards,
Phill W.

and yes; I'm /very/ familiar with dial-up download times... :-(

 

[Mark Schupp]

Is the data static or does it differ from one user to the next (or from one
query to the next)?
Is this on an intranet where the clients could have direct access to the
SQLServer database?
Which browsers must be supported? Can you limit support to a particular
browser?

 

[Rich]

Thank you all for your replies. I guess I will go with
the section idea. And the suggestion for the dropdown box
listing all the columns is real cool too. Matter of fact,
I tried out Bob's ListDemo sample for the dropdown
listbox. works great!

Thanks again for all the suggestions.

Rich

 

[Rich]

Because Lotus Notes was able to do this with up to like
400,000 recs. But now starting to fail. Thus, I started
pushing ASP. Each record has a Datefield I could filter
by or an alphanumeric ID. I guess we don't really need to
display all 500,000 recs at once. Just that even though
Notes could sort of do it (Notes 5) it doesn't really have
built in filtering. So you get the whole thing. People
kinda got used to that. I guess I need to push - that
with Sql Server and ASP you can actually filter stuff and
not have to deal with the whole potatoe at once.

 

[Rich]

Yes, this data is on an intranet, and yes, I could hook
the intended audience up with ms Access to view the data
or custom apps, but for data entry (large company, over
130,000 employees) we have people entering data all over
the place. Right now everyone (for this project) uses
Lotus Notes for data entry and viewing (although I pull
the data in to Sql Server daily from Notes for creating
reports). You have the one Notes app which is accessible
anywhere within the company intranet. I'm thinking why
not migrate this straight to ASP/Sql Server, then I don't
have to pull in any data from Notes. And with ASP only
need the one app. Much easier to maintain than a bunch of
custom apps distributed all over the place.

 

[Aaron Bertrand - MVP]

Because Lotus Notes was able to do this with up to like

400,000 recs. But now starting to fail.

Whether you're using Lotus Notes, or ASP, or ColdFusion, or Flash, or a
mainframe, or a stone tablet... 400,000 rows is WAY too many for any human
to handle (never mind the actual technological limitations outlined by
others). Imagine hitting a web page that had 400,000 rows in a table. Does
it really sound fun? This is why when you search for things on the web, or
on google, they present (by default) 10 items per page, not 100,000. If you
get too many results, you can simply narrow down your search. This is the
angle you should be approaching from, in my opinion, rather than asking,
"how can I still force them to look at 500,000 rows at once!?!?!?"

 

[Rich]

The biggest element/obstacle for me is the human element.
I write code. Not much for shmoozing. My company
(supposedly the largest hmo in US) has been around for
quite a while and has not focused as much energy on
computer tech. So I was one just a few techy types to
join on. These are hard people to sell on new technology
(heck, the stuff I'm trying to push is already 5+ years
old - wait till I start pushing VS.net).

Lotus Notes has been the mainstay here for quite a few
years. They are comfortable with it and it's
limitations. If they plan on keeping up with/or ahead of
the competition they will eventually have to step up to
newer technology.

I had to do the biggest song and dance just to get them to
use Sql Server for reporting. Before, they were using
Access (1 gig limit) at 110% of capacity - Access kept
dying. Now using sql Server (only 6 gigs of data) we are
at 0.6% of capacity and reports run smooth and quick. So
for my next trick, I need to convince them that IIS is way
more suited for their needs than Domino (notes server)
since all the data ends up in Microsoft based reports.
Keep everything in its native element.

 

[John Beschler]

Ray,

Wouldn't it make more sense to move the sofa to the river?

Sorry, I couldn't resist!



John

 

[Ray at]

Ha! Maybe. But then you'd have to move the TV, the receiver, the TiVo, the
speakers, the cats, etc.

Ray at work

 

[Mark Schupp]

The problem you are going to see with ASP is that when the user scrolls
their view (horizontally or vertically) it will not scroll smoothly (as I
assume the Lotus Notes app does). The view will jump to the next display
area after a pause while the page is generated. If this is acceptable then
have a look at some of the "record paging" approaches on the ASP sites
(www.aspfaq.com, www.asp101.com etc).

If the users expect smooth scrolling and are on the network where they could
connect to the database with a traditional client-server application you
might want to look into building a VB application with a grid control of
some kind in it.

 

[Tim Slattery]

The problem you are going to see with ASP is that when the user scrolls
their view (horizontally or vertically) it will not scroll smoothly (as I
assume the Lotus Notes app does). The view will jump to the next display
area after a pause while the page is generated.

That would have NOTHING to do with ASP!!!! The ASP code runs on the
server. Once that ends, the HTML it generates is sent to the client,
where it's rendered on the screen. Scrolling happens on the client,
and it's up to the client whether it scrolls smoothly or jumpily. ASP
has finished its job by the time you see that problem.

 

[Jeff Cochran]

I'm sure it sounds kinda nutty to display 200 columns and
500,000 rows of data. But I have been pulling data from a
Lotus Notes database into Sql Server for a while now, but
Lotus Notes is starting to crack, columns getting
corrupted. Can't handle the volume of data and number of
columns. Sql Server has no problem. But displaying the
data is the big deal. The end users want to be able to
scroll acros a page to the colum of their choice, or be
able to scroll up or down.

Why not use a combination of SQL views, along with some method of
allowing the user to select the columns seen and the range of rows
returned. What you describe is like a user looking at a phone book
for the information they need. What you really want to give them is a
search interface so you don't need to display the entire phone book,
just the data in it that meets what the user is looking for.

Jeff

 

[Mark Schupp]

What I was referring to was that, unless all 200 columns (500,000 rows each)
are loaded into a single page then "scrolling" will involve returning to the
server for more data. This introduces the "jump" in the display.

There are probably ways to use remote-scripting and JavaScript to do this
smoothly but (in my highly biased opinion) web-browsers and ASP were never
intended to simulate the Notes application described by the original post (I
could be wrong about how the Notes application displays data of course, it
may already be "jumpy" in which case the ASP application could simulate it
fairly well).

 

[Roland Hall]

Still more feasible than moving the river. Just move the whole house and
have water front property. One more thing... You drink from the river?

 

[Roland Hall]

For your next trick...give them the data unsorted and let them search
through 500,000 records to find what they want. They might be a little more
receptive to selecting what they want before retrieving it from the
database.

One of the benefits of SQL databases is that they pass records, not files.
You appear to be wanting to pass the whole database * # of users. Perhaps
you should inform IT that it's time to get rid of the ARCnet too to speed
things up.

The biggest element/obstacle for me is the human element.

This is always the biggest obstacle, usually wearing an 'upper
mismanagement' name tag who is resistant to change a.k.a. CFO.

I write code. Not much for shmoozing.

PowerPoint goes a long way. You must have one element included in all
presentations: This will save us $_________.

My company
(supposedly the largest hmo in US) has been around for
quite a while and has not focused as much energy on
computer tech.

A large organization that doesn't have any trouble giving away millions to
upper management that refuses to keep up with technology. This must be
something new.

So I was one just a few techy types to
join on. These are hard people to sell on new technology
(heck, the stuff I'm trying to push is already 5+ years
old - wait till I start pushing VS.net).

Upper management bonuses are based on profitability, excluding Enron, Arthur
Andersen, WorldCom, etc. Spending money without a clear understanding that
it will save TIME, which has a direct connection to ROI, comes off as
expenditure, not revenue.

Lotus Notes has been the mainstay here for quite a few
years.

Everyone bow your heads. Let us pray...

They are comfortable with it and it's
limitations.

.... as they are with current management and their limitations.

If they plan on keeping up with/or ahead of
the competition they will eventually have to step up to
newer technology.

Since they're already 5 years behind, the competition must be also.

I had to do the biggest song and dance just to get them to
use Sql Server for reporting. Before, they were using
Access (1 gig limit) at 110% of capacity - Access kept
dying. Now using sql Server (only 6 gigs of data) we are
at 0.6% of capacity and reports run smooth and quick.

And the still don't know who you are or what you're doing for all that money
they're paying you.

So for my next trick, I need to convince them that IIS is way
more suited for their needs than Domino (notes server)
since all the data ends up in Microsoft based reports.
Keep everything in its native element.

I have found giving someone what they think they want instead of convincing
them of what they need usually backfires.

"I want to run 386 NetWare on all of my servers."
"But, your servers are all 286s. You need to upgrade!"
"I think that's a scam just to get more of my money. Make it work!"
"OK! Oh, while I'm at it, want me to prove Einstein's theory of
relativity?"

 

[Roland Hall]

Ok, by a show of hands... how many of you are still using dialup on the
local LAN to get to the server?