Poor performance with large datasets

[Luther Miller]

I have noticed that performance is very poor when a web service
returns a large dataset.

Further testing has shown that the XML version of the dataset is
approximately 1.3GB in size - about 10x the binary representation of
the data. It is the transfer of this data over the network that is
causing the performance problem.

We are pulling a large result set into an Excel application for pivot
table processing.

Connecting directly to the database using ADO takes only about 30
seconds to finish retrieving all the data - presumably because the
data is coming over efficiently in binary format.

We also looked into using a 3rd party product to compress the XML
dataset before returning it from the web service, but that too is
prohibitively slow.

How is Microsoft recommending that applications deal with large
datasets? How are other people dealing with this issue (if at all)? I
realize that .NET 2.0 is supposed to support binary data over web
services, but this needs to be a 1.1 solution.

[Lee_Nover]

> We also looked into using a 3rd party product to compress the XML

> dataset before returning it from the web service, but that too is
> prohibitively slow.

check [url]www.remobjects.com[/url] .. .NET SDK 3 is still in beta but you can get
beta access if you ask nicely :)
their binary format supports compression + encryption and is really fast
I've used RO SDK for over a year (with delphi) and have no complaints .. :)
current RO SDK 2 is only a client version and only supports their binary
format
contact them and I'm sure you can arrange something :)

[mohan@fjb.com.sg]

Sorry, this is not an answer.. but

I too am facing the same problem.. Having tried various techniques, asp.net can't handle huge Datasets..It simply recycles... Now how Can I make my application work? I can't tell my people to buy .Net 2.0...

My DataSets will generally contain 100,000 records with relations... Its killing ...

I tried storing data in Session, Cache, other "Compact" ing techniques.. No use!!

Can any expert help pleaseeeee......



************************************************** ********************
Sent via Fuzzy Software @ [url]http://www.fuzzysoftware.com/[/url]
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

[Dan Rogers]

Hi Luther,

I think the crux of the issue comes down to why are you deciding to
implement this large-data-set requirement on a Web Service stack? When I
think thru all of the distributed computing (WS is distributed computing)
wisdom that I've encountered over the years, one of the repeating themes is
"the farther away you are, the smaller your data transmission requirements
should be". In a web service world, requests are likely to be more
serviceable if you keep the payloads of the requests and responses small.

If you find yourself needing to send large binary payloads, perhaps the
answer is to send large binary payloads. You can use remoting, save to
file and use things like FTP, or open direct streams for these kind of
"batch transfers". With web services, if you are using the WS interface to
transfer what amounts to reference copies of data, some might suggest that
a different approach to managing the publication of large reference data
sets is called for.

Compression to binary will certainly reduce the number of packets required,
and thus effect your transfer speed by a factor of the compression ratio.
But compression adds several complicating factors. An easy way to get mass
compression is to use some of the hardware solutions available at both ends
of predictable high latency connections (I say high latency because of the
end to end response time). In a controlled corporate network setting, or
high volume commercial web infrastructures, this is economically feasible
and eliminates the processing intensive and coding intensive overhead of
both sides having to know some new compression protocol.

Products from Netscalar, Cisco and F5 all can do what you want without
adding any coding overhead to your network. Another option is faster
network connections as well (gigabit ethernet, etc). I know these all may
seem daunting to the project manager/budget minded, but they are a real
part of the overall solution you should consider before you add in a
complicated software fix. One real advantage is to the budget - these are
typically capital budget items and not R&D expenses, so that added cost can
be amoritized more rapidly.

Hardware solutions to this issue also has the added benefit of degrading
gracefully. Requests from clients that are outside of your corporate
network (and thus who cannot make the efficient direct binary calls) can
still come in thru an HTTP security gateway with a normal web service
request. Yes, they will see large files, and the requisite effect of
aggregate bandwidth and latency, but it'll work without having to code a
special version of the application.

I hope this adds some aspects to your trade-off matrix,

Dan Rogers
Microsoft Corporation
--------------------

>From: [email]usenetlex@yahoo.com[/email] (Luther Miller)
>Newsgroups: microsoft.public.dotnet.framework.aspnet.webservic es
>Subject: Poor performance with large datasets
>Date: 1 Oct 2004 09:50:01 -0700
>Organization: [url]http://groups.google.com[/url]
>Lines: 23
>Message-ID: <fe596fc.0410010850.2a69ccce@posting.google.com>
>NNTP-Posting-Host: 66.238.191.14
>Content-Type: text/plain; charset=ISO-8859-1
>Content-Transfer-Encoding: 8bit
>X-Trace: posting.google.com 1096649401 13991 127.0.0.1 (1 Oct 2004

16:50:01 GMT)

>X-Complaints-To: [email]groups-abuse@google.com[/email]
>NNTP-Posting-Date: Fri, 1 Oct 2004 16:50:01 +0000 (UTC)
>Path:

cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFT NGP08.phx.gbl!newsfeed00.s
ul.t-online.de!t-online.de!border2.nntp.dca.giganews.com!border1.nn tp.dca.gi
ganews.com!nntp.giganews.com!news.glorb.com!postne ws1.google.com!not-for-mai
l

>Xref: cpmsftngxa06.phx.gbl

microsoft.public.dotnet.framework.aspnet.webservic es:25711

>X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.webservic es
>
>I have noticed that performance is very poor when a web service
>returns a large dataset.
>
>Further testing has shown that the XML version of the dataset is
>approximately 1.3GB in size - about 10x the binary representation of
>the data. It is the transfer of this data over the network that is
>causing the performance problem.
>
>We are pulling a large result set into an Excel application for pivot
>table processing.
>
>Connecting directly to the database using ADO takes only about 30
>seconds to finish retrieving all the data - presumably because the
>data is coming over efficiently in binary format.
>
>We also looked into using a 3rd party product to compress the XML
>dataset before returning it from the web service, but that too is
>prohibitively slow.
>
>How is Microsoft recommending that applications deal with large
>datasets? How are other people dealing with this issue (if at all)? I
>realize that .NET 2.0 is supposed to support binary data over web
>services, but this needs to be a 1.1 solution.
>

[rao_ch@yahoo.com]

I always proude about my coding that I always access data very careful. But, once my application change from LAN to WAN (via Leased Line), then the performance counts, and the application performance is very poor. I surprised and checked what happend to my application. When debuging, I found poor data access coding of myself as example as follows:

When I want to check if the account exist or not I checked as follows:

select * from table where account number=125632

It works very fine no doubt, but the result is very poos because the table definetions as follows:
Branch: 1 byte,
Head: 2 bytes
account No.:4 bytes
Name: 100 bytes
opening date: 4 bytes:
...
...
The tables defenition has almost 150 bytes (approximately):

So when I access, all 150 bytes (with IP address and encryption of router to router it may be more than 150 bytes) transfer from Server to Node view limited bandwidth cable: As the every BIT of data counts and the local ISP control the speed of Leased Line at limited speed, so, the result is Network trafic:

Than I rewrite the above code as follows:

select count(*) from table where account number=125632

Simply it solves the problem, and I gain speed a lot, then I almost replace "SELECT * FROM" with wanted columns ("SELECT NAME,OPENING DATA FROM": The speed of the Application is surprisingly improved verymuch.

So, I have to change my data access techniques appropriately and I have to sharp my programming skills little more.

Every BIT is counts, so beware of data transfer via WAN and Internet:

regards

Ch.Appa Rao,
Programmer