August 23, 2007 at 7:48 am
This is a very weird problem. I have a windows app (C#, .NET 2.0) which, as part of an order creation process, needs to retrieve an order number before saving the order. I created a stored proc, GetNextOrderNumber to do just that. Most of the time the app works just fine, but, about once a month, the call to execute the stored proc times out (and almost immediately I get a support call). The fix is very simple but I still don't understand what's going on: I open a query window and type: exec GetNextOrderNumber. It takes from 2 to 3 *minutes* to execute and return the next order number. The weird thing is that afterwards the execution time goes down to less than a second (as it should be). The stored proc behaves like it's somehow "stuck" and needs a little "push", after which everything is fine. I've checked for blocking locks while the stored proc is executing (in "stuck" mode) and found none. The only thing that's "special" about this stored proc is that it uses the NEWID function. Can anyone tell me why it's behaving this way and what can be done to avoid that behavior? (Increasing the timeout property for the connection is not an option).
The code couldn't be simpler:
The table ORDER_NUM_SEQUENCE has two columns: GUID (char(36)) and NextOrderNum (int identity). The procedure inserts a new row tagged with a unique identifier and then returns the value in NextOrderNum corresponding to that identifier. It seems like a complicated way to get the next order number, but remember that the application gets the number before saving the order and at any given time there are hundreds of concurrent users. So, without further ado (no pun intended) here's the code:
SET
@GUID = NEWID()
INSERT INTO ORDER_NUM_SEQUENCE(GUID) VALUES(@GUID)
SELECT NextOrderNum FROM ORDER_NUM_SEQUENCE WHERE GUID = @GUID
August 23, 2007 at 8:07 am
Is the database trying to grow during that time?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 23, 2007 at 10:22 am
Haven't checked for that, I will next time it happens. But to be honest I doubt that's what causes the problem. Why? Because if it was, the problem would go away after a while and the users would be able to resume creating orders, and that has never happened. The first time it took me about 20 minutes to figure it out and during that time all users (including me testing) got timeouts consistently. But after I identified the problem I have never had to wait for more than 2-3 minutes for the stored proc to execute. It's very unlikely that every time the problem was reported the database was just about finished growing. But I will check for that next time anyway, at this point I will try any suggestion becuase I don't have a clue as to what's going on.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply