ASP + SQL Loads Really Slow Over Intranet

Hi guys,
I've created a web application using ASP together with SQL Server as
our db source, running through IIS 6 on a Winows Server 2003 platform.
This application retrieves a list of customer codes from our db, so
records returned could be as many as 2000+ for any single transaction.
The application runs fine for users from the same state. However, our
interstate colleagues have notice that it takes more than 3-4mins for
the page to load, while it only takes me < 2secs to load.
Our intranet server is located in the same state as I, so anyone from
within this state has no problems loading the page. All other states
are finding it unbearable.
I've done some debugging, and it appears to be a server factor.
I saved the page with the longest list to a local drive and opened it
locally in IE and it loads quickly.
Does anyone have any suggestions as to how to speed this application up
for our interstate users?
Any ideas would appreciated.
Thanks,
ShawnHi
Returning 2000+ customer codes and putting it into a dropdown will make any
application unbearable. How do you find what you are looking for with a drop
down like that?
How are you retrieving the codes, a select * from ...or a select CustomerID
from ...?
Make sure you are not doing any data binding.
If the application is fast in the local state, it means that SQL Server does
all it's work in under 2 seconds, no matter what state, and then pushing the
resultant web page over the wire is your performance bottleneck. How big is
the resultant page? Anything more than 100Kb is generally considered bad.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Shawn H" <ShawnStyler@.gmail.com> wrote in message
news:1112934431.434915.93490@.o13g2000cwo.googlegroups.com...
> Hi guys,
>
> I've created a web application using ASP together with SQL Server as
> our db source, running through IIS 6 on a Winows Server 2003 platform.
>
> This application retrieves a list of customer codes from our db, so
> records returned could be as many as 2000+ for any single transaction.
>
> The application runs fine for users from the same state. However, our
> interstate colleagues have notice that it takes more than 3-4mins for
> the page to load, while it only takes me < 2secs to load.
>
> Our intranet server is located in the same state as I, so anyone from
> within this state has no problems loading the page. All other states
> are finding it unbearable.
>
> I've done some debugging, and it appears to be a server factor.
> I saved the page with the longest list to a local drive and opened it
> locally in IE and it loads quickly.
>
> Does anyone have any suggestions as to how to speed this application up
> for our interstate users?
> Any ideas would appreciated.
>
> Thanks,
> Shawn
>|||Hi Mike,
I'm not sure where you got the idea that I'm putting all the records
into a drop-down list, coz all I'm doing is just puting it into a
table.
I forgot to mention that to retrieve the records, I call a stored
procedure that does this:
"select fldCustCode, fldCustName (etc..)
from tbCust
where fldCustCode = @.custcode"
I think you might be right about the bottleneck being it has to send
the page back over the network.
I did a quick check, and the file size is just a little over 110+kB.
Is there anyway I can get around this?
Somehow I can reduce/compress the file size while it gets sent over the
network? (if thats even possible)
Regards,
Shawn
Mike Epprecht (SQL MVP) wrote:
> Hi
> Returning 2000+ customer codes and putting it into a dropdown will
make any
> application unbearable. How do you find what you are looking for with
a drop
> down like that?
> How are you retrieving the codes, a select * from ...or a select
CustomerID
> from ...?
> Make sure you are not doing any data binding.
> If the application is fast in the local state, it means that SQL
Server does
> all it's work in under 2 seconds, no matter what state, and then
pushing the
> resultant web page over the wire is your performance bottleneck. How
big is
> the resultant page? Anything more than 100Kb is generally considered
bad.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Shawn H" <ShawnStyler@.gmail.com> wrote in message
> news:1112934431.434915.93490@.o13g2000cwo.googlegroups.com...
as
platform.
transaction.
our
for
from
states
it
application up|||Shawn wrote on 7 Apr 2005 23:56:13 -0700:

