|
 |
asp_discuss thread: Performance problem
Message #1 by williams@s... on Thu, 27 Sep 2001 10:47:58 -0400
|
|
Hi everyone,
I have intranet web site running on sql server, IIS server and using ASP
files. User has slow response time when running a report that would
generate over 1K records.
I am not too familiar with the interaction between the sql and IIS server.
I need someone help so that I can find out the root cause and solve the
problem.
I have sql statment in ASP file.
When I click "report" button on my form, it pops up a new window. On the
status bar , it shows "web site found, waiting for reply....".
What does it imply?
IIS server got the client request and located the asp file?
IIS has sent the sql statement to sql server but waiting for Recordset from
sql server?
Or it is waiting for IIS to send the sql statment instruction to sql
server?
I timed the above case:
It took 117sec for the "web site found, waiting for reply...." to change to
URL address on the status bar; then
it took 10sec to display the records on the new window. That is change from
URL address to "Done" on the status bar.
The no. of records displayed for this case is 1529 rows.
Can anyone suggest to me the bottleneck?
Also, I tried same sql statment on Query analyser, it took 41sec to display
the same 1529rows of records.
Furthermore, I tried run same sql statment(different values of paramenters)
on other user with their own sql server, IIS server. The database structure
on both sql server is basically the same except of course the data.
On query analyser, it took only 1sec to display 3822 rows of records; 4 sec
to display 20061 rows of records.
On IE, I got script timeout after 6 minutes in order to display the 3822
rows of records
Any suggestion?
I though about using recordset paging or stored procedure but.........just
don't understand why so much difference on performance between two users???
Network problem? Server problem?
Thanks in advance.
William
Message #2 by Kyle Burns <kburns@c...> on Thu, 27 Sep 2001 12:38:24 -0500
|
|
If the SQL server resides on the same machine as IIS, then the problem is
not a network issue. 41 seconds is FAR too long for a query that only
returns 1529 rows of data. Possibly the first thing that you should do is
share the SQL statement that is being run so we can give some suggestions.
I'd also recommend taking a look at things like your indexing. Another
possibility is to consider moving your SQL statement into a stored procedure
and seeing how that affects your performance.
Another thing that could affect the performance, especially if the DB setup
on the box that ran quicker is the same as the DB server that is slow, is
the configuration of the server. RAM is cheap and I always throw as much
RAM as possible at a database server.
Get back with us with additional information and we'll try to help you
figure this out.
=================================
Kyle M. Burns, MCSD
ECommerce Technology Manager
Centra Credit Union
kburns@c...
-----Original Message-----
From: williams@s... [mailto:williams@s...]
Sent: Thursday, September 27, 2001 10:02 AM
To: asp_discuss
Subject: [asp_discuss] Performance problem
Hi everyone,
I have intranet web site running on sql server, IIS server and using ASP
files. User has slow response time when running a report that would
generate over 1K records.
I am not too familiar with the interaction between the sql and IIS server.
I need someone help so that I can find out the root cause and solve the
problem.
I have sql statment in ASP file.
When I click "report" button on my form, it pops up a new window. On the
status bar , it shows "web site found, waiting for reply....".
What does it imply?
IIS server got the client request and located the asp file?
IIS has sent the sql statement to sql server but waiting for Recordset from
sql server?
Or it is waiting for IIS to send the sql statment instruction to sql
server?
I timed the above case:
It took 117sec for the "web site found, waiting for reply...." to change to
URL address on the status bar; then
it took 10sec to display the records on the new window. That is change from
URL address to "Done" on the status bar.
The no. of records displayed for this case is 1529 rows.
Can anyone suggest to me the bottleneck?
Also, I tried same sql statment on Query analyser, it took 41sec to display
the same 1529rows of records.
Furthermore, I tried run same sql statment(different values of paramenters)
on other user with their own sql server, IIS server. The database structure
on both sql server is basically the same except of course the data.
On query analyser, it took only 1sec to display 3822 rows of records; 4 sec
to display 20061 rows of records.
On IE, I got script timeout after 6 minutes in order to display the 3822
rows of records
Any suggestion?
I though about using recordset paging or stored procedure but.........just
don't understand why so much difference on performance between two users???
Network problem? Server problem?
Thanks in advance.
William
Message #3 by williams@s... on Thu, 27 Sep 2001 15:20:43 -0400
|
|
Kyle,
Thanks for your help.
Tough I don't quite understand the fact behind it, I fixed the problem as
below:
In my original progm I used include file to print the html table. The
include file coding is sthg like
<%
Function print(oRS)
:
while not objRS.eof
:
for each fld in oRS.fields
str=str & "<td>" & fld.value & "</td>"
next
:
Wend
:
print=str
End Function
%>
I got email from one of our member saying replacing str with response.write
to improve performance. Thanks him again in here.
So in my asp , I add a function instead of using include file. The code in
the function is the same as the include file except using response.write.
In the main asp file, instead of calling the include file to display
records after creating RS as below:
sql="select a, b, c, ....from tblA inner join tblB (inner join tblC
(inner join .......ON .......) where a=" & var1 " and b=" & var2
:
response.write print(objR1)
I call the new function
call print(objR1)
Now, for 10147 rows of records, it takes only about 13sec for the status
bar message to change from "web site found, waiting for reply...." to URL
address and 10sec to display all date . That is from URL address to "Done".
For 1431 rows of records, it took only total of 5 sec to diaplay all data.
So my problem seem to be solved. But I don't understand
1. what was doing behind the Internt Explorer duing the 13 seconds;
2. what was doing behind the Internt Explorer duing the 10 seconds;
if item 1 is for creating the Recordset, then for 10k records , it should
take about 3 minutes beause the calling of the new print function have
nothing to do with the recordset creation. Am I right?
Can you briefly tell me what has happened?
Thanks
william
Kyle Burns
<kburns@c... To: "asp_discuss" <asp_discuss@p...>
.org> cc:
Subject: [asp_discuss] RE: Performance problem
09/27/2001
01:38 PM
Please respond
to
"asp_discuss"
If the SQL server resides on the same machine as IIS, then the problem is
not a network issue. 41 seconds is FAR too long for a query that only
returns 1529 rows of data. Possibly the first thing that you should do is
share the SQL statement that is being run so we can give some suggestions.
I'd also recommend taking a look at things like your indexing. Another
possibility is to consider moving your SQL statement into a stored
procedure
and seeing how that affects your performance.
Another thing that could affect the performance, especially if the DB setup
on the box that ran quicker is the same as the DB server that is slow, is
the configuration of the server. RAM is cheap and I always throw as much
RAM as possible at a database server.
Get back with us with additional information and we'll try to help you
figure this out.
=================================
Kyle M. Burns, MCSD
ECommerce Technology Manager
Centra Credit Union
kburns@c...
-----Original Message-----
From: williams@s... [mailto:williams@s...]
Sent: Thursday, September 27, 2001 10:02 AM
To: asp_discuss
Subject: [asp_discuss] Performance problem
Hi everyone,
I have intranet web site running on sql server, IIS server and using ASP
files. User has slow response time when running a report that would
generate over 1K records.
I am not too familiar with the interaction between the sql and IIS server.
I need someone help so that I can find out the root cause and solve the
problem.
I have sql statment in ASP file.
When I click "report" button on my form, it pops up a new window. On the
status bar , it shows "web site found, waiting for reply....".
What does it imply?
IIS server got the client request and located the asp file?
IIS has sent the sql statement to sql server but waiting for Recordset from
sql server?
Or it is waiting for IIS to send the sql statment instruction to sql
server?
I timed the above case:
It took 117sec for the "web site found, waiting for reply...." to change to
URL address on the status bar; then
it took 10sec to display the records on the new window. That is change from
URL address to "Done" on the status bar.
The no. of records displayed for this case is 1529 rows.
Can anyone suggest to me the bottleneck?
Also, I tried same sql statment on Query analyser, it took 41sec to display
the same 1529rows of records.
Furthermore, I tried run same sql statment(different values of paramenters)
on other user with their own sql server, IIS server. The database structure
on both sql server is basically the same except of course the data.
On query analyser, it took only 1sec to display 3822 rows of records; 4 sec
to display 20061 rows of records.
On IE, I got script timeout after 6 minutes in order to display the 3822
rows of records
Any suggestion?
I though about using recordset paging or stored procedure but.........just
don't understand why so much difference on performance between two users???
Network problem? Server problem?
Thanks in advance.
William
|
|
|
View Cart
