x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Dynamic Predicates?

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] 

Print | posted on Wednesday, May 05, 2004 3:27 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# 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.

5/5/2004 7:50 PM | DavidM
Gravatar

# 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...
5/6/2004 10:37 AM | Brett
Gravatar

# 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


5/6/2004 10:43 AM | Brett
Gravatar

# 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

5/6/2004 10:44 AM | Brett
Gravatar

# 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



5/6/2004 11:09 AM | Brett
Gravatar

# Database Design based on existing data

6/13/2005 3:19 PM | x002548's Blog
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET