[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

ASP.NET, DB2 Page Timeout while Data is being Fetched.

Our is a C#, ASP.NET application which gets plenty of data from DB2 and displays it to the user.
Since there is too much data, the DB2 developers tell us that it might take nearly 2 - 3 minutes for the data to be displayed on the screen.
By then, we gets this error message:
       "The operation has timed out"
I tried setting the Timeout property in C# code,
Server.ScriptTimeout = 60

Also I added
<appSettings>
     <add key="ConnectionString" value="Persist Security Info=False;User ID=hpro_web;password=;Initial Catalog=;Data Source=;Workstation ID=;Connect Timeout=30;"/>
 </appSettings>



0
dreamchaser23
Asked:
dreamchaser23
  • 6
  • 3
  • 2
  • +2
6 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi dreamchaser,

Any web page that beast a database to death for 2 to 3 minutes to render a page is worthless.  The page builder and the DBA need to sit down and find a better solution.

It may be that the current database design won't allow a significant improvement.  If so, consider static reports that are posted to another page.

I'll be glad to look at the database design and see if we can make things better.


Kent
0
 
Gautham JanardhanCommented:
if you are in that dire sitauton and dont want to change the data base design set time out to 0 i.e unconditinal wait but as Kent said i would also go for a new database design
0
 
dreamchaser23Author Commented:
Hi Kdo
Thanks a lot for your instant reply.
This occurs only when the user enters particular IDs (should not more than 3 of them).
for other IDs, I think the data gets displayed soon enough.
I just spoke with a DB2 guy after seeing your reply, it seems we dont have enough time to rectify the db design. It seems this is a long standing problem.
What do I have to do avoid this timeout for these few requests, Kent.
It'll be great if you can let me know, mate.
Thanks once again for your reply.
ps: The DB2 guy looked at me as if I knew everything about design :).
Special Thanks for that!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dreamchaser23Author Commented:
Hi Gauthampj
thanks for your comment, frend.
Where do you want me to set this unconditional timeout?
I would really appreciate if you throw some more light on this.
Yes frend, I am pretty much in a dire situation.!
0
 
Gautham JanardhanCommented:
2 option  are there
1) set timeout as 0 in connection string but as this will effect across the application .
2) better to set this in SQlcommand object .

can u post the code where u get the data

if u are using MSSQL and System.Data.SqlClient.SqlCommand FCommand;
FCommand.CommandTimeout = 0;
0
 
momi_sabagCommented:
Hi dreamchaser

the thing that i would suggest is - if you can't change the database, adjust the application
how about limiting the amound of data that is displayed on each screen ?
no user like to wait 2 minutes for a web page and then get a web page with a billion rows,
how about displaying only the first 100 rows (or less) and implement a pagining mechanism ?
this way, when you pass the querty to db2, you can add in the end
optimize for 100 rows, and db2 will make an effort to return the first 100 rows faster, and this might cause db2 to choose indexes it would have not choosen before
this of cousre won't always work, it depends on your sql statmenet.
can you post the sql here ?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hmmm....

My previous post never showed up, soooooo.....

If this performance problem only occurs on 2 or 3 IDs, it's likely that there are a large number of rows associated with these IDs and that there are (otherwise) reasonalbe indexing.  If a key index were missing a table scan would probably have a greater impact on all of these related queries.

This further suggests that a large join is in effect.  A lot of rows result from the join (either selected or by cartesian).  The final stages of the query (sorting, filtering, grouping) are working on a lot of rows that are not indexed.

Can you run an explain plan to see where the DBMS is spending most of its time?

Also, without changing the existing tables, your DBA may be able to create a materialized view/table that would much more effective at solving this query.


Kent
0
 
dreamchaser23Author Commented:
wow.. Kent.. Materialized view.. yep there are lots of rows assoc with these IDs
Will definitely ask my DBA regarding this. I am gonna sit with him and run the explain plan.
But this is there any temp fix, frend?
I tried CommandTimeout=600 in DB2 connection String:
Provider=IBMDADB2;UID=userid;PWD=password;DSN=db1;CommandTimeout=600
It worked for 1 complex ID.
But I found out another issue:
Once the page gets
  "The Operation has timed out" message,
I am not able to get to other aspx pages too. Which ever page, I try to go, I get the same
"The Operation has timed out" message. Which makes me wonder which one is timing out.
The DB2 connection or my ASPX page timeout.
Please let me know if you have more insights on this issue.
THanks again for your input, Kent.
very much appreciated.
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Sounds like a connection timeout to the middleware.

What are you running for middleware?

0
 
dreamchaser23Author Commented:
Thanks Kent.
That Db2 fella says that there is no middleware!
0
 
JimBrandleyCommented:
I think it is definately the http request that's timing out. Can you invoke the DB hit asychronously, or do you need to populate the page with the full set of data right away?

Jim
0
 
dreamchaser23Author Commented:
Hey Jim
Thanks for the reply mate.
How would you run them asynchronously?
Could you please elaborate on this.
I would really appreciate that.
Thanks once again for your reply, Jim.
0
 
JimBrandleyCommented:
See the other question.

Jim
0
 
dreamchaser23Author Commented:
Yep, I saw the comment, Jim.
Thanks.
I'll have a look at it and let you know what happened.
Regards
DreamChaser
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now