BETWEEN In Join Condition

Question

BETWEEN In Join Condition

allen davidson

SSCertifiable

Points: 7042
More actions
June 26, 2009 at 10:13 am

#205865

I suppose the subject and description say it all but does a BETWEEN in the join condition make a triangular join?
Thanks
Allen

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398911 More actions · Answer 1

No. That wouldn't in and of itself make a triangular joind. Here's a great article by Jeff Moden[/url] that defines the triangular join better than I can.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

allen davidson SSCertifiable Points: 7042 More actions · Answer 2

Thanks Grant, I've seen a few of Jeff's articles on triangular joins and RBAR (and other stuff). I was using a < in the join and getting bad performance.

Essentially I am trying to allocate orders to a campaign where an order is allocated to the most recent campaign that occurred within 7 days prior to the order.

Is the code below inefficient (very simplified and obviously lacking indexes)?

USE tempdb

GO

CREATE TABLE tblOrders

(OrderNoBIGINT PRIMARY KEY,

OrderDateDATETIME,

CampaignIDINT)

-- dummy data

INSERT tblOrders (OrderNo, OrderDate)

SELECT 1, '1-JAN-2000' UNION

SELECT 2, '2-JAN-2000' UNION

SELECT 3, '3-JAN-2000' UNION

SELECT 4, '8-JAN-2000' UNION

SELECT 5, '9-JAN-2000' UNION

SELECT 6, '15-JAN-2000' UNION

SELECT 7, '16-JAN-2000' UNION

SELECT 8, '19-JAN-2000' UNION

SELECT 9, '20-JAN-2000' UNION

SELECT 10, '23-JAN-2000' UNION

SELECT 11, '24-JAN-2000'

CREATE TABLE tblCampaign

(CampaignIDINT PRIMARY KEY,

CampaignDateDATETIME)

-- dummy data

INSERT tblCampaign (CampaignID, CampaignDate)

SELECT 1, '1-JAN-2000' UNION

SELECT 2, '14-JAN-2000' UNION

SELECT 3, '17-JAN-2000'

UPDATE

tblOrders

SET

CampaignID = Y.CampaignID

FROM

tblOrders o INNER JOIN

(SELECT

c.CampaignID,

X.OrderNo

FROM

tblCampaign c INNER JOIN

(SELECT

o.OrderNo,

MAX(c.CampaignDate) AS MaxCampaignDate

FROM

tblOrders o INNER JOIN tblCampaign c

ON o.OrderDate BETWEEN c.CampaignDate AND DATEADD(dd,6,c.CampaignDate)

GROUP BY

o.OrderNo,

o.OrderDate) X

ON c.CampaignDate = X.MaxCampaignDate) Y

ON o.OrderNo = Y.OrderNo

SELECT * FROM tblCampaign

SELECT * FROM tblOrders

DROP TABLE tblCampaign

DROP TABLE tblOrders

Grant Fritchey SSC Guru Points: 398911 More actions · Answer 3

Once you get the indexes in place, I'd suggest that a TOP with an ORDER BY is going to be more efficient in this situation than the MAX that you're currently using. In general, not always, the aggregate function is more costly than the ordering, especially if you have a good index, clustered or covering, on the columns that you're ordering. But, test it both ways to see which works better in your system.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

allen davidson SSCertifiable Points: 7042 More actions · Answer 4

Thanks Grant, I had not considered using TOP.

Cheers

Allen