November 11, 2009 at 1:52 pm
In our system, we have a stored procedure which checks that the current user has a valid session. This is a user defined process, but is core to our transactional process.
It's a simple stored procedure which passes in a numeric value and checks that a corresponding record exists.
This stored procedure is called from almost every other stored procedure in the database. In some cases where we have nested transactional processes, this stored procedure can be called 10's of times in a single transaction. It is the single biggest contributory factor to performance issues and incredibly high disk I/O.
How can I check from within the transaction if the stored procedure has already been called earlier in the transaction and bypass subsequent calls? If the nested stored procedures are not part of the same transaction, is there any way of bypassing future calls if the stored procedure has been executed within a certain period of time (i.e. 20-30 seconds) even this would improve performance exponentially. We are working on a longer term fix which will eliminate said stored procedure, but this may not happen for a while.
I hope this isn't too basic a question, and hope there's someone out there that can help.
Thanks!
November 11, 2009 at 1:59 pm
I don't think what you are trying to accomplish actually exists in SQL Server. You can't, in my knowledge, know what was executed within a certain transaction.
What you can do though, if post the "Simple" procedure here, and maybe we can try to improve it. It might look ridiculous, but maybe there's a scan on a table that is "quite" big, or if there is anything that we can do to actually improve it, we'll give it a try.
From another point of view, of course, your long term resolution looks like the way to go, launch it in the maybe client application, and keep the user "authorized" as long as he does not disconnect. Whatever the app is, it could work, and should be the way to go for long term usage.
For now, I propose you post your code, and table definition of the table you query from, we might be able to help.
Hope that helps,
Cheers,
J-F
November 11, 2009 at 2:24 pm
What is the root cause to have the validation implemented in almost every proc?
As far as I can see, there should be room for improvement in terms of the frequency the validation process is performed.
Basically, the concept could be along the following lines:
the validation process occurs only in "main" procedures (the ones that are actually called from client side). Any subroutines would be valid, since the calling proc is valid.
This could help to avoid that almost every proc does the verification.
When you return to one of the main procs from your subroutines you could check if the session is still valid to identify whether the whole process was valid or not. Since I don't know how you deal with a invalid session within one of the deeper nested levels it's hard to tell whether your current error handling philosophy would still work with this concept.
If you don't have any master routines maybe the overall concept of your database would allow to "group" some of the procs in a few "master" or "control" procedures and do the validation there. Hard to tell...
November 11, 2009 at 2:28 pm
I am fairly sure the SP is as optimised as it can be, however, I've posted it below: -
ALTER PROCEDURE CheckContext
@CxID int,
@CxToken varchar(20)
AS
SET NOCOUNT ON
DECLARE @CxLastTime datetime
SELECT @CxLastTime = EcxLastTime
FROM Context WITH(NOLOCK)
WHERE CxID = @CxID
AND CxToken = @CxToken
AND CxStatus = 1
The issue is that it's called too frequently (and unnecessarily) and that we don't (or can't) pass the status (i.e.fact that it's been called less than a few milliseconds ago) from one SP to the next (if it's already been called).
November 11, 2009 at 2:34 pm
Hi,
I understand what you say that it's called too frequently, but it may help if it runs faster...
Can you post the index definition of the table Context, and tell me how many rows are in there normally?
Cheers,
J-F
November 11, 2009 at 2:41 pm
lmu92 (11/11/2009)
What is the root cause to have the validation implemented in almost every proc?
It confirms that the 'user' has a valid session within our application. It appears to be legacy from early versions of our application, and simply validates that the user has a valid session created within our application.
The problem is that we call SP's individually and as part of a transaction. So SP1, SP2 and SP3 may be called independently with CheckContext, but SP1 and SP2 may also be nested within SP4. See the dilemma?
November 11, 2009 at 2:55 pm
Columns
NameData TypeMax Length (Bytes)Allow Nulls
CxIDint4
CxTokenvarchar(20)20
CxStartTimedatetime8
WrkIDvarchar(20)20
UseIDvarchar(20)20
OffCodesmallint2
CxLastTimedatetime8
AppIDvarchar(20)20
ProIDvarchar(5)5
UgrIDvarchar(3)3
PrgIDvarchar(3)3
WkgIDvarchar(3)3
OotTypesmallint2
BpgIDvarchar(3)3
BptIDvarchar(3)3
EcxStatustinyint1
UssSeqNoint4
TzoIDvarchar(3)3
TzoCurrentBiassmallint2
ProPriceGroupvarchar(3)3
VgrIDSalevarchar(3)3
OffAccountingOfgIDvarchar(3)3
Indexes
NameUniqueFill FactorFile GroupColumns
PK___1__12Yes90PRIMARYCxID
ContextStatusKey 90PRIMARYCxStatus/CxLastTime
November 11, 2009 at 3:03 pm
JakesterUK (11/11/2009)
I am fairly sure the SP is as optimised as it can be, however, I've posted it below: -ALTER PROCEDURE CheckContext
@CxID int,
@CxToken varchar(20)
AS
SET NOCOUNT ON
DECLARE @CxLastTime datetime
SELECT @CxLastTime = EcxLastTime
FROM Context WITH(NOLOCK)
WHERE CxID = @CxID
AND CxToken = @CxToken
AND CxStatus = 1
Let's try it with a covering index:
Try to create a nonclustered index on CxToken, CxStatus that includes EcxLastTime (included column).
I don't think you need to specify the CxID, since it's included as part of the clustered index.
Can you try this index, and see if the procedure is any faster (before and after).
Let's say it runs in 50 ms, if it now does in 25, well you got half the problem now.
Of course, you will have to review how those access are made, and maybe lower the amount of times you check it, but if it can run faster, you've still won for now!
If it does, or does not do a difference, can you post the execution plan, and tell me how many rows are in the table? Thanks,
Cheers,
J-F
November 11, 2009 at 3:13 pm
J-F Bergeron (11/11/2009)
If it does, or does not do a difference, can you post the execution plan, and tell me how many rows are in the table? Thanks,
OK, will give your main suggestion a try. There are not normally more than 1,000 records in this table, but it can go as high as 2-5,000 or thereabouts - it is a fairly dynamic table, and contains a mix of records which will be either several hours old, or cleared out every 15 minutes or so.
November 11, 2009 at 3:35 pm
... still trying the second path ... (reducing the number of calls)
Would the structure of your procedures (especially in terms of number and type of parameter per proc) allow the use of a master routine to control the 4 subs (to stay with your example...)?
CREATE PROC MasterRoutineSub1ToSub4 (@SubRoutineToCall CHAR(4),@CxID int, @CxToken varchar(20), @param1 varchar(10), @param2 varchar(10), @param3 varchar(10), @param4 varchar(10), @param5 varchar(10))
AS
/*
purpose: combine SP1, SP2, SP3, and SP4
*/
EXEC CheckContext @CxID,@CxToken, @RetVal OUTPUT
-- do the validation just once
IF @RetVal IS NULL
BEGIN
-- error_handling for failed check
END
IF @SubRoutineToCall = 'Sub1'
EXEC Sub1 param1, param2, param3, param4
IF @SubRoutineToCall = 'Sub2'
EXEC Sub2 param1, param2, param3,
IF @SubRoutineToCall = 'Sub3'
EXEC Sub3 param1
IF @SubRoutineToCall = 'Sub4'
EXEC Sub4 param1, param2, param3, param4, param5, param6
November 11, 2009 at 3:54 pm
J-F Bergeron (11/11/2009)
Let's try it with a covering index:Try to create a nonclustered index on CxToken, CxStatus that includes EcxLastTime (included column). ...
@ J-F: Just a side note: This thread is posted on the SQL2000 forum. The included column won't work, afaik. This would require to use EcxLastTime as a "real part" of the index.
November 11, 2009 at 4:21 pm
JakesterUK (11/11/2009)
I am fairly sure the SP is as optimised as it can be, however, I've posted it below: -ALTER PROCEDURE CheckContext
@CxID int,
@CxToken varchar(20)
AS
SET NOCOUNT ON
DECLARE @CxLastTime datetime
SELECT @CxLastTime = EcxLastTime
FROM Context WITH(NOLOCK)
WHERE CxID = @CxID
AND CxToken = @CxToken
AND CxStatus = 1
The issue is that it's called too frequently (and unnecessarily) and that we don't (or can't) pass the status (i.e.fact that it's been called less than a few milliseconds ago) from one SP to the next (if it's already been called).
Is something missing here? I see no return from the code to indicate success or fail.
Edit: Never mind, brain is on vacation day. It is Veterans Day after all here in the USA and I am off from work.
November 11, 2009 at 5:30 pm
lmu92 (11/11/2009)
J-F Bergeron (11/11/2009)
Let's try it with a covering index:Try to create a nonclustered index on CxToken, CxStatus that includes EcxLastTime (included column). ...
@ J-F: Just a side note: This thread is posted on the SQL2000 forum. The included column won't work, afaik. This would require to use EcxLastTime as a "real part" of the index.
Lutz, you're completely right, I did not notice it was a SQL 2000 query, thanks for the correction!
Cheers,
J-F
November 12, 2009 at 12:19 am
J-F Bergeron (11/11/2009)
lmu92 (11/11/2009)
J-F Bergeron (11/11/2009)
Let's try it with a covering index:Try to create a nonclustered index on CxToken, CxStatus that includes EcxLastTime (included column). ...
@ J-F: Just a side note: This thread is posted on the SQL2000 forum. The included column won't work, afaik. This would require to use EcxLastTime as a "real part" of the index.
Lutz, you're completely right, I did not notice it was a SQL 2000 query, thanks for the correction!
The majority of our clients are still on SQL 2000, but a handful are on SQL 2005, so I can try this on the SQL 2005 customers.
November 12, 2009 at 6:33 am
You can still try it on the SQL 2000, but as Lutz pointed out, you will have to add the "included" column as part of the index. So just add it as the last column of the index.
Cheers,
J-F
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply