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
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(
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
could it be related to parameter sniffing? have a look at http://emoreau.com/Entries/Blogs/2009/01/SQL-Server--Parameter-sniffing--Stored-Procedures-Execution-plan.aspx
ASKER
I don't know. How can I force it form doing parameter sniffing?
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Use statement level compilation or use dynamic string to over come parameter sniffing problem.