SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query times inconsistent


Query times inconsistent

Author
Message
Donna-514920
Donna-514920
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 7
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85739 Visits: 41091
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Donna-514920
Donna-514920
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 7
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!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85739 Visits: 41091
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85739 Visits: 41091
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87069 Visits: 45267
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Donna-514920
Donna-514920
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 7
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.

Smile
donna
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12203 Visits: 18574
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?
PW-201837
PW-201837
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1909 Visits: 1228
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.
gmby
gmby
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2364 Visits: 3792
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search