non-equijoin performance question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

non-equijoin performance question

Author

Topic

ElCapitan
Starting Member

28 Posts

Posted - 2009-01-20 : 12:42:22

Hi all,
I have a requirement to join two tables on the values in those tables. There is natural association between these two tables e.g. no Parent - Child relationship.

Here is a cut down sample for the purpose of illustration...

tblRule
-----------
RuleID
DateOrderedBefore
PriceLessThan
ProductID

tblOrder
-----------
OrderID
UserID
DateOrdered
Price
ProductID

SELECT RuleID FROM tblRule INNER JOIN tblOrder
ON DateOrdered<DateOrderedBefore
AND tblRule.ProductID=tblOrder.ProductID
AND tblOrder.Price<tblRule.PriceLessThan
WHERE tblRule.RuleID IN (1,2,3,4,5,6,7,8,9)
AND tblOrder.UserID=1

My question is about MS SQL Server performance when it comes to non-equijoins such as the illustration above. Are non-equijoins just as efficient as equijoins? Are they less efficient than an equijoin however the difference is negligable? Is it perfectly valid to use them in this way?

Any help including links to resources will be greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-20 : 13:01:32

Non-equi joins are bad for performance.

The number of touched records is almost (records in table1) * (records in table2) / 2.

E 12°55'05.63"
N 56°04'39.26"

ElCapitan
Starting Member

28 Posts

Posted - 2009-01-21 : 08:43:58

Thanks for your reply Peso.

I am not sure of the mechanics of how SQL will pull the data out. So, what if we restructured the query slightly...

SELECT * FROM tblOrder
WHERE EXISTS
(SELECT * FROM tblRule
WHERE tblRule.RuleID IN (1,2,3,4,5,6,7,8,9)
AND tblOrder.DateOrdered<tblRule.DateOrderedBefore
AND tblOrder.ProductID=tblRule.ProductID
AND tblOrder.Price<tblRule.PriceLessThan) AND tblOrder.UserID=1

Would this be more efficient? Is this a good / valid way of approaching the problem?

Thanks again for your time.

By the way, in the first sentence on the very first post, the sentence should have read...
'...There is NO natural association between these two tables e.g. no Parent - Child relationship.'

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-01-21 : 11:11:17

This "AND tblRule.ProductID=tblOrder.ProductID" conflicts with "...There is NO natural association between these two tables e.g. no Parent - Child relationship."...becasue the = bit establishes a relationship.

putting an index on tblorder.productid would be a performance boost.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources