November 12, 2007 at 3:43 pm
Hi - I'm having some performance issues with an application I've recently inherited. I was able to increase the performance of the query by adding some joins instead of subselects and looking at some indexes etc. So now the procedure executes in 6 secs or so with a smaller date range - say 15 days. However the users can still not run the report from the web app - as it always times out. With a six second query, this timeout should not occur.
Also - i'll execute the query over and over in query analyzer, and get a response of six seconds or so and then randomly get 40 secs or more again - which seems like it's compiling. i can't figure out why it would randomly compile (if that is what it's doing). I've read that the cached procs can get pushed out, but this is really quick - how do i verify that the procedure is cached and then check to see if it's pushed out by other processing?
This report is accessing a history table that is constantly being written to by the application, but i've written the query with NOLOCK, so i was hoping that the writers would not block this reader and vice versa. I'm attaching the proc body. it's long because it has two optional parms that i have to check for the queries are all the same, except for dates (last line).
Any input would be fantastic!!
CREATE PROCEDURE dbo.mmds_rpt_test_results_detail
@StartDate varchar(10),
@EndDate varchar(10),
@CustomerID int,
@ReturnError varchar(200) OUTPUT
AS
DECLARE @ErrorNum int
BEGIN
SET NOCOUNT ON
declare @startDt as datetime
declare @endDt as datetime
if @StartDate <> ''
begin
set @startDt = convert(datetime,@StartDate)
if @EndDate <> ''
begin
set @endDt = convert(datetime,@EndDate)
SELECT T_HISTORY.USER_NM,
U.LNAME_NM + ', ' + U.FNAME_NM AS DISPLAY_NM,
EC.EVENT_DESC AS ACTION,
P.LNAME + ', ' + P.FNAME AS PATIENT,
B.INDEXED_ITEM_ID AS PATIENT_ID,
Q.DATA_DESC AS TEST_RESULT,
T_HISTORY.ORIG_DT
FROM dbo.T_HISTORY WITH(NOLOCK)
JOIN dbo.T_INDEX_DOCUMENTS B WITH(NOLOCK) ON B.WORK_ITEM_ID = T_HISTORY.ITEM_ID
AND B.INDEX_TYPE_ID = 1
JOIN dbo.MMDS_CHECKLIST_DATA C WITH(NOLOCK) ON C.WORK_ITEM_ID = T_HISTORY.ITEM_ID
JOIN DBO.MMDS_PATIENT P WITH(NOLOCK) ON P.PATIENT_ID = B.INDEXED_ITEM_ID
JOIN DBO.T_LOOKUP Q ON Q.DATA_CD = C.TEST_RESULTS
AND Q.CUSTOMER_ID = @CustomerID
AND Q.LOOKUP_TABLE_CD = 'TEST_RESULTS'
AND Q.SITE_ID = 0
JOIN dbo.T_USER U ON U.USER_NM = T_HISTORY.USER_NM
AND U.CUSTOMER_ID = @CustomerID
JOIN dbo.T_HISTORY_EVENT_CODES EC ON EC.EVENT_CD = T_HISTORY.EVENT_CD
WHERE T_HISTORY.CUSTOMER_ID=@CustomerID
AND T_HISTORY.STATE_CD = 'RESULTS'
AND T_HISTORY.USER_NM <> 'RULE'
AND T_HISTORY.EVENT_CD IN ('WI_CPLT','WI_RET_TO_QUE')
AND T_HISTORY.ORIG_DT >= @startDt
AND T_HISTORY.ORIG_DT <= @endDt + 1
end
else
begin
SELECT T_HISTORY.USER_NM,
U.LNAME_NM + ', ' + U.FNAME_NM AS DISPLAY_NM,
EC.EVENT_DESC AS ACTION,
P.LNAME + ', ' + P.FNAME AS PATIENT,
B.INDEXED_ITEM_ID AS PATIENT_ID,
Q.DATA_DESC AS TEST_RESULT,
T_HISTORY.ORIG_DT
FROM dbo.T_HISTORY WITH(NOLOCK)
JOIN dbo.T_INDEX_DOCUMENTS B WITH(NOLOCK) ON B.WORK_ITEM_ID = T_HISTORY.ITEM_ID
AND B.INDEX_TYPE_ID = 1
JOIN dbo.MMDS_CHECKLIST_DATA C WITH(NOLOCK) ON C.WORK_ITEM_ID = T_HISTORY.ITEM_ID
JOIN DBO.MMDS_PATIENT P WITH(NOLOCK) ON P.PATIENT_ID = B.INDEXED_ITEM_ID
JOIN DBO.T_LOOKUP Q ON Q.DATA_CD = C.TEST_RESULTS
AND Q.CUSTOMER_ID = @CustomerID
AND Q.LOOKUP_TABLE_CD = 'TEST_RESULTS'
AND Q.SITE_ID = 0
JOIN dbo.T_USER U ON U.USER_NM = T_HISTORY.USER_NM
AND U.CUSTOMER_ID = @CustomerID
JOIN dbo.T_HISTORY_EVENT_CODES EC ON EC.EVENT_CD = T_HISTORY.EVENT_CD
WHERE T_HISTORY.CUSTOMER_ID=@CustomerID
AND T_HISTORY.STATE_CD = 'RESULTS'
AND T_HISTORY.USER_NM <> 'RULE'
AND T_HISTORY.EVENT_CD IN ('WI_CPLT','WI_RET_TO_QUE')
AND T_HISTORY.ORIG_DT >= @startDt
end
end
else
begin
if @EndDate <> ''
begin
set @endDt = convert(datetime,@EndDate)
SELECT T_HISTORY.USER_NM,
U.LNAME_NM + ', ' + U.FNAME_NM AS DISPLAY_NM,
EC.EVENT_DESC AS ACTION,
P.LNAME + ', ' + P.FNAME AS PATIENT,
B.INDEXED_ITEM_ID AS PATIENT_ID,
Q.DATA_DESC AS TEST_RESULT,
T_HISTORY.ORIG_DT
FROM dbo.T_HISTORY WITH(NOLOCK)
JOIN dbo.T_INDEX_DOCUMENTS B WITH(NOLOCK) ON B.WORK_ITEM_ID = T_HISTORY.ITEM_ID
AND B.INDEX_TYPE_ID = 1
JOIN dbo.MMDS_CHECKLIST_DATA C WITH(NOLOCK) ON C.WORK_ITEM_ID = T_HISTORY.ITEM_ID
JOIN DBO.MMDS_PATIENT P WITH(NOLOCK) ON P.PATIENT_ID = B.INDEXED_ITEM_ID
JOIN DBO.T_LOOKUP Q ON Q.DATA_CD = C.TEST_RESULTS
AND Q.CUSTOMER_ID = @CustomerID
AND Q.LOOKUP_TABLE_CD = 'TEST_RESULTS'
AND Q.SITE_ID = 0
JOIN dbo.T_USER U ON U.USER_NM = T_HISTORY.USER_NM
AND U.CUSTOMER_ID = @CustomerID
JOIN dbo.T_HISTORY_EVENT_CODES EC ON EC.EVENT_CD = T_HISTORY.EVENT_CD
WHERE T_HISTORY.CUSTOMER_ID=@CustomerID
AND T_HISTORY.STATE_CD = 'RESULTS'
AND T_HISTORY.USER_NM <> 'RULE'
AND T_HISTORY.EVENT_CD IN ('WI_CPLT','WI_RET_TO_QUE')
AND T_HISTORY.ORIG_DT <= @endDt + 1
end
else
begin
SELECT T_HISTORY.USER_NM,
U.LNAME_NM + ', ' + U.FNAME_NM AS DISPLAY_NM,
EC.EVENT_DESC AS ACTION,
P.LNAME + ', ' + P.FNAME AS PATIENT,
B.INDEXED_ITEM_ID AS PATIENT_ID,
Q.DATA_DESC AS TEST_RESULT,
T_HISTORY.ORIG_DT
FROM dbo.T_HISTORY WITH(NOLOCK)
JOIN dbo.T_INDEX_DOCUMENTS B WITH(NOLOCK) ON B.WORK_ITEM_ID = T_HISTORY.ITEM_ID
AND B.INDEX_TYPE_ID = 1
JOIN dbo.MMDS_CHECKLIST_DATA C WITH(NOLOCK) ON C.WORK_ITEM_ID = T_HISTORY.ITEM_ID
JOIN DBO.MMDS_PATIENT P WITH(NOLOCK) ON P.PATIENT_ID = B.INDEXED_ITEM_ID
JOIN DBO.T_LOOKUP Q ON Q.DATA_CD = C.TEST_RESULTS
AND Q.CUSTOMER_ID = @CustomerID
AND Q.LOOKUP_TABLE_CD = 'TEST_RESULTS'
AND Q.SITE_ID = 0
JOIN dbo.T_USER U ON U.USER_NM = T_HISTORY.USER_NM
AND U.CUSTOMER_ID = @CustomerID
JOIN dbo.T_HISTORY_EVENT_CODES EC ON EC.EVENT_CD = T_HISTORY.EVENT_CD
WHERE T_HISTORY.CUSTOMER_ID=@CustomerID
AND T_HISTORY.STATE_CD = 'RESULTS'
AND T_HISTORY.USER_NM <> 'RULE'
AND T_HISTORY.EVENT_CD IN ('WI_CPLT','WI_RET_TO_QUE')
end
end
SELECT @ErrorNum = @@ERROR
IF @ErrorNum <> 0
BEGIN
RAISERROR('Error in mmds_rpt_test_results_detail', 16,@ErrorNum)
RETURN
END
RETURN 0
END
November 12, 2007 at 6:14 pm
This report is accessing a history table that is constantly being written to by the application,
That would be part of the inconsistency... procs recompile when certain amounts of data change... and it doesn't take much.
Dunno about your timeout problem...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2007 at 6:46 pm
It doesn't take much huh? I guess that is why it's recompiling .... the recompile seems to take quite a bit of time too... in the end i'm not sure what i can do to get around this other than set the timeout of the db call to be pretty high. Is there anyway you can set something so it recompiles less often?
I'll have to look around a bit more for details on how much data changing causes a recompile.
Any other input/thoughts/suggestions would be great!
November 12, 2007 at 11:31 pm
Of course it's going to take some time... your sproc has four relatively large SELECTS and they ALL must be recompiled even if only one is going to be used...
The other thing you may be running into is a thing that happens with variables (I'll be damned if I can remember what they call it)... basically, you need to assign each input variable to an explicitly named variable... can greatly improve the speed if the problem is occuring (what the heck do they call that problem? Variable something or other...)
Believe it or not, another thing you can try is adding the WITH RECOMPILE option to a stored procedure... sometimes it helps even on something like this.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2007 at 11:36 pm
Now I remember... it's called "Parameter Sniffing" and, apparently, it can crush otherwise effecient code. Lots of good info on the Web about it... Google it...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2007 at 1:28 am
The other problem with that is that when the proc compiles, all of the selects compile, based on the values of the parameters for that call. That's even if the select can't be reached based on those parameters. That behaviour can get you some really erratic performance. Sometimes the query with one set of params runs fine and with a second runs slow, sometimes the second set of params are the fast ones and the first the slow.
I normally recommend that a proc of that form gets split up, so that you call other procs based on the value of the params
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
November 13, 2007 at 7:45 am
We are trying to avoid opening the code, but it looks like we may have to.
I will check into the parameter sniffing - previously the query was dynamic - i could flip it back to that quickly to see if that actually increases the compile time.
Thank you for all input - if i have any great improvements i'll reply back.
🙂
donna
November 13, 2007 at 8:44 am
Or, you might care to FORCE the recompile each time it's run, so that it will optimize for the branch that will actually run....
That's adding a WITH RECOMPILE to the SP definition as I recall.
----------------------------------------------------------------------------------
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?
November 13, 2007 at 9:20 am
Looks to me like all 4 SELECTs are exactly the same, with just a different filter on T_HISTORY.ORIG_DT based on the input parameters.
Do you really need IF .. THEN blocks with 4 separate branches of execution ?
Couldn't you just set the local date variables to very low/high values if they are passed as blank, and have 1 SELECT ?
eg:
-- Convert blank start date to a date earlier than earliest possible date in
-- your data
Select @startDt = Cast(Case when @StartDate = '' Then '01 Jan 1900' Else @startDt End As DateTime)
Why try to preempt the optimizer with IF statements ? Give it both dates in 1 SELECT, let it optimize based on data distribution and indexes.
November 14, 2007 at 3:00 pm
You can wrap the following around the SQL, and it will show you the parse & compile time for each execution. It will also show you the IO - whether logical(in cache) or physical(from disk). If you have cache pressure it could also be that the data is getting flushed. I think this will at least help point you to where the slowdown is happening - sp recompile or data flushed from cache.
set statistics io on
set statistics time on
set statistics profile on
go
--QUERY GOES HERE
go
set statistics io off
set statistics time off
set statistics profile off
go
jg
November 15, 2007 at 7:04 am
having 4 seperate selects, any one of which may run based upon parameters passed will almost always give you problems unless the query plan for each query is identical - then generally you don't need 4 queries!
I usually split procs like this into 5 procs, the top one sorts the parameters and calls the relevent sub proc - this usually ( but not always ) resolves parameter sniffing issues too.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 28, 2007 at 8:59 pm
My two cents on compacting the procedure so it does not recompile.
1) Be careful with unknowingly converting a field in the WHERE clause that is a data element in a table. Do this at first only once (with some as a default to keep from having an error)
SET @startDt = CONVERT(DATETIME,@StartDate)
SET @endDt = DATEADD(DAY,1,CONVERT(DATETIME,@EndDate))
Your @endDt + 1 in the WHERE may be converting the T_HISTORY.ORIG_DT field to something other than a date (look in the query plan to see).
2) Then, replace all four parts with just one using the following as an example.
--set up the test table
CREATE TABLE dbo.T_HISTORY
(
KEY_ID INT IDENTITY(1,1) NOT NULL
, ORIG_DT DATETIME NOT NULL
)
GO
INSERT INTO
dbo.T_HISTORY
(ORIG_DT
)
VALUES
('09/01/2007'
)
GO
INSERT INTO
dbo.T_HISTORY
(ORIG_DT
)
VALUES
('10/01/2007'
)
GO
INSERT INTO
dbo.T_HISTORY
(ORIG_DT
)
VALUES
('11/01/2007'
)
GO
INSERT INTO
dbo.T_HISTORY
(ORIG_DT
)
VALUES
('12/01/2007'
)
GO
SELECT * FROM dbo.T_HISTORY
--now test ...
DECLARE@StartDate VARCHAR(10)
, @EndDate VARCHAR(10)
--THE FOUR CASES CAN BE TESTED WITH THESE VALUES
SET @StartDate = '10/01/2007' --''--'10/01/2007'
SET @EndDate = '10/30/2007' --''--'10/31/2007'--'10/30/2007'
DECLARE@startDt DATETIME
, @endDt as DATETIME
--ALWAYS CONVERT TO THE TABLE.COLUMN DATA TYPE
SET @startDt = CONVERT(DATETIME,@StartDate)
SET @endDt = DATEADD(DAY,1,CONVERT(DATETIME,@EndDate))
--SOME DIAGNOTIC PRINT
SELECT @startDt AS startDt
, @endDt AS endDt
--WHAT DO WE FIND ...
SELECTTH.KEY_ID
, TH.ORIG_DT
FROMdbo.T_HISTORY TH
WHERETH.ORIG_DT >=
CASE WHEN @StartDate <> '' THEN @startDt
ELSE TH.ORIG_DT END
AND
TH.ORIG_DT <=
CASE WHEN @EndDate <> '' THEN @endDt
ELSE TH.ORIG_DT END
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply