SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Inner join vs Left join


Inner join vs Left join

Author
Message
SpinyNorman
SpinyNorman
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 37
Hi Everyone,

I'm experiencing something I don't expect and I'm hoping someone can shed some light. We are using entity framework and LINQ to query our SQL Server 2008 R2 database and it is generating the following (fairly ugly) SQL:


SELECT
[Extent1].[ValuationDateID] AS [ValuationDateID],
[Extent3].[TransactionRef] AS [TransactionRef],
[Extent3].[Counterparty] AS [Counterparty],
[Extent2].[BookName] AS [BookName],
[Extent2].[Category] AS [Category],
[Extent2].[Type] AS [Type],
[Extent2].[LifeLicence] AS [LifeLicence],
[Extent2].[Manager] AS [Manager],
[Extent2].[TaxFund] AS [TaxFund],
[Extent2].[HiPortName] AS [HiPortName],
[Extent2].[AliasPortfolioName] AS [AliasPortfolioName],
[Extent4].[ParallelShift] AS [ParallelShift],
[Extent5].[Tenor] AS [Tenor],
[Extent1].[Value] AS [Value]
FROM (SELECT
[fctSwapDualShift].[ValuationDateID] AS [ValuationDateID],
[fctSwapDualShift].[Currency] AS [Currency],
[fctSwapDualShift].[BookID] AS [BookID],
[fctSwapDualShift].[HiPortNameID] AS [HiPortNameID],
[fctSwapDualShift].[ContractTypeID] AS [ContractTypeID],
[fctSwapDualShift].[TypeID] AS [TypeID],
[fctSwapDualShift].[ManagerID] AS [ManagerID],
[fctSwapDualShift].[TransactionID] AS [TransactionID],
[fctSwapDualShift].[ParallelShiftID] AS [ParallelShiftID],
[fctSwapDualShift].[SwapTenorID] AS [SwapTenorID],
[fctSwapDualShift].[Value] AS [Value],
[fctSwapDualShift].[ClientID] AS [ClientID]
FROM [Reporting].[fctSwapDualShift] AS [fctSwapDualShift]) AS [Extent1]
INNER JOIN (SELECT
[vwAllMappingWithDescriptions].[BookID] AS [BookID],
[vwAllMappingWithDescriptions].[ValuationDateID] AS [ValuationDateID],
[vwAllMappingWithDescriptions].[BookName] AS [BookName],
[vwAllMappingWithDescriptions].[Category] AS [Category],
[vwAllMappingWithDescriptions].[LifeLicence] AS [LifeLicence],
[vwAllMappingWithDescriptions].[TaxFund] AS [TaxFund],
[vwAllMappingWithDescriptions].[Type] AS [Type],
[vwAllMappingWithDescriptions].[HiPortName] AS [HiPortName],
[vwAllMappingWithDescriptions].[AliasPortfolioName] AS [AliasPortfolioName],
[vwAllMappingWithDescriptions].[Manager] AS [Manager],
[vwAllMappingWithDescriptions].[RasHiPortCode] AS [RasHiPortCode],
[vwAllMappingWithDescriptions].[LiquidityRiskClassification] AS [LiquidityRiskClassification],
[vwAllMappingWithDescriptions].[IncludeForRisk] AS [IncludeForRisk]
FROM [configuration].[vwAllMappingWithDescriptions] AS [vwAllMappingWithDescriptions]) AS [Extent2] ON [Extent1].[BookID] = [Extent2].[BookID]
INNER JOIN [Reporting].[dimTransaction] AS [Extent3] ON [Extent1].[TransactionID] = [Extent3].[ID]
INNER JOIN [Reporting].[dimParallelShift] AS [Extent4] ON [Extent1].[ParallelShiftID] = [Extent4].[ID]
INNER JOIN [Reporting].[dimSwapTenor] AS [Extent5] ON [Extent1].[SwapTenorID] = [Extent5].[ID]
WHERE (20130314 = [Extent1].[ValuationDateID]) AND (20130314 = [Extent2].[ValuationDateID])



Trying to run this query yields:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.


However, if I change the INNER JOIN on the Reporting.dimParallelShift to a LEFT JOIN, then the query returns the correct results.

Here's the part I don't understand. The ParallelShiftID column on the Reporting.fctSwapDualShift fact table is NOT nullable. Furthermore, the dimParallelShift dimension as a primary key on the ID column and so it is unique. To my mind, in this case, the LEFT join and INNER join are logically equivalent but that obviously isn't the case. What am I missing?
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5248 Visits: 5478
You have posted incomplete statement...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
SpinyNorman
SpinyNorman
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 37
The select statement on my post is complete - I just copied it from my post into SSMS, changed the offending join to a LEFT join as described in my original post, and it ran.

