Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
smithygreg
Starting Member
37 Posts |
 Posted - 2010-02-18 : 12:06:34
|
| I have a Service written in C# that takes data files, imports them into my database and processes them using my SP "ProcessData"...This past week, it started to take a very very long time to run this procedure..Even for the smallest files.When I run the procedure from Management Studio it takes much much less time.To give you an example..In Mgmt Studio, the SP will take 1 minute, but it is Timing out at 40 minutes when it's called from my service.The Service calls the procedure with a simple SqlCommand.ExecuteNonQuery() command.I don't believe that I have made any changes to the DB structure or any of the queries recently. Can you think of any settings or configuration issues that would cause this issue to happen?I wrote a small test WinForms application that just ran the procedure and it had the same problem...It took over 30 minutes before I killed it. I ran the same procedure from Management Studio and it took less than 5 minutes. Thanks in advance!-Greg |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-18 : 12:25:53
|
| Tables not being reindexed / statistics not being updated regularly?Either that, or the houskeeping is not causing the SProc to be forced to recompile (which used to be the case under SQL 2000, but I thought it was fixed under SQL 2005)Having said that, if you run it "manually" does it then run quickly for a while after that? If not I don't think updating statistics / recompile is the problemEXE sp_recompile 'ProcessData'will force your Sproc "ProcessData" to recompile the next time it runs - maybe try that and see if it still times out when called from the Service ?? |
 |
|
|
smithygreg
Starting Member
37 Posts |
Posted - 2010-02-18 : 12:33:56
|
| Hi Kristen..Thanks, but unfortunately it doesn't matter what order I run the procedures in...They always run significantly faster from Management Studio than from my .net application that calls them. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-18 : 12:37:18
|
| That tends to suggest some sort of network issue then.Is the Sproc returning a lot of data? that would be fast on the machine itself, but dependent on network speed for anything remote.Or is the SProc doing a SELECT * and perhaps a large TEXT/IMAGE column (or data) has been added to the table since it was first created?Other than that I think I'm out of ideas ... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-18 : 12:39:59
|
| Use SQL Profiler to see the actual command / parameter being sent to launch the SProc - maybe something is going on that you are not expecting? |
|
|
|
smithygreg
Starting Member
37 Posts |
Posted - 2010-02-18 : 12:49:32
|
| The procedure is working with a lot of data, but nothing is being returned by any selects..It's all UPDATE statements. The service is running on the local machine where the DB is. Mgmt Studio is on another PC on the network...I would think if it's a network issue that it would run slower on the Network PC. I'll look at the profiler and see if anything pops out at me. Thanks |
|
|
|
|
|
|
|
|
Same Query, Different execution time..