June 24, 2019 at 12:41 pm
Hi all
Bit of a puzzler (for me at least).
I've got a fairly complex stored procedure that, when run for one set of parameters, returns data in around 30 seconds (first run) and then, on subsequent runs, returns data in around 10 seconds (for the same parameters).
Unfortunately, changing the parameters means the stored procedure takes forever (currently running for 10 minutes and still going).
Parameters are dates in the format yyyymmdd.
On the first run, the execute command is:-
exec usp_Load_MSDS101_Pregnancy_and_Booking '20190401','20190430'
This one takes seconds.
On the second run with different dates, the execute command is:-
exec usp_Load_MSDS101_Pregnancy_and_Booking '20190501','20190531'
I'm stumped as all I'm doing is changing the dates but I think it's creating a massively different plan (although I can't get the second query to finish so I can compare the two).
Has anyone got any rough ideas on where to start looking (apart from the query plan)?
I can post all the code to the stored procedure if anyone wants to take a look.
TIA
Richard
June 25, 2019 at 11:48 am
It sounds like bad parameter sniffing, but you're going to have to get the plans to be sure. Without the plans, you can look at the statistics for the column(s) in question and the values that you're passing to get an understanding of the row count estimates that will be generated. That's a great starting point.
Nothing says you can't use the estimated plans. Actual plans are just estimated plans plus runtime metrics. Try capturing those for the procedure and comparing them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 25, 2019 at 12:10 pm
Thanks Grant
I don't think it's parameter sniffing (I hope) as I moved the stored procedure into a standard piece of SQL and it was doing the same thing.
I was using a fairly chunky CTE but I've changed that to a couple of temp tables (with their own indexes) and that seems to have had a big impact (in a good way) as well.
I'm going to put my updated code back into the stored procedure and try it again.
I'll let you know what happens after that (may help someone else).
Richard
June 25, 2019 at 12:14 pm
When say standard SQL, did you hard code passing the filters or use local variables? If you hard coded the values, it's the same as parameter sniffing (it's using those values to get row counts & compile the plans).
Trick to remember on a CTE is that it's just a query that can be reused. Despite the name, it's not a table. It's an expression, a query. If you need temporary storage, then temp tables are usually the way to go (assuming you need statistics, filters, joins, otherwise, table variables have some advantages).
I think the answers are going to lie in the plans though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 25, 2019 at 12:14 pm
Agree with Grant, but also check (just to be sure) that the query isn't blocked! Query the sys.dm_exec_requests DMV and check the "blocking_sessions_id" column (should be 0).
select session_id
, blocking_session_id
, start_time
, status
, command
, wait_type
, wait_time
, wait_resource
, last_wait_type
, plan_handle
, qp.query_plan
from sys.dm_exec_requests request
outer apply sys.dm_exec_query_plan(plan_handle) qp
where session_id = ##-- put the SPID of your long-running query here
June 25, 2019 at 12:17 pm
Now it's gone odd.
I moved the "normal" code into a stored procedure and tried to alter the procedure and I've got the error:-
Msg 206, Level 16, State 2, Procedure usp_Load_MSDS101_Pregnancy_and_Booking, Line 366 [Batch Start Line 7]
Operand type clash: int is incompatible with date
The field the procedure is talking about (DischargeDateMatService) is a date but will have a NULL in it.
The temp table creation is here:-
CREATE TABLE #firstcutbirths
(
PregnancyID VARCHAR(36) NULL
,LPIDMother VARCHAR(20) NULL
,OrgIDComm VARCHAR(5) NULL
,AntenatalAppDate DATE NULL
,PregFirstConDate DATE NULL
,EDDAgreed DATE NULL
,OrgSiteIDBooking VARCHAR(9) NULL
,EDDMethodAgreed VARCHAR(2) NULL
,SourceRefMat VARCHAR(2) NULL
,OrgIDProvOrigin VARCHAR(6) NULL
,OrgIDRecv VARCHAR(5) NULL
,ReasonLateBooking VARCHAR(2) NULL
,PregFirstContactCareProfType VARCHAR(2) NULL
,LastMenstrualPeriodDate DATE NULL
,DisabilityIndMother VARCHAR(1) NULL
,LangCode VARCHAR(2) NULL
,MHPredictionDetectionIndMother VARCHAR(1) NULL
,ComplexSocialFactorsInd VARCHAR(1) NULL
,EmploymentStatusMother VARCHAR(2) NULL
,SupportStatusIndMother VARCHAR(1) NULL
,EmploymentStatusPartner VARCHAR(2) NULL
,PreviousCaesareanSections INT NULL
,PreviousLiveBirths INT NULL
,PreviousStillBirths INT NULL
,PreviousLossesLessThan24Weeks INT NULL
,FolicAcidSupplement VARCHAR(2) NULL
,DischargeDateMatService DATE NULL
,DischReason VARCHAR(2) NULL
,ReportingMonth DATE NULL
);
The table it's going into is here:-
USE [MDS_Maternity_V2]
GO
/****** Object: Table [dbo].[tbl_MSD101_Pregnancy_and_Booking_Details] Script Date: 25/06/2019 13:15:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_MSD101_Pregnancy_and_Booking_Details](
[PregnancyID] [VARCHAR](36) NULL,
[LPIDMother] [VARCHAR](20) NULL,
[OrgIDComm] [VARCHAR](5) NULL,
[AntenatalAppDate] [DATE] NULL,
[PregFirstConDate] [DATE] NULL,
[EDDAgreed] [DATE] NULL,
[OrgSiteIDBooking] [VARCHAR](9) NULL,
[EDDMethodAgreed] [VARCHAR](2) NULL,
[SourceRefMat] [VARCHAR](2) NULL,
[OrgIDProvOrigin] [VARCHAR](6) NULL,
[OrgIDRecv] [VARCHAR](5) NULL,
[ReasonLateBooking] [VARCHAR](2) NULL,
[PregFirstContactCareProfType] [VARCHAR](2) NULL,
[LastMenstrualPeriodDate] [DATE] NULL,
[DisabilityIndMother] [VARCHAR](1) NULL,
[LangCode] [VARCHAR](2) NULL,
[MHPredictionDetectionIndMother] [VARCHAR](1) NULL,
[ComplexSocialFactorsInd] [VARCHAR](1) NULL,
[EmploymentStatusMother] [VARCHAR](2) NULL,
[SupportStatusIndMother] [VARCHAR](1) NULL,
[EmploymentStatusPartner] [VARCHAR](2) NULL,
[PreviousCaesareanSections] [INT] NULL,
[PreviousLiveBirths] [INT] NULL,
[PreviousStillBirths] [INT] NULL,
[PreviousLossesLessThan24Weeks] [INT] NULL,
[FolicAcidSupplement] [VARCHAR](2) NULL,
[DischargeDateMatService] [DATE] NULL,
[DischReason] [VARCHAR](2) NULL,
[ReportingMonth] [DATE] NULL
) ON [PRIMARY]
GO
If I comment that field out in the insert and select, it all goes fine.
Anyone any ideas?
June 25, 2019 at 12:21 pm
May be a data issue. The stuff getting loaded into the temp table is not stored as a date maybe? Hard to know not seeing the data. Check the data using ISDATE and look for bad values.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 25, 2019 at 12:21 pm
Grant - I use local variables in all cases. The normal for a stored procedure that needs parameters for our team is to get the parameters (in whatever format) and copy themm to local variables (of the same format) and use the local variables in code. These are usually dates.
HanShi - I checked and the query wasn't being blocked, it waas just taking forever.
I've got that sorted, just need to sort out issue number two with data-type mismatches that aren't actually data-type mismatches (as far as I can see).
June 25, 2019 at 12:23 pm
Local variables won't be sniffed, so you avoid parameter sniffing... Unless you're getting recompiles. Then, a local variable also gets sniffed.
Ain't query tuning fun?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 25, 2019 at 12:24 pm
May be a data issue. The stuff getting loaded into the temp table is not stored as a date maybe? Hard to know not seeing the data. Check the data using ISDATE and look for bad values.
I can't see how the data is stored as anything else (although it is a NULL).
The data-type in the temp table is date, the data-type in receiving table is date.
The line in the SELECT statement is:-
DischargeDateMatService = NULL
I'm just confused now.
June 25, 2019 at 12:24 pm
Sounds like an implicit conversion somewhere along the way. Do you alter the contents of that field between the SELECT and the INSERT?
For debugging purpose: store the final result into a new table using the SELECT...INTO... and check the table definition and contents of that newly created table.
June 25, 2019 at 12:29 pm
No conversions anywhere. It starts as a NULL (it's a filler field for now that doesn't contain any data but will be needed in the future) and stays as a NULL all the way through the code.
I've done a SELECT .... INTO a table (not temp) and it's come through as a date field.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply