February 11, 2015 at 11:00 am
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.
February 11, 2015 at 2:27 pm
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.
February 11, 2015 at 2:38 pm
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.
February 11, 2015 at 3:20 pm
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.
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
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply