SQL NESTED Query Optimization

Question

I am running two sql queries say,

select obname from table1 where obid = 12

select modname from table2 where modid = 12

Both are taking very less time, say 300 ms each.

But when I am running:

select obname, modname 
from (select obname from table1 where obid = 12) as alias1, 
(select modname from table2 where modid = 12) as alias2

It is taking 3500ms. Why is it so?

First of all you are comparing 2 queries to 1,in execution queries are not like apples just add the times together and its done — Mihai
– Mihai, Commented Oct 17, 2015 at 11:53

Gordon Linoff · Accepted Answer · 2015-10-17 11:54:25Z

1

In general, putting two scalar queries in the from clause is not going to affect performance. In fact, from an application perspective, one query may be faster because there is less overhead going back and forth to the database. A scalar query returns one column and one row.

However, if the queries are returning multiple rows, then your little comma is doing a massive Cartesian product (which is why I always use CROSS JOIN rather than a comma in a FROM clause). In that case, all bets are off, because the data has to be processed after the results start returning.

answered Oct 17, 2015 at 11:54

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jayant Over a year ago

Even cross join is taking the same time for me...because the second query is returning only one row

Collectives™ on Stack Overflow

SQL NESTED Query Optimization

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related