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 |
|
hanspret
Starting Member
2 Posts |
 Posted - 2011-02-04 : 08:32:33
|
| HiI have developed a stored proc that calculates depreciation for assets. The store proc calculate depreciation for one asset on one day and save that record to a table. The scenario is that some clients have 20000 assets and they only want to run depreciation once a month.With sql server profiler I can see that 30 days for 1 asset takes about 2 seconds, so if you do the math it will be as follows:20000 * 2 /60 / 60 = 11 hours plusI never had a scenario where a store proc needs to run this long. My Question is:What effect will this have on a server?Will it make the server slow and effect other employees to do their work?Can sql server handle a query that takes this long?thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-02-04 : 08:55:15
|
Your assumption is wrong. I've seen cursor-based code that takes 1 second per record on a 10 million record table.It would "never" finish. Well, at least for the next 115-116 days...Rewriting the code to a set-based solution made it run less than a second.If you want real help, please post real sample data and expected output. You can also supply your existing solution for review.Maybe we can make it work faster, maybe we tell you to use something else instead.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
hanspret
Starting Member
2 Posts |
Posted - 2011-02-04 : 11:08:25
|
| Thanks Pesosomething triggered in my brain when you mentioned set based solution.I have redeveloped the query and it only takes a second now.WOW from 11 hours to 1 second. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-02-04 : 13:09:49
|
That is great news!
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|
|
Long running queries