January 17, 2011 at 3:54 pm
Hi,
I have been asked to rewrite a query in a stored procedure that will accept multiple parameters from a web front end. The amount of parameters passed will be solely down to the user.
The way the query is setup at the moment, each parameters is defaulted to -1 and then will either stick with a value of -1 or, if passed into the, sp will be replaced by the key value selected by the user. The query is as follows:
SELECT
o.CustomerCode,
oa.OrderDate,
o.OrderID,
op.OrderProductID,
op.OrderProductQuantity * coalesce(@creditamount, OrderProductUnitPrice) AS TotalExGST,
op.OrderProductQuantity * coalesce(@credittaxamount, OrderProductUnitTax) AS TotalTax
FROM afd_order o
INNER JOIN afd_order_actual oa
on o.OrderID = oa.OrderID -- ORDER DATE
INNER JOIN afd_round_division_delivery_day_history rdddh--DELIVERY DAY
on rdddh.RoundDivisionDeliveryDayID = o.RoundDivisionDeliveryDayID
INNER JOIN afd_round_division rd
on rd.RoundDivisionID = rdddh.RoundDivisionID--ROUND DIVISION
INNER JOIN afd_round r
on r.RoundID = rd.RoundID --ROUND
INNER JOIN afd_depot d
on d.DepotID = rdddh.DepotID -- DepotID
INNER JOIN afd_order_product op
on op.OrderID = o.OrderID
INNER JOIN afd_customer_product cp
on cp.CustomerProductID = op.CustomerProductID --Customer Product
INNER JOIN afd_product_group pg
on pg.ProductGroupID = cp.ProductGroupID --ProductGroup
WHERE
(pg.ProductLineID = @ProductLine) OR (@ProductLine = -1)
AND
(pg.ProductGroupID = @ProductGroup) OR (@ProductGroup = -1)
AND
(cp.CustomerProductID = @CustomerProduct) OR (@CustomerProduct = -1)
AND
(d.OperationID = @OperationId) OR (@OperationId = -1)
AND
(d.DepotID = @DepotID) OR (@DepotID = -1)
AND
(rd.RoundID= @RoundID) OR (@RoundID = -1)
AND
(rd.RoundDivisionID = @RoundDivisionID) OR (@RoundDivisionID = -1)
AND
(o.RoundDivisionDeliveryDayID = @RoundDivisionDeliveryDayID) OR (@RoundDivisionDeliveryDayID = -1)
AND
(o.customercode = @CustomerCode) OR (@CustomerCode = -1)
AND
(op.CustomerProductID = @ProductID OR @ProductID = -1)
AND
(o.OrderID = @OrderID OR @OrderID = -1)
AND
(oa.OrderDate between CONVERT(CHAR, @startDate, 112) AND CONVERT(CHAR, @endDate, 112))
ORDER BY oa.OrderDate
I'm just wondering if this is the best way to code the where clause or is there a better way to build up the query based on the which parameters the user sets.
I was thinking maybe of Case statments in the WHERE clause but would that really make things better?
Thanks for any tips!
January 17, 2011 at 4:01 pm
Found this article which looks good to me:
http://www.sqlteam.com/article/implementing-a-dynamic-where-clause
Using COALESCE certainly looks a lot neater.....
January 17, 2011 at 10:49 pm
JayK (1/17/2011)
Found this article which looks good to me:http://www.sqlteam.com/article/implementing-a-dynamic-where-clause
Using COALESCE certainly looks a lot neater.....
You need to be a wee bit careful with COALESCE because it will change datatypes based on the "highest precedence type from the set of types in expression [sic]" whereas ISNULL will return the datatype of the checked expression. Lots of folks have run into performance problems when they use COALESCE because they're not aware of the implicit datatype change that can occur.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2011 at 11:05 pm
JayK (1/17/2011)
Using COALESCE certainly looks a lot neater.....
Neater maybe. Well performing? Definitely not.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
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
January 19, 2011 at 3:23 am
JayK (1/17/2011)
WHERE(pg.ProductLineID = @ProductLine) OR (@ProductLine = -1)
AND
(pg.ProductGroupID = @ProductGroup) OR (@ProductGroup = -1)
AND
(cp.CustomerProductID = @CustomerProduct) OR (@CustomerProduct = -1)
AND
what about..
WHERE
(pg.ProductLineID = case when @ProductLine > 0 then @ProductLine else pg.ProductLineID end)
AND
(pg.ProductGroupID = case when @ProductGroup > 0 then @ProductGroup else pg.ProductGroupID end )
AND
(cp.CustomerProductID = case when @CustomerProduct > 0 then @CustomerProduct else cp.CustomerProductID end )
AND
..........
My guess is this will not have any impact on performance and still looks neat 😀
Wkr,
Eddy
January 19, 2011 at 3:53 am
Sjeef (1/19/2011)
what about..WHERE
(pg.ProductLineID = case when @ProductLine > 0 then @ProductLine else pg.ProductLineID end)
AND
(pg.ProductGroupID = case when @ProductGroup > 0 then @ProductGroup else pg.ProductGroupID end )
AND
(cp.CustomerProductID = case when @CustomerProduct > 0 then @CustomerProduct else cp.CustomerProductID end )
AND
..........
My guess is this will not have any impact on performance and still looks neat 😀
Your guess would be wrong
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
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
January 19, 2011 at 4:11 am
GilaMonster (1/19/2011)Your guess would be wrong
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Just read the article,
So there are 2 basic options left
or you use the statement as i provided WITH the option Recompile
or you have to fall back on dynamic sql
the latter is find more work to maintain when logics have to be altered + i find it harder to read the code, but that may be just me..
Would the recompile cause a lot of cpu time to perform.?
Would test it myself but im @ home right now, not having my sqlengine near by 😎
Tnx, for the link Gila
Wkr,
Eddy
January 19, 2011 at 4:34 am
Sjeef (1/19/2011)
or you use the statement as i provided WITH the option Recompile
Providing you're on specific builds of 2008. The recompile feature was available in RTM, pulled in a CU because of incorrect results and then put back in later. I can't remember the builds offhand. think it might have been put back in SP2, but that needs checking.
Would the recompile cause a lot of cpu time to perform.?
Depends. Probably, but better than a badly performing query.
I prefer the dynamic SQL solution - optimal plans for each combinations of parameters that's used, so less recompiles and good plan reuse. It's no harder to maintain, code's all in the same place still, not in multiple places.
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
January 19, 2011 at 5:56 am
Dynamic SQL.
Despite being politically incorrect (insert long discussion on injection, readability, etc.), if your query runs for a long time, and has a ton of OR's in the predicate to handle missing parameters (implicitly via ISNULL or explicitly), you can almost universally make huge improvements with dynamic SQL.
This is even more true if you have a BI related environment where you have aggregate tables, and may be able to use a more higher level aggregate vs. a detail table because some parameter was omitted. One could argue that is the role of the calling application, but still...
Personally I prefer to build the dynamic SQL in the application not in the stored procedure. The tools are better, and you are "closer" to the logic so that you can me it more immune to UI changes (if it is a UI, and it usually is).
And when someone says "but it has to recompile every time".... compilations take milliseconds. If the query takes seconds or minutes, do you really care?
January 19, 2011 at 4:09 pm
Hi GilaMonster,
OK - i read your article, took the advise and changed my query replacing the COALESE and using Dynamic SQL instead.
My query, which returns 60830 rows went from an execution time of 11 mins 05 secs to 23 secs.
Still could work in the execution time but this is related more to indexing.
Thanks for the help!
January 20, 2011 at 3:22 am
JayK (1/19/2011)
My query, which returns 60830 rows went from an execution time of 11 mins 05 secs to 23 secs.
:w00t: That's excellent. Glad to hear.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply