Slow SP (Query) 1

[hwkranger]

So, I have this slow query...

I try to keep the sps flexible, since we have so many (thousands) and business objects pass the same datasets around, so it's useful.

Anyways.. I've found that there's a point where the query optimizer becomes retarded, its' around the 5 or 6 nullable parameter marker. Regardless of the indexing (most of these parameters are part of a clustered index) there's a point where it goes from FAST to super slow. If I remove one of the nullable parameters (and matching case statement) it'll speed up. Doesn't matter which one, it just does. And it speeds up by a factor of about 10.

Can anyone explain this to me? I've been doing this method for a few years, and haven't seen a huge issue until this project where sometimes a dozen of nullable parameters are used to pull data.

I'd rather not use dynamic SQL to solve this issue.

P.S. Some of the sps with a dozen or more are not slow. It's only on larger (depth) tables.

ALTER Procedure [dbo].[uspAttributeReading_GetAll]
(
	@UserID int = null,
	@LanguageID int = null,
	@AttributeTemplateID int = null,
	--@AttributeID int = null,
	@TestModeID int = null,
	@EquipmentID int = null,
	--@AttributeReadingID int = null,
	@EquipmentTemplateCode int = null,
	@SiteVisitID int = null,
	@TaskTemplateID int = null,
	@ContractorID int = null
)
AS

-- REMOVED ATTRIBUTEID, ATTRIBUTEREADINGID for SPEED (not currently passed)

IF @LanguageID is null
	SELECT @LanguageID = UP_LanguageID from User_Preference Where UP_UserID = @UserID
IF @ContractorID is null
	SELECT @ContractorID = SV_ContractorID from Site_Visit where SV_SiteVisitID = @SiteVisitID


SELECT [AR_AttributeReadingID] AttributeReadingID
      ,[AR_AttributeID] AttributeID
      ,[AR_NumberValue] NumberValue
      ,[AR_TextValue] TextValue
      ,[AR_ReadingTime] ReadingTime
      --,[AR_IsDeleted]
--      ,[AR_CreatedBy]
--      ,[AR_CreatedOn]
      ,[AR_ContractorID] ContractorID
--      ,[AR_HistoryID]
	  ,[ATR_AttributeID] AttributeID
      ,[ATR_EquipmentID] EquipmentID
      ,[ATR_AttributeTemplateID] AttributeTemplateID
      ,[ATR_SiteVisitID] SiteVisitID
      ,[ATR_TestModeID] TestModeID
      --,[ATR_AttributeTemplateTypeID] AttributeTemplateTypeID
      ,[ATR_UnitOfMeasureID] UnitOfMeasureID
      ,[ATR_Sticky] Sticky

	  ,[ATT_AttributeTemplateID] AttributeTemplateID
      ,[ATT_Name] AttributeTemplateName 
      --,[ATT_Unit] Unit 
	  ,ETA_EquipmentTemplateCode EquipmentTemplateCode
	  ,UnitOfMeasureType.TYP_TypeName as UnitOfMeasureTypeName
      ,[UOM_Name] UnitofMeasureName
      ,[UOM_UnitOfMeasureTypeID] UnitOfMeasureTypeID
      ,[UOM_UnitOfMeasureTypeClassID] UnitOfMeasureTypeClassID
	  ,SV_StartDate StartDate
	  ,TM_TestModeLabel TestModeLabel

  FROM 
	[dbo].[Attribute_Reading] 
	INNER JOIN Attribute
			ON AR_AttributeID = ATR_AttributeID
	INNER JOIN Attribute_Template
			ON ATR_AttributeTemplateID = ATT_AttributeTemplateID
					AND
			   @LanguageID = ATT_LanguageID
	LEFT JOIN dbo.Unit_Of_Measure
			ON
				UOM_UnitOfMeasureID = [ATR_UnitOfMeasureID]
					AND
				UOM_LanguageID = ATT_LanguageID
	LEFT JOIN Type UnitOfMeasureType
			ON
				UOM_UnitOfMeasureID = UnitOfMeasureType.TYP_TypeID
					AND
				UOM_LanguageID = UnitOfMeasureType.TYP_LanguageID
	LEFT JOIN Equipment_Template_Attribute
			ON 
			   [ATR_AttributeTemplateID] = ETA_AttributeTemplateID
	LEFT JOIN Tasking_Attribute_Template
			ON
				TVT_AttributeTemplateID = ATR_AttributeTemplateID
					AND
				@TaskTemplateID = TVT_TaskTemplateID
					AND
				TVT_ContractorID = @ContractorID
	LEFT JOIN Test_Mode
			ON ATR_TestModeID = TM_TestModeID
	LEFT JOIN Site_Visit
			ON ATR_SiteVisitID = SV_SiteVisitID
WHERE
	AR_IsDeleted = 'false'
		AND
(
	Case when @TaskTemplateID is null then 1 else @TaskTemplateID end
		=
	Case when @TaskTemplateID is null then 1 else TVT_TaskTemplateID end
)
		AND
(
	Case when @AttributeTemplateID is null then 1 else @AttributeTemplateID end
		=
	Case when @AttributeTemplateID is null then 1 else [ATR_AttributeTemplateID] end
)
--		AND
--(
--	Case when @AttributeID is null then 1 else @AttributeID end
--		=
--	Case when @AttributeID is null then 1 else [ATR_AttributeID] end
--)
		AND
(
	Case when @TestModeID is null then 1 else @TestModeID end
		=
	Case when @TestModeID is null then 1 else [ATR_TestModeID] end
)

		AND
(
	Case when @EquipmentID is null then 1 else @EquipmentID end
		=
	Case when @EquipmentID is null then 1 else [ATR_EquipmentID] end
)
--		AND
--(
--	case when @AttributeReadingID is null then 1 else @AttributeReadingID end
--		=
--	case when @AttributeReadingID is null then 1 else [AR_AttributeReadingID] end
--)
		AND
(
	Case when @EquipmentTemplateCode is null then 1 else @EquipmentTemplateCode end
		=
	Case when @EquipmentTemplateCode is null then 1 else ETA_EquipmentTemplateCode end
)
		AND
(
	Case when @SiteVisitID is null then 1 else @SiteVisitID end
		=
	Case when @SiteVisitID is null then 1 else [ATR_SiteVisitID] end
)

 

[gmmastros]

My best guess... and it's just that... is that you are running in to a [google]parameter sniffing[/google] issue.

Try adding the WITH RECOMPLILE option to the stored procedure to see if this fixes the issue.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

If it is the NULLs that are giving you issues, then why don't you set the defaults to 1 like your CASE statements are doing? Have you tried that? Any change in performance?

--------------------------------------------------
"...and did we give up when the Germans bombed Pearl Harbor? NO!"

"Don't stop him. He's roll'n."
--------------------------------------------------

 

[hwkranger]

George,

I wouldn't have thought of that... I knew about the option from the execution side (and in subqueries), but adding it to the troublesome sps fixed it instantly!

God Bless your guess!!

Joe,

I didn't try that, but if i did -- it looks to be about the same (in my eyes) as to issue as I was having. It was the issue with running against the indexes properly, I could see in the execution plan that that it was going crazy... I was just stumped as to why.