patternsqlMinor
SQL cross join and multiple joins
Viewed 0 times
sqlcrossjoinmultipleandjoins
Problem
Is there a way to improve this SQL query? It involves multiple cross join and joins.
I have 3 tables, and I want to compute the cardinal product:
Here is a fiddle.
I have 3 tables, and I want to compute the cardinal product:
table N:
N
-
4
table sums:
i S
-----------
1 22
2 26
3 22
table mults:
i j M
-------------------
1 1 122
1 2 144
1 3 119
2 1 144
2 2 170
2 3 141
3 1 119
3 2 141
3 3 126Here is a fiddle.
SELECT ((N.n*M1.m)-(S1.s*S2.s)) / (SQRT((N.n*M2.m)-(pow(S1.s,2))) * SQRT((N.n*M3.m)-(pow(S2.s,2))))
FROM N
CROSS JOIN sums as S1
CROSS JOIN sums as S2
JOIN mults as M1
ON M1.i = S1.i
AND M1.j = S2.i
JOIN mults as M2
ON M2.i = S1.i
AND M2.j = S1.i
JOIN mults as M3
ON M3.i = S2.i
AND M3.j = S2.i;Solution
This SQL, for what it does, is neat, and concise. There is no obvious place where any optimizations can be made. With the data size as small as it is, there is no reason to recommend indexes, or other improvements.
The only glaring issue has been pointed out already: There are tools other than SQL that are designed to do these types of heavy computation with performance that your database just won't beat.
Apart from anything else, the log functions are notoriously hard to get right, and there are multiple 'standards' for implementation.
Consider math libraries designed for data and computations such as this.
The only glaring issue has been pointed out already: There are tools other than SQL that are designed to do these types of heavy computation with performance that your database just won't beat.
Apart from anything else, the log functions are notoriously hard to get right, and there are multiple 'standards' for implementation.
Consider math libraries designed for data and computations such as this.
Context
StackExchange Code Review Q#18742, answer score: 3
Revisions (0)
No revisions yet.