CODEWITHSUNDEEP
sqldatabase
mvcorrea
mvcorrea

Reputation: 101

performance difference between queries with inner queries

Let's use a simple "redundant" query like this.

SELECT * FROM
 (SELECT * FROM
   (SELECT * FROM mytable) AS X) AS Y

Are there any optimisation (prior to execution) on the database engine that converge to the last one without loss in performance? What database engines do that?

as suggested I will put the real question. And a possible bet on the performance in each query :)

here it goes..

SELECT * FROM t1 JOIN t2 ON t1.chkId = t2.xchkId;

and

SELECT * FROM
 (SELECT * FROM t1) AS X
JOIN
 (SELECT * FROM t2) AS Y
ON X.chkId = Y.xchkId;

of course I can reduce the domain on the last (but its not the case now). doing the same! any difference in performance?

Upvotes: 0

Views: 77

Answers (3)

user3112728
user3112728

Reputation: 405

Having two sub queries that are combined like your example is a pretty common sql pattern. Conceptually, each sub query is a temporary table. You'll see it show up in the query plan as some sort of table operator like an index seek or scan.

Depending on the complexity of the sub query: 1. using the sub-query like your example can be best. 2. placing the sub-query's results into an actual temp table or table variable can be best.

It's helpful to get much more specific in your question and focus on the query plan.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

The database that you are talking about is very important.

As mentioned in the comments, pretty much every reasonable database would ignore the subqueries and compile the queries to the same underlying code. An easy way to understand this is that SQL is not a procedural language; a SQL query specifies the structure of the output, not how it is generated.

In general, the underlying engine is a dataflow engine that contains a bunch of algorithms for different tasks, such as joining tables, using indexes, and aggregation. What gets executed is pretty far from the SQL statement itself.

All that said, not all databases are "reasonable". In particular, MySQL (and hence MariaDB) materialize subqueries. Hence, the structure does differ. Other simple databases may do this as well.

Upvotes: 2

geofftnz
geofftnz

Reputation: 10102

Examining the query plan on SQLServer 2012 shows that it compiles to a single scan. Pretty much every serious database should exhibit the same behaviour.

Query plan

Upvotes: 2

Related Questions