October 7, 2010 at 1:23 am
I’ve read some forum items regarding WITH RECOMPILE. But would like to know what it’s all about based on a specific scenario I have encountered.
I have a USP that returns a list of data based on the parameters.
ALTER PROCEDURE [dbo].[uspListExecutionTransaction](@StatusKey INT = NULL, @StrategyKey INT = NULL, @DateKey INT = NULL) AS BEGIN
SELECT * FROM EXECS.tblExecutionTransaction WHERE ExecutionKey IN (SELECT ExecutionKey FROM dbo.fnUserExecutionKeyList(@StatusKey,@StrategyKey,@DateKey))
END
NOTE: This procedure uses Optinal Parameters as well as a Table Function also with Optinal Parameters. This is to give users the option to search for data based on their individual needs.
Recently after adding indexes on new tables, including the one used above, the performance of the stored procedure seemed to take a dip at the end of the day. During the day the parameter values stays the same, but at the end the day the procedure is also used to with different set of parameters meaning the cached query plan is useless. It then takes almost 100 times longer for the query execute.
The problem is that we have 100’s of procedures where different combinations of parameters used during the day to produce data specific to those parameters. Different users will request different data by using different combinations of parameters.
I’m going to go out limb here and am probably going to sounds like a total noob … but it seems kind of silly to have cache query plans when the need in certain databases is to have more dynamic data returned by procedures.
I would like to know how this problem can be solved easily without having to add WITH RECOMPILE to each of the 100’s of stored procedures.
If more info is required from me, please let me know.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
October 7, 2010 at 1:43 am
It sounds to me that you are attempting to solve the 'catch-all' query problem.
IMO , the best way to handle those are by using Dynamic Sql.
Gail Shaw has documented that here
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Taking that a step further , where the cached plan is 'wrong' due to the distribution of data being different for different parameters, you could try this technique.
Feel free to vote up my connect item on the issue here:
https://connect.microsoft.com/SQLServer/feedback/details/610284/fix-the-psp-problem
October 7, 2010 at 2:08 am
Please post the definition of that function.
The reason that SQL caches plans is simple. Optimisation is a hard, difficult, CPU-intensive process. It's getting more and more CPU intensive as we improve the optimiser's capabilities. To avoid using a fair portion of the server's resources just to generate plans, SQL caches as many of them as possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2010 at 2:10 am
ALTER FUNCTION [dbo].[fnUserExecutionKeyList]
(
@StatusKey INT = NULL,
@Strategy INT = NULL,
@DateKey INT = NULL
)
RETURNS TABLE
AS
RETURN
(
SELECT ExecutionKey FROM EXECS.tblLineUp
INNER JOIN EXECS.tblLeg ON tblLeg.LegKey = tblLineUp.LegKey
INNER JOIN EXECS.tblTrade ON tblTrade.TradeKey = tblLeg.TradeKey
WHERE UserKey = dbo.fnGetUserID()
AND (@StatusKey IS NULL OR tblLineUp.StatusKey = @StatusKey)
AND (@Strategy IS NULL OR StrategyKey = @Strategy)
AND ExecutionDateKey = ISNULL(@DateKey,dbo.fnGetCurrentDateKey())
GROUP BY tblLineUp.ExecutionKey
)
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
October 7, 2010 at 2:27 am
Yup. Catch-all type query. See the first link Dave posted.
That will never perform well and recompiles will not help. SQL will generate a plan that is safe for all parameter possibilities and generally optimal for none of them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2010 at 2:41 am
Dave thanks for suggesting those two articles, Gail those articles you wrote helped me a lot understanding my problem.
Let for a minute assume that the problem could be a design flaw. A flaw which is in the process of being fixed as we speak.
The procedure was supposed to return a set of transactions based on what user, what strategy, what status and what date was used to create it in the first place … basically a batch or set.
I realize using optional parameters and table function was a bad idea from the start. So instead of doing that a single batchkey-like parameter will be used.
Here is the new procedure definition:
ALTER PROCEDURE [dbo].[uspListExecutionTransaction]
(@SessionKey UniqueIdentifier) AS BEGIN
SELECT * FROM EXECS.tblExecutionTransaction WHERE SessionKey = @SessionKey
END
Although every time this procedure will be called a different session key will be used – never the same one twice.[/u]
Will it be better to use WITH RECOMPILE here? Instead of using dynamic SQL?
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
October 7, 2010 at 2:47 am
It's not the procedure that's the problem, it's the function.
Edit: Just realised that you never gave me the definition of the main function (EXECS.tblExecutionTransaction). Please can you?
That there's a different session key each time is totally irrelevant to caching and compiling.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2010 at 2:54 am
That query looks like, I'm saying looks like because I really don't know anything about the distribution of SessionKey values, a good candidate for a cached plan. Meaning do not use RECOMPILE.
There is a good chance that the same execution plan will be optimal for all values of SessionKey. Again, saying this without knowing anything about what SessionKey actually is and it's distribution of values.
EDIT: Oh, is tblExectionTransaction a function?
EDIT2: No it isn't
October 7, 2010 at 2:54 am
USE [ACCDB]
GO
/****** Object: Table [EXECS].[tblExecutionTransaction] Script Date: 10/07/2010 10:53:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [EXECS].[tblExecutionTransaction](
[TransactionKey] [uniqueidentifier] NOT NULL,
[ExecutionKey] [uniqueidentifier] NOT NULL,
[SideKey] [int] NOT NULL,
[Volume] [bigint] NOT NULL,
[Price] [float] NOT NULL,
[TransactionID] [varchar](500) NOT NULL,
[TransactionDateKey] [int] NOT NULL,
[TransactionTimeKey] [int] NOT NULL,
CONSTRAINT [PK_tblExecutionTransaction] PRIMARY KEY CLUSTERED
(
[TransactionKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 50) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [EXECS].[tblExecutionTransaction] WITH CHECK ADD CONSTRAINT [FK_tblExecutionTransaction_tblExecution] FOREIGN KEY([ExecutionKey])
REFERENCES [EXECS].[tblExecution] ([ExecutionKey])
GO
ALTER TABLE [EXECS].[tblExecutionTransaction] CHECK CONSTRAINT [FK_tblExecutionTransaction_tblExecution]
You think of this as InvoiceHeader/InvoiceLine scenario ... where tblExecutionTransaction is the InvoiceLine table and tblExecution AND InvoiceHeader.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
October 7, 2010 at 2:56 am
ALTER PROCEDURE [dbo].[uspListExecutionTransaction]
(@SessionKey UniqueIdentifier) AS BEGIN
SELECT * FROM EXECS.tblExecutionTransaction WHERE SessionKey = @SessionKey
END
Assuming that EXECS.tblExecutionTransaction is a table with an index on sessionkey, then caching of plans is exactly what is required.
The plan will not change regardless of the value of @sessionKey.
October 7, 2010 at 2:57 am
I'm probably blind, but I don't see any SessionKey column in that table.
October 7, 2010 at 2:59 am
Nils Gustav Stråbø (10/7/2010)
That query looks like, I'm saying looks like because I really don't know anything about the distribution of SessionKey values, a good candidate for a cached plan. Meaning do not use RECOMPILE.There is a good chance that the same execution plan will be optimal for all values of SessionKey. Again, saying this without knowing anything about what SessionKey actually is and it's distribution of values.
EDIT: Oh, is tblExectionTransaction a function?
EDIT2: No it isn't
I get what your saying ... this SessionKey will be the same x-amount of records and it will be indexed as well. So by my understading is never mind the value of @SessionKey, the plan will be the same -> Look in the Index of SessionKey for @SessionKey
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
October 7, 2010 at 3:06 am
Dave Ballantyne (10/7/2010)
ALTER PROCEDURE [dbo].[uspListExecutionTransaction](@SessionKey UniqueIdentifier) AS BEGIN
SELECT * FROM EXECS.tblExecutionTransaction WHERE SessionKey = @SessionKey
END
Assuming that EXECS.tblExecutionTransaction is a table with an index on sessionkey, then caching of plans is exactly what is required.
The plan will not change regardless of the value of @sessionKey.
I figured that much ...
So basicly I assume what you are saying is to try and stay away from optional parameters and the "catch-all" saga. But if I have to do "catch-all" (for eg report based queries where user have different search options) I should try and use dynamic sql approach.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
October 7, 2010 at 3:11 am
Nils Gustav Stråbø (10/7/2010)
I'm probably blind, but I don't see any SessionKey column in that table.
Nils thats becuase its not in there yet. Thats part of my version2 design for this table. We have been designing a basic model based on the clients requirements, we are now looking at beefing up the model with better performance based on a trail run we had for almost a year.
But i do now understand the impact the model's design have on cache query plans and its importance to optimize it.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
October 7, 2010 at 3:22 am
roelofsleroux (10/7/2010)
So basicly I assume what you are saying is to try and stay away from optional parameters and the "catch-all" saga. But if I have to do "catch-all" (for eg report based queries where user have different search options) I should try and use dynamic sql approach.
That seems like a pretty spot on summation of this thread so far 🙂
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply