Optimize SQL select query with join

Question

I have two tables:

 Shop_Products
 Shop_Products_Egenskaber_Overruling

I want to select all records in Shop_Products_Egenskaber_Overruling which has a related record in Shop_Products. This Means a record with an equal ProductNum.

This Works for me with the statement below, but I don't think a CROSS JOIN is the best approach for large record sets. When using the statement in web controls, it becomes pretty slow, even with only 1000 records. Is there a better way to accomplish this?

 SELECT     Shop_Products.*, Shop_Products_Egenskaber_Overruling.*
 FROM       Shop_Products CROSS JOIN
            Shop_Products_Egenskaber_Overruling
 WHERE      Shop_Products.ProductNum = Shop_Products_Egenskaber_Overruling.ProductNum

Any optimizing suggestions?

Best regards.

score 3 · Answer 1 · answered Nov 28 '13 at 10:03

3

You can do it that way but not sure it will ensure an optimization

SELECT     Shop_Products.*, Shop_Products_Egenskaber_Overruling.*
FROM       Shop_Products 
INNER JOIN Shop_Products_Egenskaber_Overruling on Shop_Products.ProductNum = Shop_Products_Egenskaber_Overruling.ProductNum

answered Nov 28 '13 at 10:03

T0to

422
2
5

Thank you, inner join was the solution! – micknt Nov 29 '13 at 08:06

score 2 · Accepted Answer · answered Nov 28 '13 at 10:03

2

You are actually looking for an INNER JOIN.

SELECT 
    SO.*,
    SPEO.*
FROM SHOP_PRODUCTS SP 
    INNER JOIN Shop_Products_Egenskaber_Overruling SPEO 
        ON SP.ProductNum = SPEO.ProductNum

This will have improved performance over your CROSS-JOIN, because the condition to look for records with equal ProductNum is implicit in the JOIN condition and the WHERE clause is eliminated.

WHERE clauses always execute AFTER a JOIN. In your case, all possible combinations are created by the CROSS JOIN and then filtered by the conditions in the WHERE clause.

By using an INNER JOIN you are doing the filtering in the first step.

answered Nov 28 '13 at 10:03

Radu Gheorghiu

20,049
16
72
107

Thanks for the working solution and the explanation! It really boosted the load time... – micknt Nov 29 '13 at 08:05
@micknt You're welcome. Just as a side note, CROSS JOIN should always be your last option in term of JOINS. – Radu Gheorghiu Nov 29 '13 at 09:16

score 1 · Answer 3 · edited May 23 '17 at 10:31

1

Cross join is slower, because it produce all combinations, which filtred after by where predicate. So you can use INNER JOIN for better performance. But I think It would be useful if you check execution plan of this query anyway, because in Oracle there is no difference between where and inner join solutions Inner join vs Where

edited May 23 '17 at 10:31

Community

1
1

answered Nov 28 '13 at 10:14

Alexander Myshov

2,881
2
20
31

Thank you, inner join was the solution! – micknt Nov 29 '13 at 08:08

score 0 · Answer 4 · answered Nov 28 '13 at 10:05

0

Try using INNER JOIN

SELECT     Produkter.*, Egenskaber.*
FROM       Shop_Products Produkter
INNER JOIN Shop_Products_Egenskaber_Overruling Egenskaber ON Produkter.ProductNum=Egenskaber.ProductNum

Jag namngav aven dem pa Norska..

answered Nov 28 '13 at 10:05

Mad Dog Tannen

7,129
5
31
55

Thank you, inner join was the solution! – micknt Nov 29 '13 at 08:07

Optimize SQL select query with join

4 Answers4