April 21, 2011 at 2:53 pm
I have "running total" that I solved using..... cursors. Anyway, very slow, about 5 minutes.
I don't need all of the running totals, just the totals that cross a list of thresholds. ie user1 creates a random amount of units per day and I need to know the datetime that they crossed the 50, 100, 150,etc thresholds.
To help speed this up, I store the results to a secondary table, so I can start at the last known record. This cuts the run time down from 5 minutes to ~5 seconds, assuming no new records came in. That 5 seconds of overhead is because I need to validate that the running totals for the past date ranges haven't been modified. Because I already know the start and end dates for a given threshold, I can easily do a group by user sum(unit), which is very fast.
Here's my problem. This table that I update cannot be safely changed by more than one user at a time, otherwise I run the chance for a race condition. I tried wrapping the part of the code in a transaction, but that instantly made the run time go from 5 minutes to 9 minutes.
I don't need to be able to rollback changes as my code can safely be interrupted/canceled mid-processing.
Is there a way to lock the tables for the duration of the SP without using a transaction?
This proc will most likely be used by only a few people only a few times during the year and reducing the run time would be nice.
P.S. The target DB has 13k users and 470k records so far this year alone, and the cursor route scales linearly. I would happily take other suggestions for a running total that scales well.
Thanks 🙂
April 21, 2011 at 3:07 pm
There are, but it requires a little extra coding on your part. For things like this, I like to use sp_getapplock. Use TRY/CATCH to make sure you release the applock on failures.
Have the getapplock test at the beginning, and if you're already locked, have it return out of the proc with a message to the user stating it's already running, please try again later kind of thing.
You're basically going to have to create your own transaction. Flag in a table, sp_getapplock, single_user mode... you'll have to do something. I really don't recommend that last one though. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 21, 2011 at 3:27 pm
Nice info Craig 🙂
Instead of testing and exiting, is there a way to use a cursor to spin and "sleep" whiling waiting for the lock to release?
btw, I've done quite a bit of threading in C#, so mind my terminology 🙂
Thanks again , this will definitely help.
April 21, 2011 at 3:52 pm
Not a cursor, but a while loop. Look at WAITFOR DELAY while testing for the applock's release.
I always recommend when doing an app wait delay that you build in your own timeout to make sure the user isn't stuck longer then two-three minutes waiting.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply