Site Sections
Posts
83
Comments
600
Trackbacks
40
<< The Interview | Home | Calculate the most effecient Storage size based on the catalog >>
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] 

posted on Wednesday, May 05, 2004 3:27 PM Print
Comments
# re: Dynamic Predicates?
DavidM
5/5/2004 7:50 PM
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.

# re: Dynamic Predicates?
Brett
5/6/2004 10:37 AM
David,

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

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

I'll run the test...
# re: Dynamic Predicates?
Brett
5/6/2004 10:43 AM
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


# My OR Solution
Brett
5/6/2004 10:44 AM
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

# Damn
Brett
5/6/2004 11:09 AM
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



# Database Design based on existing data
x002548's Blog
6/13/2005 3:19 PM
Comments have been closed on this topic.
Article Categories
Archives
Post Categories
Image Galleries
Misc
SQL Server