> Hi Mike,
> I'm not sure where you got the idea that I'm putting all the records
> into a drop-down list, coz all I'm doing is just puting it into a
> table.
> I forgot to mention that to retrieve the records, I call a stored
> procedure that does this:
> "select fldCustCode, fldCustName (etc..)
> from tbCust
> where fldCustCode = @.custcode"
> I think you might be right about the bottleneck being it has to send
> the page back over the network.
> I did a quick check, and the file size is just a little over 110+kB.
Large tables are really noticeable when the bottleneck is the connection -
until the end table tag is received IE won't show anything. If you remove
the table tags for testing and just show plain text, you'll probably find
those users start seeing data almost immediately. Still, 110kB isn't much -
are you sure it's not 1.1MB? I've got a table displayed in my browser right
now with only a menu system, 6 columns (and mostly numbers in each of
those), and for around 100 rows that's 160kB. This page shows up on my home
PC in around 4-5 secs over a 512kbps ADSL line. I guess if your users have
dialup then it might take a few mins, but anything faster shouldn't.

> Is there anyway I can get around this?
> Somehow I can reduce/compress the file size while it gets sent over the
> network? (if thats even possible)
If you don't need all the data to be seen at once is to use paging - show
say 100 per page, with clickable page numbers to get other records.
If the data doesn't need to look nice you could look at pre tags and fixed
width spacing of lines.
If both ends support gzip compression then it's possible I guess.
Dan|||Hi Daniel,
The file is definitely 110+kB and not 1.1Mb, I wouldn't miss such a big
difference.
The asp page itself is pretty simple, it doesn't contain any menu(s) or
any images, it only contains two buttons and a table consisting of 8
columns and over 1000+ rows (each with some short text and small
numbers).
I still gotta find out how fast our interstate connections are, but
it's definitely not dial-up.
I'll keep trying ... but keep the suggestions coming!
Regards,
Shawn
Daniel Crichton wrote:
> Shawn wrote on 7 Apr 2005 23:56:13 -0700:
>
records
send
110+kB.
> Large tables are really noticeable when the bottleneck is the
connection -
> until the end table tag is received IE won't show anything. If you
remove
> the table tags for testing and just show plain text, you'll probably
find
> those users start seeing data almost immediately. Still, 110kB isn't
much -
> are you sure it's not 1.1MB? I've got a table displayed in my browser
right
> now with only a menu system, 6 columns (and mostly numbers in each of

> those), and for around 100 rows that's 160kB. This page shows up on
my home
> PC in around 4-5 secs over a 512kbps ADSL line. I guess if your users
have
> dialup then it might take a few mins, but anything faster shouldn't.
>
the
>
> If you don't need all the data to be seen at once is to use paging -
show
> say 100 per page, with clickable page numbers to get other records.
> If the data doesn't need to look nice you could look at pre tags and
fixed
> width spacing of lines.
> If both ends support gzip compression then it's possible I guess.
> Dan|||Shawn wrote on 8 Apr 2005 09:00:24 -0700:

> The file is definitely 110+kB and not 1.1Mb, I wouldn't miss such a big
> difference.
> The asp page itself is pretty simple, it doesn't contain any menu(s) or
> any images, it only contains two buttons and a table consisting of 8
> columns and over 1000+ rows (each with some short text and small
> numbers).
OK, I was just wondering as my HTML is quite large (I guess due to using
links and having a number of columns with around 50 characters each per
row). A basic table with 1000 rows, 8 columns, and 1 character per column is
around 90kB.

> I still gotta find out how fast our interstate connections are, but
> it's definitely not dial-up.
> I'll keep trying ... but keep the suggestions coming!
As your earlier posts state that the page comes up immediately for yourself
for the same data, then the bottleneck has to be something that's between
the other states and your server that your PC doesn't go through. Could be a
router or firewall somewhere in the path that's overloaded and is passing
data really slowly. Have you tried a traceroute from some of these locations
to your server to see what the response times are like from each router in
the path?
Dan|||some other things you might want to take a look at a well...
How often is the clients cache cleared?
Stylesheets...how big are the stylesheets being used?
Scripts .... are they set to "DEFER" correctly?
Message posted via http://www.webservertalk.com