Avatar of Jess31
Jess31
 asked on

Query / Criteria ?

See the attached Function.
tblGuides is about 5M rows. tblPOSupplierItemList 377.
When I run this function it takes < 1 second and returns 732 rows.
But when I add this criteria to the Where it takes more than 10 minutes and returns 75 rows. It does not matter where I add this criteria, inside the cte, outside or even in ssms (select * from dbo.POItemsBySupplierList(.5) <Where adding it here>). Is there a way to tell SQL to only apply this criteria to the result of 732?

Criteria:
((g.[New Net Pr] - ((sl.NetPrice ) + @ShippingCost)) / ((sl.NetPrice ) + @ShippingCost)) > 0

ALTER FUNCTION [dbo].[POItemsBySupplierList] 
(	

	@ShippingCost smallmoney = 0

)
RETURNS TABLE 
AS
RETURN 

With CTE As(

Select	g.ISBN, 
		g.Title,
		g.Publisher,
		g.[new net pr]								As	GuidePrice,
		dbo.QtyLeft_Guide(g.GuideNumber, g.ISBN)	As	QtyLeft,
		sl.NetPrice									As	Cost,
		sl.Discount,
		sl.LeadTimeDays,
		@ShippingCost								As ShippingCost
		

	From	tblGuides g
	Join	tblGuideHeader gh			On g.GuideNumber = gh.GuideNumber
	Join	tblPOSupplierItemList sl	On g.ISBN = sl.ISBN

	where	(DateDiff(day, GetDate(), gh.FinalShipDate) >= sl.LeadTimeDays)
		And
			(dbo.QtyLeft_Guide(g.GuideNumber, g.ISBN) > 0)
		--And
		--	((g.[New Net Pr] - ((sl.NetPrice ) + @ShippingCost)) / ((sl.NetPrice ) + @ShippingCost)) > 0
)
select  * from cte
--where ((GuidePrice - ((Cost ) + @ShippingCost)) / ((Cost ) + @ShippingCost)) > 0

Open in new window

Microsoft SQL Server

Avatar of undefined
Last Comment
Pawan Kumar

8/22/2022 - Mon
Éric Moreau

Jess31

ASKER
I don't know. How can I force it form doing parameter sniffing?
Éric Moreau

as explained in the link, declare a variable inside your function (declare @internalShippingCost smallmoney), set its value (set @internalShippingCost  = @ShippingCost ) and use the variable instead of the parameter.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jess31

ASKER
Sorry, I didn't explain. But I can't get the syntax correct. When I try adding a declare it won't compile.
ASKER CERTIFIED SOLUTION
Éric Moreau

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Pawan Kumar

Use statement level compilation or use dynamic string to over come parameter sniffing problem.