5
\$\begingroup\$

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:

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   126

Here 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;
\$\endgroup\$
3
  • \$\begingroup\$ Does it have to be done in SQL? I would suck the data out and do it in memory instead. There are some powerful matrix and numeric libraries out there. \$\endgroup\$
    – Leonid
    Nov 17, 2012 at 17:33
  • \$\begingroup\$ yeah, I know LAPACK for example but with sql queries? \$\endgroup\$
    – edgarmtze
    Nov 17, 2012 at 17:33
  • 3
    \$\begingroup\$ I agreed with @Leonid. Using a database like this is likely to fall apart if you try to apply this approach on a little larger data sets, but another issue is that it looks so much like a math problem that you really should use a math tool just to make it easier to maintain and debug later. \$\endgroup\$ Nov 18, 2012 at 21:24

1 Answer 1

3
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.