Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Inner join vs Left join Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 2:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 17, 2013 11:17 PM
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?
Post #1431410
Posted Friday, March 15, 2013 3:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 2,857, Visits: 5,129
You have posted incomplete statement...


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

How to post your question to get the best and quick help
Post #1431428
Posted Friday, March 15, 2013 4:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 17, 2013 11:17 PM
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.
Post #1431433
Posted Friday, March 15, 2013 4:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 2,857, Visits: 5,129
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1431437
Posted Friday, March 15, 2013 4:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 17, 2013 11:17 PM
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?
Post #1431441
Posted Friday, March 15, 2013 5:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 2,857, Visits: 5,129
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1431463
Posted Friday, March 15, 2013 7:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 17, 2013 11:17 PM
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?
Post #1431526
Posted Friday, March 15, 2013 8:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 12,995, Visits: 12,414
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 Moden's 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)
Post #1431553
Posted Friday, March 15, 2013 8:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 2,857, Visits: 5,129
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1431560
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse