SQL Query Timeout - ASP.NET 2.0

[memcom]

I am using Visual Basic 2005 to populate a gridview from an SQL 2000 database. My query usually takes around 2 minutes to return the data, but every time I try to load the data into the gridview I get a timeout error. I have found a Microsoft Knowledge Base article (

http://support.microsoft.com/?kbid=842044)

that shows how to change the timeout period in the connection string, but it seems that it does not work in ASP.NET 2.0. I am getting the error "Unrecognized attribute 'sqlCommandTimeout'"

How can I set the timeout on this page so that I don't get an error.

Thanks,
Michael

 

[jbenson001]

That article's example if you are using sql session state, which does not apply to your situation I assume. In your case you would have to change the timeout property on the SQL server. I suggest you don't return so much data, the users won't be able to use much anyway. Limit your result set. If it is just that the query is complex and take a long time, you have to adjust the timeout on the sql server.

Jim

 

[memcom]

I am not returning very much data. The query is very complex and that is something that I cannot change and keep the results in realtime. Where would I change the timeout on the SQL server? The only place that I found a timeout setting is in Enterprise Manager -> server group -> server properties -> connection tab -> remote server connections -> query time-out. I tried changing this, but it had no effect on the problem at all.

 

[dashen]

Can you not rewrite the query to be more efficient? Show your query, maybe we can make it more efficient.

Did you use a Stored procedure to force the query to run off of the server?

 

[jbenson001]

Change that timeout property to 0. Also, as dashen, the query probably can be written better. There is no reason and end user should be waiting 2 minutes for data.

 

[memcom]

I tried setting the timeout property to 0 and that made no difference at all. The website is used for internal processes only, so I don't have a problem with the user having to wait for 2 minutes. Below is the query that I am using. Running in query analyzer it took 111 seconds to run and returned 71 rows. If I eliminate the derived table, it still takes over 30 seconds to run. 2 of the tables in the query have over 500,000 rows and the other table (inv_loc) has over 2,500,000 rows. Thank you for looking at this!

select
inv_mast.item_id
,inv_mast.item_desc
,inv_loc.inv_min
,inv_loc.inv_max
,free_stock = inv_loc.qty_on_hand - inv_loc.qty_allocated
,qty_needed_for_min = inv_loc.inv_min - (inv_loc.qty_on_hand - inv_loc.qty_allocated - inv_loc.qty_backordered)
,qty_needed_for_max = inv_loc.inv_max - (inv_loc.qty_on_hand - inv_loc.qty_allocated - inv_loc.qty_backordered)
,extra_stock.extra_stock
,location_needing_stock = inv_loc.location_id
,location_with_extra_stock
,inventory_supplier.cost
from
inv_mast
inner join inv_loc on inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
inner join inventory_supplier on inv_mast.inv_mast_uid = inventory_supplier.inv_mast_uid and inv_loc.primary_supplier_id = inventory_supplier.supplier_id
inner join (
select
inv_mast.inv_mast_uid
,inv_mast.item_id
,extra_stock = inv_loc.qty_on_hand - inv_loc.qty_allocated - inv_loc.qty_backordered - inv_loc.inv_max
,location_with_extra_stock = location_id
from
inv_mast
inner join inv_loc on inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
where
inv_loc.qty_on_hand - inv_loc.qty_allocated - inv_loc.qty_backordered - inv_loc.inv_max > 0)
extra_stock on inv_mast.inv_mast_uid = extra_stock.inv_mast_uid
where
inv_loc.qty_on_hand - inv_loc.qty_allocated - inv_loc.qty_backordered < inv_loc.inv_min