CODEWITHSUNDEEP
sqlsql-server
Hoy Cheung
Hoy Cheung

Reputation: 1670

SQL Server hangs querying 2 independent tables

I am querying 2 independent tables but related data.

The query is as follows:

select 
    STDEV(M1.[Close]) as M1, STDEV(M2.[Close]) as M2 
from 
    M1, M2;

I want to show 2 standard deviation data on separate columns.

But SQL Server hangs there and stops moving. Why is that? It is a very simple query.

How can I do so without crossing 2 tables? The tables are huge.

Upvotes: 2

Views: 61

Answers (3)

LukStorms
LukStorms

Reputation: 29647

Cross joins on big tables can have that effect on performance.
Although there are some rare situations where they are useful, it's best to avoid them.

Serkan's answer that uses 2 nested sub-queries is the best solution for this question.
Short and easy to understand.

But to get a result from 2 unrelated tables, one could also use UNION ALL with placeholder values.

SELECT
 SUM(StddevM1) AS M1,
 SUM(StddevM2) AS M2
FROM
(
    SELECT STDEV(M1.Close) as StddevM1, 0 AS StddevM2
    FROM M1
    UNION ALL
    SELECT 0, STDEV(M2.Close)
    FROM M2
) q;

On second thought, one could actually use a CROSS JOIN here ;)

SELECT q1.M1, q2.M2
FROM (SELECT STDEV([Close]) as M1 FROM M1) q1
CROSS JOIN (SELECT STDEV([Close]) as M2 FROM M2) q2;

Upvotes: 1

Hoy Cheung
Hoy Cheung

Reputation: 1670

I understand what you guys are saying, but i just want to understand more how SQL works behind

if 

from A, B

has the same result as

from A cross join B

then why 

select STDEV(M1.[Close]) as M1 from M1, M2;

process so quickly, but

select STDEV(M1.[Close]) as M1, STDEV(M2.[Close]) as M2 from M1, M2;

takes forever?

both queries need to multiple 2 tables first.

Upvotes: -1

Serkan Arslan
Serkan Arslan

Reputation: 13393

you can use this.

 SELECT 
   (SELECT STDEV(M1.[Close]) from M1) as M1 , 
   (SELECT STDEV(M2.[Close]) from M2) as M2;

Upvotes: 5

Related Questions