| Author |
Topic |
|
ryanoc
Starting Member
25 Posts |
 Posted - 2006-07-03 : 14:31:30
|
| I have a test db that is an exact copy of a live db and one of the insert procs on the keeps timing out on the test db from my .net code. When I change the conn string to the live its fine. I tested another insert proc and it works, so the user has permission. Im at a loss and need to get this working. Any help would be great, thanks in advnace!Ryan |
|
|
ryanoc
Starting Member
25 Posts |
Posted - 2006-07-03 : 16:13:01
|
| I found by running inline sql in my page that some tables on the test db timeout and some have no problem with timming out. Since I created my test database from a live restore shouldnt it be the same?Ryan |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-07-03 : 16:18:31
|
| Are the execution plans the same on both databases? Is the hardware identical on both servers? Did you run UPDATE STATISTICS after the restore?Tara Kizeraka tduggan |
|
|
|
ryanoc
Starting Member
25 Posts |
Posted - 2006-07-03 : 16:50:22
|
| I didnt do either and Im not sure how to do either. :)What does the "UPDATE STATISTICS" do?Ryan |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-07-03 : 16:55:28
|
| You can read up on UPDATE STATISTICS in SQL Server Books Online. The first thing to check though is the execution plans. How big is your database? How many rows are impacted by the query? What is the query? Are your indexes fragmented? How much memory is there on both servers? How many CPUs on both servers? Are other users connected to your test database when you run the query? If so, are you being blocked by those other users?There are loads and loads of things to check and run. There is not a single answer that we can give for a question like this due to the number of things that could be the problem, so we have to ask lots of questions and have you run lots of things.Tara Kizeraka tduggan |
|
|
|
ryanoc
Starting Member
25 Posts |
Posted - 2006-07-03 : 17:06:13
|
| It is a large database with a hundred or so tables and a hundred or so procs. The query is just an update query for one field in one table with one column (very simple). There are qudit triggers there that I dont deal with at all so I hope thats not the problem. Both databases are on the same server also. There are not many users (less that 20), and its not used much. Thanks for all your help.Ryan |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-07-03 : 21:13:24
|
| is there an update trigger on the tables that 'time-out'? the one you call with your inline sql (is this dynamic sql?)if it's dsql, you need to verify if the strings created connect to the correct server/databaseHTH--------------------keeping it simple... |
|
|
|
ryanoc
Starting Member
25 Posts |
Posted - 2006-07-05 : 08:27:39
|
| Instead of using the stored procedure I tried to execute a single non dynamic update statement using c# code and the same one using query analyzer. The query analyzer one worked fine everytime with 2 different update statements but the code timed out on one. Both were just updating one field in one record. Both tables have triggers going on to, but Im using the same admin password in the code as Im using to login with query analyzer. The only other difference is that some stored procedures are encrypted and some are not But Im not sure all the procs and tables the triggers handle. I figured that wouldnt matter since im using an admin password. Does anyone know what is going on? ThanksRyan |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-07-05 : 11:42:41
|
| It is hard to know what is going on without seeing the code. Using an admin userid and password doesn't make a difference as it'll still go through the same code.Tara Kizeraka tduggan |
|
|
|
ryanoc
Starting Member
25 Posts |
Posted - 2006-07-05 : 13:01:10
|
| The first update will always timeout, but the second one wont. Also, I created a database locally and restored it using the same file as the one im having problems with and I now have no timeout issues. ahhh! SqlConnection connRDK = new SqlConnection("Server=my_server;Database=ad_xx_beta;Persist Security Info=False;user id=sa;Password=xxx"); String sql; sql = "UPDATE VHSLSFIN SET AmtPriceVehicle = '116907' WHERE SlsId = 'V01001878'"; //sql = "UPDATE COEMP SET NameNick = 'Ryan C' WHERE empid = '163'"; try { SqlCommand commRDK = new SqlCommand(sql, connRDK); connRDK.Open(); commRDK.ExecuteNonQuery(); } catch (Exception ex) { string x = (ex.Message); } finally { //done } |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-07-05 : 13:08:03
|
| How long does the first query take if you run it in Query Analyzer rather than through your code? What does the execution plan show for query 1?Tara Kizeraka tduggan |
|
|
|
ryanoc
Starting Member
25 Posts |
Posted - 2006-07-05 : 16:40:18
|
| Its very fast. The execution plan is huge, theres all sorts of things going on, but the same restore file works locally, weird. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-07-05 : 16:44:20
|
| I don't understand why the execution plan would be huge for this query:UPDATE VHSLSFIN SET AmtPriceVehicle = '116907' WHERE SlsId = 'V01001878'You need to compare the execution plans on the database that times out and the one that doesn't. If the execution plan is the same, then we'll have to look elsewhere.Tara Kizeraka tduggan |
|
|
|
ryanoc
Starting Member
25 Posts |
Posted - 2006-07-05 : 16:52:57
|
| Theres a bunch of triggers for auditing and other things that Im not fully aware of. |
|
|
|
ryanoc
Starting Member
25 Posts |
Posted - 2006-07-05 : 17:04:30
|
| I did a "Display Estimated Execution Plan" using sql2005 and it was much smaller of a diagram. Both were the same for the different dbs also. One other thing I noticed was that I ran the query for the first time in a few hours and it took over 20 seconds to execute. After that it ran instantly everytime. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-07-05 : 17:33:18
|
| Of course it runs faster the second time around, the data is now cached.Tara Kizeraka tduggan |
|
|
|
ryanoc
Starting Member
25 Posts |
Posted - 2006-07-06 : 07:41:52
|
| Sorry, Im not as experienced with sql as I am with vb.net. Is this an issue that is to strange to figure out with the info I posted here?Ryan |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-07-06 : 11:26:56
|
| You haven't answered a lot of the questions that I have asked. I asked how big is the database. Your response was that it was large and had hundreds of objects. That doesn't answer the question though. When I asked if your query was being blocked by other users, your response was that there are other users on the system. That doesn't answer the question either. There are quite a few question and answers like this. We can't help without finding out more information. There is no specific one answer. If you don't know how to find an answer for a question, then ask.Tara Kizeraka tduggan |
|
|
|
|
Proc Error on test DB