July 17, 2008 at 4:31 am
I had a strange thought this morning when answering a question for someone else, and then an even stranger thought when I realized I should know the answer to this, but....
When writing up an INSERT statement, do you get a performance hit when you don't write up the columns in ordinal order of how they are in the database?
I.E., if I do "Insert into MyTable (Col5, Col2, Col1, Col4, Col3)" does it cause the engine to do more work than if I do "Insert into MyTable (Col1, Col2, Col3, Col4, Col5)"?
I used to tell people it didn't matter. That SQL would do the insert regardless of what order the columns were in as long as the VALUES or SELECT statement was in the same order (which it will), but I never thought about potential performance impact. Granted, I could probably run Profiler and check this out, but I'm in the middle of a few things at the moment and thought I'd see if anyone else had done this experiment before I went to the trouble.
Thoughts?
July 17, 2008 at 5:00 am
It won't make any difference to how the data is actually stored in the database, so there will be no performance difference.
July 17, 2008 at 5:05 am
I used to think that, but given how Indexes are set up (depending on the Clustered or Non-Clustered) and whether or not you have Triggers / Defaults / Etc... I have to wonder.
Do those performance hits affect you the same regardless of column order in the Insert statement?
July 17, 2008 at 5:38 am
Do those performance hits affect you the same regardless of column order in the Insert statement?
The column order in the insert statement makes absolutely no difference to those performance hits.
The triggers will fire just the same, the indexes will have to be built just the same, regardless of whether they are clustered or not.
You might get the tiniest (and I mean TINY) improvement by not mentioning in the column list, a column that has a default... but to know if that makes any difference at all, you'd have to know how SQL Server is coded at a very low level!
July 17, 2008 at 6:52 am
There probably is a vanishingly small performance hit for that, yes. However, all the other issues around writing are so intensive, that little tiny blip where the query engine rearranges the columns is probably not noticeable on even the most intensely accessed systems.
Our local SQL Server users group (SNESSUG) had Andre Novick in to speak recently. He was talking about how to manage databases at the 2-3tb level. One interesting point he made is that while CPU's have increased performance over the last 30 years by millions of times, disk performance has only increased only about a 100 times over the same period. Writing the data, and any indexes, plus page splits, etc., is going to cost so much more than a CPU cycle or three that worrying about the CPU just isn't worth the time saved.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply