2

If I had a table with 3 columns, Column A Column B and Column C.

is the query faster if my select statement is like Select Column A, Column B, Column C from Table? what if it's Select Column A, Column C, Column B from Table?

Same for update and insert.

Update Table set Column A = '', Column B = '', Column C = '' and Update Table set Column A = '', Column C = '', Column B = ''

Insert into Table (Column A, Column B, Column C) Values() and Insert into Table (Column A, Column C, Column B) Values()

sabre
  • 207
  • 6
  • 18

3 Answers3

1

I don't believe any of the SQL standards dictate the performance requirements of individual statements so it's really totally under the control of the implementation.

However, I'd be very surprised if there was a substantial difference since the bulk of the time would be simply retrieving the data and delivering it.

Most DBMS' do a fair bit of analysis on statements before trying to execute them, so that they can reduce the effect on the retrieval phase. Things like figuring out whether all data can be retrieved by an index-only read, or choosing the correct index to use the minimum cardinality.

So it's possible that your column order may not survive the transition from analysis to execution anyway (it'll have to be re-instated for a select when delivering the data to the user, but not so for insert or update).

There may be a minuscule difference caused by re-ordering data from the order in which its stored in a record but, if it's significant, you should move to a better DBMS.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
0

No, not in the SELECT or UPDATE columns. Places where order MIGHT matter are the GROUP BY/ORDER BY clauses. Predicates in the WHERE clause and JOIN conditions will be re-ordered by the optimizer based on cost.

Pam Lahoud
  • 1,095
  • 6
  • 9
0

is the query faster if my select statement is like Select Column A, Column B, Column C from Table? what if it's Select Column A, Column C, Column B from Table?

I see you have tagged with multiple RDBMS tags. My answer is ONLY for Oracle.

In Oracle SQL, the order of columns in the SELECT list will have no effect in the performance of the query.

Let's test and verify:

CASE 1

SQL> EXPLAIN PLAN FOR
  2  SELECT empno, ename, deptno FROM emp;

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   182 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   182 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL>

CASE 2

SQL> EXPLAIN PLAN FOR
  2  SELECT deptno, ename, empno FROM emp;

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   182 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   182 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL>

CASE 3

SQL> EXPLAIN PLAN FOR
  2  SELECT deptno, empno, ename  FROM emp;

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   182 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   182 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL>

So, all the three test cases show that there is absolutely no difference at all.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • 1
    Although I fully agree your answer, I don't think you will never see any difference in any case when you select a table with only 14 rows and a few columns. – Wernfried Domscheit Mar 27 '15 at 06:36
  • It was just an example, i thought of showing it in PL/SQL with a million loops and show the timings. But, it would be too much of effort for this trivial thing. – Lalit Kumar B Mar 27 '15 at 07:02