Brett Kaiser (x002548) Blog

Not Just a Number - Brett Kaiser

Dynamic Predicates?

by Brett Kaiser (x002548) in  

Always seem to asked...bugged the hell out of me..The answer always seemd to be dynamic SQL since anything in the predicate to handle the absence of a criteria would cause a scan of the index...stage 2 predicate (non sargable) and all..

 

But what about this?  Just add as many Left Joins as you need.....if properly indexed, it all index seek on the main/large table that's being searched.

 

All Comments appreciated

 

EDIT:  After more research, I don't think ( I hate when that happens) That you can have a full AND Condition, but all we can hope for is a ranking of how many not null conditions are met.  So I've added the ranking code.

 

 

[code]

USE Northwind
GO

SET NOCOUNT ON


CREATE INDEX Orders_RequiredDate ON Orders(RequiredDate)

DECLARE @OrderDate datetime, @RequiredDate datetime, @ShippedDate datetime, @SQL varchar(8000)

SELECT @OrderDate = '1996/08/14', @RequiredDate = NULL, @ShippedDate = NULL

SELECT @OrderDate    AS OrderDate    INTO #OrderDateTemp
SELECT @RequiredDate AS RequiredDate INTO #RequiredDateTemp
SELECT @ShippedDate  AS ShippedDate  INTO #ShippedDateTemp

    SELECT
   CASE WHEN ot.OrderId IS NULL THEN 0 ELSE 1 END
 + CASE WHEN rt.OrderId IS NULL THEN 0 ELSE 1 END
 + CASE WHEN st.OrderId IS NULL THEN 0 ELSE 1 END
 AS Rank
 , o.*
      FROM Orders o
 LEFT JOIN (     SELECT OrderId FROM #OrderDateTemp ot   
  INNER JOIN Orders o1  ON o1.OrderDate = ot.OrderDate) AS ot
 ON o.OrderId = ot.OrderId
 LEFT JOIN
   (     SELECT OrderId FROM #RequiredDateTemp rt
  INNER JOIN Orders o1  ON o1.RequiredDate = rt.RequiredDate) AS rt
 ON o.OrderId = rt.OrderId
 LEFT JOIN
   (     SELECT OrderId FROM #ShippedDateTemp st 
  INNER JOIN Orders o1  ON o1.ShippedDate = st.ShippedDate) AS st
 ON o.OrderId = rt.OrderId
     WHERE ot.OrderId IS NOT NULL
        OR rt.OrderId IS NOT NULL
        OR st.OrderId IS NOT NULL

ORDER BY RANK DESC

DROP INDEX Orders.Orders_RequiredDate
DROP TABLE #OrderDateTemp
DROP TABLE #RequiredDateTemp
DROP TABLE #ShippedDateTemp

SET NOCOUNT OFF
GO

[/code] 

Legacy Comments
DavidM
2004-05-05		 re: Dynamic Predicates?
I've never seen a variable loaded into a table before. Nice trick.

The best non dynamic sql I've seen for this is with an existance check for each column..

SELECT *
FROM Orders o
WHERE EXISTS(SELECT 1 from Orders OD where @OrderDate IS NOT NULL AND
O.OrderID = OD.OrderID AND OD.OrderDate = @OrderDate )
or EXISTS (SELECT 1 from Orders RD where @RequiredDate IS NOT NULL AND
O.OrderID = rD.OrderID AND RD.RequiredDate = @RequiredDate )
or EXISTS (SELECT 1 from Orders SD where @ShippedDate IS NOT NULL AND
O.OrderID = sD.OrderID AND sD.ShippedDate = @ShippedDate )


But in a vast majority of scenarios, dynamic sql will out perform simply because it is doing a customized query.

Brett
2004-05-06		 re: Dynamic Predicates?
David,

Thanks...I'll give it a test...

But isn't that a an OR not an AND condition?

I'll run the test...
Brett
2004-05-06		 re: Dynamic Predicates?
Yup...it's an OR...But it's all Index Seek..

I added the extra date here

DECLARE @OrderDate datetime, @RequiredDate datetime, @ShippedDate datetime, @SQL varchar(8000)

SELECT @OrderDate = '1996/08/14', @RequiredDate = '1996-08-28', @ShippedDate = NULL

CREATE INDEX Orders_RequiredDate ON Orders(RequiredDate)

SELECT *
FROM Orders o
WHERE EXISTS( SELECT * FROM Orders OD
WHERE @OrderDate IS NOT NULL
AND O.OrderID = OD.OrderID
AND OD.OrderDate = @OrderDate )
OR EXISTS ( SELECT * FROM Orders RD
WHERE @RequiredDate IS NOT NULL
AND O.OrderID = rD.OrderID
AND RD.RequiredDate = @RequiredDate )
OR EXISTS ( SELECT * FROM Orders SD
WHERE @ShippedDate IS NOT NULL
AND O.OrderID = sD.OrderID
AND sD.ShippedDate = @ShippedDate )
GO

DROP INDEX Orders.Orders_RequiredDate
GO


Brett
2004-05-06		 My OR Solution
I got my OR Like

SELECT *
FROM Orders o
INNER JOIN #OrderDateTemp ot ON o.OrderDate = ot.OrderDate
UNION ALL
SELECT *
FROM Orders o
INNER JOIN #RequiredDateTemp rt ON o.RequiredDate = rt.RequiredDate
UNION ALL
SELECT *
FROM Orders o
INNER JOIN #ShippedDateTemp st ON o.ShippedDate = st.ShippedDate

Brett
2004-05-06		 Damn
So much for QA...mine is an OR as well...

But it can give you a "Rank" by determining how many fields matched...

Which I guess is the best you could hope for...

No?

Here's how to Add Rank such that the "best" hits come out on top (relatively speaking 8-))

SELECT
CASE WHEN ot.OrderId IS NULL THEN 0 ELSE 1 END
+ CASE WHEN rt.OrderId IS NULL THEN 0 ELSE 1 END
+ CASE WHEN st.OrderId IS NULL THEN 0 ELSE 1 END
AS Rank
, o.*
FROM Orders o
LEFT JOIN ( SELECT OrderId FROM #OrderDateTemp ot
INNER JOIN Orders o1 ON o1.OrderDate = ot.OrderDate) AS ot
ON o.OrderId = ot.OrderId
LEFT JOIN
( SELECT OrderId FROM #RequiredDateTemp rt
INNER JOIN Orders o1 ON o1.RequiredDate = rt.RequiredDate) AS rt
ON o.OrderId = rt.OrderId
LEFT JOIN
( SELECT OrderId FROM #ShippedDateTemp st
INNER JOIN Orders o1 ON o1.ShippedDate = st.ShippedDate) AS st
ON o.OrderId = rt.OrderId
WHERE ot.OrderId IS NOT NULL
OR rt.OrderId IS NOT NULL
OR st.OrderId IS NOT NULL
ORDER BY RANK DESC