Cross-Product Query Tuning

[RichardHayes]

I am finding a cross-product (Cartesian) query I have designed very slow to run. The only join I can impose on the two tables is that values lie within a range of other values. The other problem is one table has over a million records and the other in order of hundreds of thousands.

Any tips on how to speed this up?

SELECT
a.ID
,a.Value1
,a.Value2
,b.ID
,b.Value1
,b.Value2
FROM
a
,b
WHERE
ABS(a.Value1 - b.Value1) <= 1
AND ABS(a.Value2 - b.Value2) <= 1

 

[SemperFiDownUnda]

And? What do you think when the database has to produce
1,000,000 * 500,000
rows?
that is 500,000,000,000 rows .....let me emphise that 500 BILLION ROWS

Your WHERE clause doesn't narrow down records it has to look at. EVERY one still has to be looked at, all you are doing is stopping it from reaching the result set.


ABS(a.Value1 - b.Value1) <= 1
AND ABS(a.Value2 - b.Value2) <= 1

lets see if
this statement, if I read it correctly means you want records where b.Value1 is ±1 of a.Value same for a.Value2 and b.Value2

Have you tried doing a join?


SELECT
a.ID
,a.Value1
,a.Value2
,b.ID
,b.Value1
,b.Value2
FROM a
INNER JOIN b
ON B.Value1 BETWEEN A.Value1 - 1 AND A.Value1 + 1
AND B.Value2 BETWEEN A.Value2 - 1 AND A.Value2 + 1

If your Value1 and Value2 are index you should find it much faster

CROSS joins should be avoided like the plague unless you really do want EVERY record in the cross join.

 

[RichardHayes]

I didn't know you could use a range in a join, I guess it just hadn't crossed my mind. I will try this and let you know if I am successful.

Many thanks for your comments.

 

[SemperFiDownUnda]

I'll cross my fingers
I'm assuming you can....I've never done it myself but I don't see why not. JOINS are a different syntax to the 88 standard where you have the conditions in a WHERE clause. I've just JOINS with LIKE keyword before.
The biggest benifit you'll get is with proper indexs.

Good luck.