| Author |
Topic |
|
dbenoit64
Starting Member
36 Posts |
 Posted - 2003-06-02 : 14:05:38
|
| Im running 3 distributed sql querys on a linked server (sits in a different province) through an asp page. Both databases are Sql Serverthe select staments both kind of look like the following:select * from openquery(MON, 'SELECT vmv_lab_code, variable_name as variable, method_code as method, unit_code as unit from tvar2 where vmv_lab_code in (''02011'',''T132-02'') order by vmv_lab_code'); WHen setting up my linked server i set all timeouts to 0.Timeouts on the remote sever itself are set high enough (if that even makes a difference)WHen i run the first two queries in sql query analyzer, together they run in about 1:48. However, when running the queries in my asp page, when it gets to the second statement the page times out on the recordset.open.shows this error statement:Microsoft OLE DB Provider for SQL Server error '80040e31' Timeout expired /envirodat_moncton/root/main/en/generatePivot.asp, line 124 I really dont get it because in addition, my connection timeout and commandtimeout properties are set to 99999.Again, when i run these statments in query analyzer they work fine. however they timeout when running in my asp code.Any ideasthanks,dave |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-02 : 14:09:15
|
| 1:48 to run both queries! Is that one minute and 48 seconds? If so, then that's a long time. Not sure why you are getting the timeouts, but I would start by optimizing your query. Do you have an index on vmv_lab_code? If not, then add one.Tara |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-02 : 14:14:56
|
| How much data are you suppose to get back? I'm guessing it's a lot, and if it's not a lot, why the 1:48 execution time? If it's a lot, then Why, what are you doing, if it's not a lot, then you need to perform some perf tuning.Brett8-) |
|
|
|
dbenoit64
Starting Member
36 Posts |
Posted - 2003-06-02 : 14:41:13
|
| Im good with the 1:48. When i run the same query in query analyzer it works. it takes a minute and 48 secs but it still works. Its an extreemly huge couple of querys that go through a lot of work (I only included a piece of one of them to illustrate the openquery function that i run).Im not sure if there is an index on the remote database. but either way, if i get results back in the query analyzer shouldnt this work in my asp code? Ill look into indexing vmv_lab_code (have never added indexed before) in the mean timethanksdave |
|
|
|
wilso_s
Starting Member
10 Posts |
Posted - 2003-06-02 : 15:08:59
|
| it's been a very long time since I've written any ASP but isn't there a "QueryTimeout" property in either the connection or recordset objects? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-02 : 15:13:17
|
| Well if it's that complex, why not create a stored proc?Brett8-) |
|
|
|
dbenoit64
Starting Member
36 Posts |
Posted - 2003-06-03 : 08:03:59
|
| how would that work? what would i have to do? is it possible to run from asp code? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-03 : 12:16:52
|
| All of your T-SQL can be run in a stored procedure. And yes it can be called from ASP. Stored procedures are actually the better way to go due to performance and security.Tara |
|
|
|
spromtet
Starting Member
7 Posts |
Posted - 2003-06-05 : 15:10:23
|
| Setting the ASP page timeout (Server.ScriptTimeout) might help, but probably not -- There's also a session timeout value, but don't worry about that one.I'm not sure how much data is involved but, try checking the network throughput between the two linked servers. Because of a misconfigured router/hub/switch, I've seen network adapters switch to run 10 Mbps / half duplex instead of 100 Mbps/ full.Try copying a 10 megabyte file between the two servers. If it doesn't take a few seconds, there's a problem. |
|
|
|
|
SQL server timeout (distributed query linked svr)