Timeout on my request!

[Smith]

Hello,
In my asp.net application i need to retreive data from my MS SQL db. The
query takes more then 3 minutes.
I get a timeout after about 30 seconds. I have change the sqlCommandTimeout
default value of the <sessionState to 12000 in web.config. I still get the
same timeout problem.

Does anyone know how i solve the timeout problem.

Thanks
Smith

 

[Mark Rae [MVP]]

Hello,
In my asp.net application i need to retreive data from my MS SQL db. The
query takes more then 3 minutes.
I get a timeout after about 30 seconds. I have change the
sqlCommandTimeout default value of the <sessionState to 12000 in
web.config. I still get the same timeout problem.

Does anyone know how i solve the timeout problem.

How are you specifying the setting? Note that the sqlCommandTimeout
attribute is a TimeSpan attribute:
http://msdn2.microsoft.com/en-us/library/h6bb9cz9(vs.80).aspx

 

[Smith]

How are you specifying the setting? Note that the sqlCommandTimeout
attribute is a TimeSpan attribute:
http://msdn2.microsoft.com/en-us/library/h6bb9cz9(vs.80).aspx

Below how i do it:

<sessionState

sqlCommandTimeout="12000"

cookieless="true"

timeout="20"

/>

Thanks

/S

 

[Mark Rae [MVP]]

sqlCommandTimeout="12000"

Hmm - that's a *HUGE* amount of time to wait for results from a database
query... 12,000 seconds = 200 minutes = over 3 hours...

I'm not aware that the sqlCommandTimeout has a maximum value but, just in
case, have you tried setting it to something a bit more sensible e.g. 300 (=
5 minutes)...?

 

[Smith]

Hmm - that's a *HUGE* amount of time to wait for results from a database
query... 12,000 seconds = 200 minutes = over 3 hours...

I'm not aware that the sqlCommandTimeout has a maximum value but, just in
case, have you tried setting it to something a bit more sensible e.g. 300
(= 5 minutes)...?

Hi Mark,
I know this is unusual and thank for pointing out the 200 mins.

My query takes approx 7 minutes to run. This is not a usual web application
and hopefully we will not have more the 5 simultaneous users.

But it should be possible to read about the max value on msdn online. I
wonder why this was not documented.

Cheers
/S

 

[Mark Rae [MVP]]

My query takes approx 7 minutes to run.

In which case, try setting the value to 600.

But it should be possible to read about the max value on msdn online. I
wonder why this was not documented.

As I said, it may not actually exist...

7 minutes is, frankly, a ridiculous amount of time for a query to process,
especially in a web scenario.

If I were you, I'd concentrate on that rather than sqlCommandTimeout...

Does every table referenced in the query have a primary key?

Does every field referenced in either the joins or the WHERE clause have an
index?

Are you using anything like cursors?

Etc

 

[Smith]

In which case, try setting the value to 600.


As I said, it may not actually exist...

What do you mean?

7 minutes is, frankly, a ridiculous amount of time for a query to process,
especially in a web scenario.

We are computing schintific data and this take the same time when we run the
querry directly on the server.
This is an attempt to move from our FTP server. We are streaming data
directly to the user instead having to send an FTP link.

If I were you, I'd concentrate on that rather than sqlCommandTimeout...

There is nothing wrong with the query/db server

Does every table referenced in the query have a primary key?

Does every field referenced in either the joins or the WHERE clause have
an index?

Are you using anything like cursors?

Etc

S

 

[Mark Rae [MVP]]

What do you mean?

As I said, there may not actually be a maximum value for this...

There is nothing wrong with the query/db server

Other than it takes 7 minutes to process...

 

[Smith]

As I said, there may not actually be a maximum value for this...

This does not make sence in computing. 32 bit computing(what we are doing)
has quite obvious limits.

Other than it takes 7 minutes to process...

Hi Mark,
It all depends on the kind of data you work with. We are looking at scintifc
data here.
Some of our queries can span over several hours.

Cheers
S

 

[Mark Rae [MVP]]

It all depends on the kind of data you work with. We are looking at
scientifc data here.

Data is data is data, at the end of the day...

Some of our queries can span over several hours.

In which case, I would be considering a different architecture, as the web
simply isn't designed for this kind of delay between Request and Response...

Have you thought about maybe some sort of messaging system where queries are
queued up waiting to be processed, and then the results emailed to the user
maybe as a PDF...?

 

[Chuck Norris]

First, check the estimated execution plan of your query and, if it's
possible, try to optimize it.
If you have already done that,
why don't you schedule your query to run in background each night (or
each hour) and save the results in a DB table?
At this point in your web page do a simple 'select' statement to show
the data...surely it'll be faster.

 

[Smith]

een Request and Response...

Have you thought about maybe some sort of messaging system where queries
are queued up waiting to be processed, and then the results emailed to the
user maybe as a PDF...?

I understand your concerns Mark. And you are right. I think i have to take
this up with management. Some of this does not really make sens in the web
world.
Users select a combination of data and we calculate what the asked for on
the DB server, then return the data.
Users are prompted with a pop up window with an estimation of the time the
computation will take. As i said earlier, we are working in the scientific
community. Users previously got an FTP link to the data and dowloaded. Now,
progress mean, the data can be streamed to them. The data is highly
compressable, so dowload is not a problem.

But i understand we need to review the architecture. And again, computation
has been gratly optimised on the DB server.

Cheers
Smith

 

[bruce barker]

you are changing the timeout on a session fetch, not a sqlquery, so it
has no impact. the SqlCommand you are using has a CommandTimeout
property you can set. you will also need to change the page timeout so
it doesn't timeout also.

three minutes is too long, the browser may give up, the user will. you
should start a background thread to do the query and have the browser
poll for completion.

-- bruce (sqlwork.com)

 

[Smith]

you are changing the timeout on a session fetch, not a sqlquery, so it has
no impact. the SqlCommand you are using has a CommandTimeout property you
can set. you will also need to change the page timeout so it doesn't
timeout also.

three minutes is too long, the browser may give up, the user will. you
should start a background thread to do the query and have the browser poll
for completion.

-- bruce (sqlwork.com)

Hi Bruce,
could you please give an example of your CommandTimeout ?

Thanks
Smith