Thursday, March 8, 2012

Conditional Joins

Hi, I have a query that needs to filter on certain data only if needed. eg.
(Assume tblStock and tblFilter are 1-to-1 on StockID)
DECLARE @.Min TINYINT , @.Max TINYINT
SELECT @.Min = 5 , @.Max = 10
SELECT
StockID
FROM
tblStock
INNER JOIN
tblFilter
ON
tblStock.StockID = tblFilter.StockID
AND
(
tblFilter.Value BETWEEN @.Min AND @.Max
OR
@.Min IS NULL OR @.Max IS NULL
)
Preferably I'd like to not do the join at all if either variable is null,
and I'd like it in a stored procedure so it's precompiled.
This is a simplified version of the query, in our system there are
aproximately 8 filters, which may or may not be required - and in any
combination.
I could, in theory create a stored proc for each combination of filter
inclusion - but this will be hard to maintain - especially if/when more
filters are introduced.
Thanks.
Rebecca.Please always include DDL with questions like this. It also helps to
include sample data and required results.
If Value is not nullable then maybe you just want to set @.min and @.max
to the min and max values for INT. Otherwise, use EXISTS:
SELECT stockid
FROM tblstock
WHERE EXISTS
(SELECT *
FROM tblfilter
WHERE tblStock.StockID = tblFilter.StockID
AND (stockid BETWEEN @.min AND @.max
OR @.min IS NULL
OR @.max IS NULL)) ;
David Portas
SQL Server MVP
--|||I have included the full ddl for the sample i've included.
Essentially what I want to do is put an IF around the Inner joins to exclude
the join if the parameters filtering the value are null.
Currently, we have the query dynamically built in the client, but this gives
compaliation overheads when various combinations are excluded/inculded and
we are investigating a pre-compiled version.
USE tempdb
/* Create tables */
IF OBJECT_ID('dbo.tblFilter2') IS NOT NULL DROP TABLE dbo.tblFilter2
IF OBJECT_ID('dbo.tblFilter1') IS NOT NULL DROP TABLE dbo.tblFilter1
IF OBJECT_ID('dbo.tblStock') IS NOT NULL DROP TABLE dbo.tblStock
CREATE TABLE dbo.tblStock ( StockID INT PRIMARY KEY CLUSTERED , Other
VARCHAR(5) NULL , Fields VARCHAR(5) )
CREATE TABLE dbo.tblFilter1 ( StockID INT PRIMARY KEY NONCLUSTERED , Value
INT , CONSTRAINT FK_dbo_tblFilter1_dbo_tblStock FOREIGN KEY ( StockID )
REFERENCES dbo.tblStock ( StockID ) )
CREATE TABLE dbo.tblFilter2 ( StockID INT PRIMARY KEY NONCLUSTERED , Value
DATETIME , CONSTRAINT FK_dbo_tblFilter2_dbo_tblStock FOREIGN KEY ( StockID )
REFERENCES dbo.tblStock ( StockID ) )
CREATE CLUSTERED INDEX CIX_dbo_tblFilter1 ON dbo.tblFilter1 ( Value )
CREATE CLUSTERED INDEX CIX_dbo_tblFilter2 ON dbo.tblFilter2 ( Value )
/* Populate sudo-random data */
INSERT INTO
dbo.tblStock
SELECT
N AS StockID
, NULL AS Other
, NULL AS Fields
FROM
dbo.tblNumbers /* contains n = 1 to 1,000,000 */
WHERE
N BETWEEN 1 AND 5000
INSERT INTO
dbo.tblFilter1
SELECT
StockID
, ( 5000 - StockID ) + 1 AS Value
FROM
dbo.tblStock
INSERT INTO
dbo.tblFilter2
SELECT
StockID
, DATEADD( d , -StockID , [Now] ) AS Value
FROM
dbo.tblStock
CROSS JOIN
( SELECT GETDATE() AS [Now] ) vwDate
/* Search Query */
GO
CREATE PROCEDURE
dbo.prRunFilter
@.Min INT
, @.Max INT
, @.FilerMonth DATETIME /* assumes midnight, first of month */
AS
SET DATEFORMAT YMD
DECLARE @.StartOfMonth DATETIME , @.EndOfMonth DATETIME
SELECT @.StartOfMonth = @.FilerMonth , @.EndOfMonth = DATEADD( ms , -3 ,
DATEADD( m , 1 , @.FilerMonth ) )
SELECT
tblStock.StockID
, tblFilter1.Value
, tblFilter2.Value
FROM
dbo.tblStock tblStock
INNER JOIN
(
SELECT
StockID
, Value
FROM
dbo.tblFilter1
WHERE
Value BETWEEN @.Min AND @.Max
) tblFilter1
ON
tblStock.StockID = tblFilter1.StockID
INNER JOIN
(
SELECT
StockID
, Value
FROM
dbo.tblFilter2
WHERE
Value BETWEEN @.StartOfMonth AND @.EndOfMonth
) tblFilter2
ON
tblStock.StockID = tblFilter2.StockID
GO
EXEC dbo.prRunFilter @.Min = 4490 , @.Max = 4500 , @.FilerMonth = {d
'2004-05-01'}|||I would create a view:
create view t1
as
SELECT
StockID
FROM
tblStock
INNER JOIN
tblFilter
ON
tblStock.StockID = tblFilter.StockID
in the procedure, I would
if @.Min IS NULL OR @.Max IS NULL
-- the plan for this may be simpler
select * from t1
else
-- this query might need a different plan
select * from t1
where tblFilter.Value BETWEEN @.Min AND @.Max
this way both the maintenance of the query is easy, as it's in a view,
and you have 2 plans|||Except that, as stated in my original post, we have 8 such filters...
This would result in 64 if/else statements and views to handle all the
combinations.
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1128437238.893216.20870@.o13g2000cwo.googlegroups.com...
> I would create a view:
> create view t1
> as
> SELECT
> StockID
> FROM
> tblStock
> INNER JOIN
> tblFilter
> ON
> tblStock.StockID = tblFilter.StockID
> in the procedure, I would
> if @.Min IS NULL OR @.Max IS NULL
> -- the plan for this may be simpler
> select * from t1
> else
> -- this query might need a different plan
> select * from t1
> where tblFilter.Value BETWEEN @.Min AND @.Max
> this way both the maintenance of the query is easy, as it's in a view,
> and you have 2 plans
>|||no - just one view, and yes, some if ... else statements.
Believe me or not, if you are selecting from big tables, then the
performance penalty of running the generic plan may be pretty high.
That, of course, has been said assuming that there are indexes that
could be used for some of your filters.

No comments:

Post a Comment