Are you referring to another statement? If you require more information, please let me know and I'll do my best to provide it.
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5248 Visits: 5478
Ough, that's fine. I just couldn't see all closing brackets from first glance.
There are many different reason for this error to happen.
Have you seen this one? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36409

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
SpinyNorman
SpinyNorman
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 37
Thanks for posting the link. I can safely eliminate any of the .Net-related causes because I have this issue simply running the query in management studio. All I know is that switching from an INNER join to a LEFT join seems to make a difference. I assume this means different query plans are being generated, but I can't even see an estimated query plan for the inner join because the same error comes up when I ask for one.

UPDATE: We have a filtered index on the fact table. I disabled the index and now the query runs using the INNER join too. Here is the code for the index:


/****** Object: Index [idx_fctSwapDualShift_ValueAggregation] Script Date: 03/15/2013 12:34:24 ******/
CREATE NONCLUSTERED INDEX [idx_fctSwapDualShift_ValueAggregation] ON [Reporting].[fctSwapDualShift]
(
[ValuationDateID] ASC,
[BookID] ASC,
[HiPortNameID] ASC,
[ContractTypeID] ASC,
[TypeID] ASC,
[ManagerID] ASC,
[ParallelShiftID] ASC,
[SwapTenorID] ASC,
[ClientID] ASC
)
INCLUDE ( [Value])
WHERE ([Value]<>(0))
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO



Any ideas why this makes a difference?
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5248 Visits: 5478
Can you try this one (with index enabled):


SELECT
[Extent1].[ValuationDateID] AS [ValuationDateID],
[Extent3].[TransactionRef] AS [TransactionRef],
[Extent3].[Counterparty] AS [Counterparty],
[Extent2].[BookName] AS [BookName],
[Extent2].[Category] AS [Category],
[Extent2].[Type] AS [Type],
[Extent2].[LifeLicence] AS [LifeLicence],
[Extent2].[Manager] AS [Manager],
[Extent2].[TaxFund] AS [TaxFund],
[Extent2].[HiPortName] AS [HiPortName],
[Extent2].[AliasPortfolioName] AS [AliasPortfolioName],
[Extent4].[ParallelShift] AS [ParallelShift],
[Extent5].[Tenor] AS [Tenor],
[Extent1].[Value] AS [Value]
FROM [Reporting].[fctSwapDualShift] AS [fctSwapDualShift] AS [Extent1]
INNER JOIN [configuration].[vwAllMappingWithDescriptions] AS [vwAllMappingWithDescriptions] AS [Extent2]
ON [Extent1].[BookID] = [Extent2].[BookID]
INNER JOIN [Reporting].[dimTransaction] AS [Extent3]
ON [Extent1].[TransactionID] = [Extent3].[ID]
INNER JOIN [Reporting].[dimParallelShift] AS [Extent4]
ON [Extent1].[ParallelShiftID] = [Extent4].[ID]
INNER JOIN [Reporting].[dimSwapTenor] AS [Extent5]
ON [Extent1].[SwapTenorID] = [Extent5].[ID]
WHERE (20130314 = [Extent1].[ValuationDateID])
AND (20130314 = [Extent2].[ValuationDateID])



Why your query builds sub-selects when objects can be JOINed to directly?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
SpinyNorman
SpinyNorman
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 37
I'm not sure why the sub-selects are being generated, but removing them doesn't make a difference. It's almost as if the query is trying to use the filtered index when we specify an inner join, but not for a left join, and using this index for the query results in the error.

The reason I say this is that the inner join works if we create a more appropriate index, which is then used instead. Here is the new index I created:

CREATE NONCLUSTERED INDEX test
ON [Reporting].[fctSwapDualShift] ([ValuationDateID])
INCLUDE ([BookID],[TransactionID],[ParallelShiftID],[SwapTenorID],[Value])



Any ideas why this is happening?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27374 Visits: 17557
Eugene Elutin (3/15/2013)

Why your query builds sub-selects when objects can be JOINed to directly?


That is what Entity Frameworks seems to do most of the time. It generates hugely complicated queries with mountains of sub-selects.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5248 Visits: 5478
SpinyNorman (3/15/2013)
I'm not sure why the sub-selects are being generated, but removing them doesn't make a difference. It's almost as if the query is trying to use the filtered index when we specify an inner join, but not for a left join, and using this index for the query results in the error.

The reason I say this is that the inner join works if we create a more appropriate index, which is then used instead. Here is the new index I created:

CREATE NONCLUSTERED INDEX test
ON [Reporting].[fctSwapDualShift] ([ValuationDateID])
INCLUDE ([BookID],[TransactionID],[ParallelShiftID],[SwapTenorID],[Value])



Any ideas why this is happening?


Didn't you have the same discussion few months ago?
http://www.sqlservercentral.com/Forums/Topic1369717-391-1.aspx

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search