February 6, 2015 at 3:30 pm
I was wondering if I have a table with 10 fields, just using a smaller table for an example. If I am doing an insert into this table for all 10 fields does the order the fields are listed in make any difference in performance? What about if the SQL was ran in SSIS, without insert into and using the data flow to map to the correct field?
Here is an example table and SQL:
CREATE TABLE dbo.test1
(
field_1DATETIME,
field_2INT,
field_3CHAR(10),
field_4VARCHAR(50),
field_5CHAR(1),
field_6DATETIME,
field_7INT,
field_8CHAR(10),
field_9VARCHAR(50),
field_10CHAR(1)
)
;
INSERT INTO dbo.test1 (field_5, field_8, field_1, field_6, field_2, field_9, field_4, field_3, field_10, field_7)
SELECT data_5, data_8, data_1, data_6, data_2, data_9, data_4, data_3, data_10, data_7
FROM dbo.test2
Would it be any fast/more efficient to do it this way?
INSERT INTO dbo.test1 (field_1, field_2, field_3, field_4, field_5, field_6, field_7, field_8, field_9, field_10)
SELECT data_1, data_2, data_3, data_4, data_5, data_6, data_7, data_8, data_9, data_10
FROM dbo.test2
This is a simplified example, in real life there are 20+ columns and over 200K records.
I know it is a lot easier to read and know if you are missing anything if they are in the same order. And that is how I would code it. But does it make a difference to SQLserver? Or SSIS as far as performance.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
February 6, 2015 at 3:35 pm
I'd like to say that it should not make any noticeable difference, but a better answer would be to test and measure both options to confirm it.
February 6, 2015 at 3:41 pm
I guess I was just hoping that someone knew this, or has already tested it, without running my on test. Like I said anytime I write SQL I try and put the fields in the same order as they appear in the table. I'm seeing some others write code where the fields are not in order and was just wondering if it made any difference.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
February 6, 2015 at 5:36 pm
Data is not stored in the order the columns are declared. IIRC, fixed length data is stored first, bit columns are consolidated together in bytes, and variable length data stored last.
I am sure others more knowledgeable with confirm, correct, and/or expand on this.
February 9, 2015 at 9:23 am
Lynn Pettis (2/6/2015)
Data is not stored in the order the columns are declared. IIRC, fixed length data is stored first, bit columns are consolidated together in bytes, and variable length data stored last.I am sure others more knowledgeable with confirm, correct, and/or expand on this.
Confirmation (from Paul Randal's "inside the storage engine"[/url] article)
The record structure is as follows:
•record header
?4 bytes long
?two bytes of record metadata (record type)
?two bytes pointing forward in the record to the NULL bitmap
•fixed length portion of the record, containing the columns storing data types that have fixed lengths (e.g. bigint, char(10), datetime)
•NULL bitmap
?two bytes for count of columns in the record
?variable number of bytes to store one bit per column in the record, regardless of whether the column is nullable or not (this is different and simpler than SQL Server 2000 which had one bit per nullable column only)
?this allows an optimization when reading columns that are NULL
•variable-length column offset array
?two bytes for the count of variable-length columns
?two bytes per variable length column, giving the offset to the end of the column value
•versioning tag
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 9, 2015 at 9:27 am
below86 (2/6/2015)
does the order the fields are listed in make any difference in performance? What about if the SQL was ran in SSIS, without insert into and using the data flow to map to the correct field?
No and No.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2015 at 3:55 pm
Well, OK then, I'm not sure why I thought it made a difference.
I know when listing the fields in your joins in the same way the index is created has always made a difference, I guess maybe I thought the same was true for the fields in the insert.
Thanks everyone for your input.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
February 10, 2015 at 4:05 pm
below86 (2/10/2015)
Well, OK then, I'm not sure why I thought it made a difference.I know when listing the fields in your joins in the same way the index is created has always made a difference, I guess maybe I thought the same was true for the fields in the insert.
Thanks everyone for your input.
AFAIK, order in the columns used in the join criteria doesn't affect performance. Using the correct columns to match the index will make a difference.
February 11, 2015 at 12:46 am
below86 (2/10/2015)
I know when listing the fields in your joins in the same way the index is created has always made a difference
It does not. Nor does the order of columns in a where clause have any relevance to anything except readability.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 11, 2015 at 7:01 am
GilaMonster (2/11/2015)
below86 (2/10/2015)
I know when listing the fields in your joins in the same way the index is created has always made a differenceIt does not. Nor does the order of columns in a where clause have any relevance to anything except readability.
I hate to disagree with you, but at least on our servers it has made a huge difference in performance. If the index was set up with these fields in this order, sys_id, acct_no, eff_date but in your join you had them in any other order it was like it was not using the index. And yes I did check the execution plan on that. I've had queries that ran for an hour, run in less than a minute after changing the order in the joins.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
February 11, 2015 at 7:34 am
below86 (2/11/2015)
GilaMonster (2/11/2015)
below86 (2/10/2015)
I know when listing the fields in your joins in the same way the index is created has always made a differenceIt does not. Nor does the order of columns in a where clause have any relevance to anything except readability.
I hate to disagree with you, but at least on our servers it has made a huge difference in performance. If the index was set up with these fields in this order, sys_id, acct_no, eff_date but in your join you had them in any other order it was like it was not using the index. And yes I did check the execution plan on that. I've had queries that ran for an hour, run in less than a minute after changing the order in the joins.
And I can tell you it was not due to the order of columns in a join. Not unless you're doing something like Column1+Column2 = Column3+Column4 (and probably not even then)
Changing the query would have forced a fresh execution plan, that's a fresh compile, possibly with parameters that made more sense to the current execution, possibly getting the optimiser to search a different part of the plan space and come up with a different plan. But then changing the order of columns in a select could cause the same thing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 11, 2015 at 7:52 am
GilaMonster (2/11/2015)
below86 (2/11/2015)
GilaMonster (2/11/2015)
below86 (2/10/2015)
I know when listing the fields in your joins in the same way the index is created has always made a differenceIt does not. Nor does the order of columns in a where clause have any relevance to anything except readability.
I hate to disagree with you, but at least on our servers it has made a huge difference in performance. If the index was set up with these fields in this order, sys_id, acct_no, eff_date but in your join you had them in any other order it was like it was not using the index. And yes I did check the execution plan on that. I've had queries that ran for an hour, run in less than a minute after changing the order in the joins.
And I can tell you it was not due to the order of columns in a join. Not unless you're doing something like Column1+Column2 = Column3+Column4 (and probably not even then)
Changing the query would have forced a fresh execution plan, that's a fresh compile, possibly with parameters that made more sense to the current execution, possibly getting the optimiser to search a different part of the plan space and come up with a different plan. But then changing the order of columns in a select could cause the same thing.
We are not doing this "Column1+Column2 = Column3+Column4 ". When I've looked at indexes for some tables I have eeven seen the same fields in the index, but in a different order. Like one where it has eff_date, exp_date in one index and the other is exp_date, eff_date. Maybe seeing examples like this on our tables has lead me to believe that the order of the fields makes a difference, but then again I've seen it impove performance. I'm not a DBA, and the DBA(s) that could have done this are no longer working here, and we are currently without a DBA. So for now I have to rely on the experts here, and Gail if you and others say it doesn't make a difference I have to believe you. I will still put them in order, becuase that makes the most sense to me.:-)
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
February 11, 2015 at 8:20 am
below86 (2/11/2015)
When I've looked at indexes for some tables I have eeven seen the same fields in the index, but in a different order. Like one where it has eff_date, exp_date in one index and the other is exp_date, eff_date.
That can make a huge difference when doing inequality matches on the two columns, or mixed equality and inequality.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 11, 2015 at 8:34 am
It should not cause any difference
February 11, 2015 at 9:15 am
The order of columns in the insert does not matter to the optimizer or engine. Also the order of columns in the SELECT, WHERE, and JOIN do not matter.
First, when comparing perormance of one SQL operation versus another, verify if the execution plan is different, or is it the same plan but with a different number of physical (disk) versus logical (buffer cache) reads. You can call DBCC DROPCLEANBUFFERS to clear the buffer cache before each execution.
https://technet.microsoft.com/en-us/library/ms187762(v=sql.110).aspx
If the exection plan has changed, then consider that the query optimizer is cost based and changes in statistics and avilable memory can influence the execution plan. For example when performing a hash join, the query optimizer gives preferance to the smaller of the two tables when choosing which input is used for building the hash table. The execution plan may change if one table involved in a join has subsequently received a large number of inserts.
http://blogs.msdn.com/b/craigfr/archive/2006/08/10/687630.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply