CODEWITHSUNDEEP
sqlselect
Gokul
Gokul

Reputation: 329

SQL query timings

Does it take more time to create a table using select as statement or to just run the select statement? Is the time difference too large or can it be neglected?

For example between

create table a
as select * from b
where c = d;

and

select * from b
where c = d;

which one should run faster and can the time difference be neglected?

Upvotes: 0

Views: 92

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Creating the table will take more time. There is more overhead. If you look at the metadata for your database, you will find lots of tables or views that contain information about tables. In particular, the table names and column names need to be stored.

That said, the data processing effort is pretty similar. However, there might be overhead in storing the result set in permanent storage rather than in the data structures needed for a result set. In fact, the result set may never need to be stored "on disk" (i.e. permanently). But with a table creation, that is needed.

Depending on the database, the two queries might also be optimized differently. The SELECT query might be optimized to return the first row as fast as possible. The CREATE query might be optimized to return all rows as fast as possible. Also, the SELECT quite might just look faster if your database and interface start returning rows when they first appear.

I should point out that under most circumstances, the overhead might not really be noticeable. But, you can get other errors with the create table statement that you would not get with just the select. For instance, the table might already exist. Or duplicate column names might pose a problem (although some databases don't allow duplicate column names in result sets either).

Upvotes: 2

Related Questions