March 15, 2013 at 2:59 am
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?
March 15, 2013 at 3:48 am
You have posted incomplete statement...
March 15, 2013 at 4:01 am
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.
March 15, 2013 at 4:16 am
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
March 15, 2013 at 4:31 am
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?
March 15, 2013 at 5:22 am
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?
March 15, 2013 at 7:28 am
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?
March 15, 2013 at 8:12 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 15, 2013 at 8:18 am
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 testON [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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply