Will order of fields create any performance issues?

Question

Will order of fields create any performance issues?

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

below86 SSChampion Points: 11538 More actions · Answer 1

Eric M Russell (2/11/2015)
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

I did see where the execution plan changed when I changed the order of the fields to match the index. Before it was doing a scan and after an index seek on the table. It has been several years since I did this. I do see the same situation happening in DB2, where a join on just two fields, both in the index, performs faster when the fields match the order they appear in the index. Some day, when I get some freee time, Ha Ha, I will run some additional test on our servers.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

below86 (2/11/2015)
Some day, when I get some freee time, Ha Ha, I will run some additional test on our servers.

Let me save you some time. 😉

As you'll see, both queries return the same execution plan.

-- Declare some variables

DECLARE @NumberOfRows INT,

@StartValue INT,

@EndValue INT,

@Range INT,

@StartDate DATETIME;

-- Preset the variables

SELECT @NumberOfRows = 1000000,

@StartValue = 1,

@EndValue = 500,

@Range = @EndValue - @StartValue + 1,

@StartDate = '2014';

-- Conditionally drop the tables to make reruns easier

IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL

DROP TABLE #TestTable;

IF OBJECT_ID('tempdb..#TestJoinTable','U') IS NOT NULL

DROP TABLE #TestJoinTable;

-- Create the test table with "random constrained" integers, floats and dates

SELECT TOP (@NumberOfRows)

myInteger = ABS(CHECKSUM(NEWID())) % @Range + @StartValue,

myFloat = RAND(CHECKSUM(NEWID())) * @Range + @StartValue,

myDate = ABS(CHECKSUM(NEWID())) % @Range + @StartDate

INTO #TestTable

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2;

-- Create a smaller table to join with

SELECT TOP( @NumberOfRows / 100) *

INTO #TestJoinTable

FROM #TestTable

-- Create indexes for both tables

CREATE CLUSTERED INDEX IDXTest ON #TestTable(myInteger, myDate)

CREATE CLUSTERED INDEX IDXJoinTest ON #TestJoinTable(myInteger, myDate)

-- Clean buffers and procedure chache

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

DBCC FREEPROCCACHE WITH NO_INFOMSGS

-- Query using index column order

SELECT *

FROM #TestTable t

JOIN #TestJoinTable j ON t.myInteger = j.myInteger

AND t.myDate = j.myDate

WHERE j.myInteger < 100 --Removing the WHERE clause generates an Index Scan because it reads the entire table

-- Clean buffers and procedure chache (again)

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

DBCC FREEPROCCACHE WITH NO_INFOMSGS

-- Query using column order different than index column order

SELECT *

FROM #TestTable t

JOIN #TestJoinTable j ON t.myDate = j.myDate

AND t.myInteger = j.myInteger

WHERE j.myInteger < 100 --Removing the WHERE clause generates an Index Scan because it reads the entire table

Let me know if you have any comments on this.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

below86 SSChampion Points: 11538 More actions · Answer 3

Luis Cazares (2/11/2015)
below86 (2/11/2015)
Some day, when I get some freee time, Ha Ha, I will run some additional test on our servers.
Let me save you some time. 😉

Thanks for the sample code Luis, I'll give it a look some time. I know we would not have had a where statement like that.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Eric M Russell SSC Guru Points: 125562 More actions · Answer 4

Changing the ordinal position of columns in the ORDER BY clause can cause a change in the execution plan. If the ordinal position matches an index, then it can leverage the index for sorting the resultset. If not, then SQL Server may create a hash table to faciliate the sorting.

Also if you have query that is covered by an index (google "sql server covering indexes"), then adding a single column to a SELECT, WHERE, or ORDER BY clause can completely change the execution plan, for example causing the plan to resort to a table scan or bookmark lookups to fetch the additional column not contained in the index.

https://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/

http://blogs.msdn.com/b/craigfr/archive/2006/06/30/652639.aspx

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho