13

Does anyone knows if removing the field names from an INSERT query results in some performance improvements?

I mean is this:

INSERT INTO table1 VALUES (value1, value2, ...)

faster for DB to be accomplished rather than doing this:

INSERT INTO table1 (field1, field2, ...) VALUES (value1, value2, ...)

?

I know it might be probably a meaningless performance difference, but just to know.

I'm usually using MySQL and PostgreSQL as DBs.

Marco Demaio
  • 33,578
  • 33
  • 128
  • 159

3 Answers3

14

No, actually the contrary! At least for Microsoft SQL Server - you didn't specify what database you're talking about.....

If you don't specify the fields, either in a SELECT or an INSERT, then SQL Server's query processor must first go inspect the system catalogs to find out what fields are indeed available.

So I would always recommend to explicitly list the fields you want - on SELECTs as much as on INSERTs.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 2
    +1 I agree in principle, but wondered - won't it have to query the schema on the latter case for column definitions/defaults anyway? – Andy Mar 24 '10 at 18:26
  • @Andy: maybe so - but only for a subset of the fields - those you specify. For the INSERT, the difference might be extremely marginal - but for SELECTs, it's definitely worth explicitly specifying the fields - also for speed reasons – marc_s Mar 24 '10 at 18:27
  • 2
    +1 And also the readability would be really bad for a insert query with say 10 columns if one does not specify the column names. – Ashish Gupta Mar 24 '10 at 18:28
  • 3
    +1, but don't forget that listing columns keeps old code fom breaking when new columns are added. – KM. Mar 24 '10 at 18:38
  • 4
    It also keeps you from breaking the code when some fool decides to rearrange the column order. Always specify fields in all statements to be run on production. – HLGEM Mar 24 '10 at 18:47
  • Columns' ordinal positions within the table should ideally be arbitrary. Not a good idea to write application code that is reliant on a specific column order that is not explicitly defined by the application. Specifing the column order in each SQL DML statement is easier than discovering the columns' ordinal positions from the `INFORMATION SCHEMA` at run time. – onedaywhen Mar 25 '10 at 09:14
  • @marc_s: when you say '...specify the fields...in SELECT...' you mean to specify them instead of using the '*' character, don't you?! Thanks! – Marco Demaio Mar 25 '10 at 13:01
  • 1
    @Marco: yes, exactly - `SELECT *` is okay for a quick'n'dirty ad-hoc SQL query in SSMS when you're looking at stuff - but you should really NEVER use that in a production code anywhere; no exception. – marc_s Mar 25 '10 at 13:07
2

No, it's not faster. The database has to check which fields are in the table and match against the values anyway.

You should always specify the fields in the query to make the code more robust. If someone changes the order of the fields in the table, it stops working (or worse writes the data in the wrong field) if you haven't specified the fields.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
1

I don't know of any performance improvement by omitting the field names but I would advise against it for anything other than testing/once-only inserts.

Readability will suffer (you have to check the table to see what is being inserted where) and your code will break if the table schema is changed

CResults
  • 5,100
  • 1
  • 22
  • 28