August 3, 2015 at 2:21 pm
I've been seeing a very odd SQL Server 2014 behavior, and wondered if anyone has seen it. I have a query that has a left join with a large partitioned table. The partitioned table has 10s of millions of records, and each partition has about 100,000 records. Nothing much, really.
The left join is part of an insert that gets a column from the partitioned table, if the column exists. The query contains the partition ID and all other joined columns are part of a non-clustered index.
Through the profiler, I found that there were millions of reads and the execution plan was giving me a table scan on the partitioned table.
I changed the query to do the insert followed by an update with inner join. That did the trick, but it worries me that SQL Server 2014 behaves differently from 2012 or 2008R2, which can make upgrading very time consuming. Has anyone seen this before?
August 4, 2015 at 1:05 am
Yes,I agree your points.little more difference between old and new versions in SQL..!
August 4, 2015 at 7:27 am
You're right, SQL Server 2014 has a new cardinality estimator which will change the behavior of some queries.
That said, you can't change a LEFT JOIN for an INNER JOIN. They're not equivalent and will change the results. If you're selecting the whole table, a table scan is completely natural.
If you want better advice, follow the advice on this article: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 7, 2015 at 1:26 pm
I fully understand that LEFT and INNER joins are different. We wouldn't have different commands to give the same result 😉
Anyhow, the problem I have is quite simple, but it means
declare @T1 table ( ID int, Value int, primary key clustered (ID) )
declare @T2 table ( ID int, Value int, primary key clustered (ID) )
declare @Result table ( ID int, Value int, primary key clustered (ID) )
insert @T1 ( ID, Value ) values
( 1, 1 ), ( 2, 2 ), ( 3, 3 ), ( 4, 4 )
insert @T2 ( ID, Value ) values
( 1, 10 ), ( 3, 30 )
Here's the query with performance problems:
insert @Result ( ID, Value )
select T1.ID, ISNULL(T2.Value,T1.Value)
from @T1 T1 LEFT JOIN @T2 T2 on T2.ID = T1.ID
"select ID, Value from @Result" gives:
ID = 1, Value = 10
ID = 2, Value = 2
ID = 3, Value = 30
ID = 4, Value = 4
Here's much better performance:
insert @Result ( ID, Value )
select ID, Value
from @T1
update R
set Value = T2.Value
from @Result R inner join @T2 T2 on
T2.ID = R.ID
"select ID, Value from @Result" gives:
ID = 1, Value = 10
ID = 2, Value = 2
ID = 3, Value = 30
ID = 4, Value = 4
The execution plan for the LEFT JOIN query, when extrapolated to permanent partitioned tables with millions of records, has 10s of millions of reads. The insert/update query has very few reads (compared to the LEFT JOIN query) and runs significantly faster. The insert/update doesn't have left joins.
I didn't have this problem in SQL2012 or SQL2008R2, but I have problems with left joins on SQL2014. The change in how the query plans are generated in SQL2014 has degraded performance of the left join significantly. I have many procedures with left joins just like that, and performance is now horrendous.
August 10, 2015 at 12:11 pm
There is a way to get the "old" behavior back - via a trace flag. If my recall is correct, it's 2453, but someone please verify. I know that trace flag has been posted in the forums recently, so you can probably find it easily enough. I don't know if it's server-wide or query wide in it's scope.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply