My new View query is taking long for execution as compared to the actual table query.

Question

My new View query is taking long for execution as compared to the actual table query.

Mahesh Kharawatkar 0

The normal query which is running fine within10 seconds.

SELECT productUri.AttributeValue AS ProductUri, orderUri.AttributeValue AS OrderUri, COUNT (*) OVER () AS TotalResultsCount, productUri.GroupId AS GroupId FROM OrdersLookup AS productUri INNER JOIN OrdersLookup AS orderUri ON orderUri.GroupId=productUri.GroupId AND orderUri.Attribute='OrderUri' INNER JOIN OrdersLookup AS isRootInOrder ON isRootInOrder.GroupId=productUri.GroupId AND isRootInOrder.Attribute='IsRootInOrder' INNER JOIN OrdersLookup AS alternateIdentifier ON alternateIdentifier.GroupId=productUri.GroupId AND alternateIdentifier.Attribute='AlternateIdentifier' INNER JOIN OrdersLookup AS collectionTicketReference ON collectionTicketReference.GroupId=productUri.GroupId AND collectionTicketReference.Attribute='CollectionTicketReference' LEFT JOIN OrdersLookup AS fareCategoryClassification ON fareCategoryClassification.GroupId=productUri.GroupId AND fareCategoryClassification.Attribute='FareCategoryClassification' INNER JOIN OrdersLookup AS departureDateUtc ON departureDateUtc.GroupId=productUri.GroupId AND departureDateUtc.Attribute='DepartureDateUtc' WHERE productUri.Attribute = 'ProductUri' AND (isRootInOrder.AttributeValue = 'True' AND (alternateIdentifier.AttributeValue = 'two' OR alternateIdentifier.AttributeValue = 'four' AND collectionTicketReference.AttributeValue = '356d6e9c-2681-47a8-9863-94dc27e1e583') AND (fareCategoryClassification.AttributeValue IS NULL OR fareCategoryClassification.AttributeValue<>'season')) GROUP BY productUri.AttributeValue, orderUri.AttributeValue, productUri.GroupId ORDER BY MIN(departureDateUtc.AttributeValue) ASC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

My New query with View which is taking more than 5 minutes for execution.

SELECT productUri.AttributeValue AS ProductUri, orderUri.AttributeValue AS OrderUri, COUNT (*) OVER () AS TotalResultsCount, productUri.GroupId AS GroupId
FROM v_OrdersLookup_New AS productUri
INNER JOIN v_OrdersLookup_New AS orderUri ON orderUri.GroupId=productUri.GroupId AND orderUri.Attribute='OrderUri'
INNER JOIN v_OrdersLookup_New AS isRootInOrder ON isRootInOrder.GroupId=productUri.GroupId AND isRootInOrder.Attribute='IsRootInOrder'
INNER JOIN v_OrdersLookup_New AS alternateIdentifier ON alternateIdentifier.GroupId=productUri.GroupId AND alternateIdentifier.Attribute='AlternateIdentifier'
INNER JOIN v_OrdersLookup_New AS collectionTicketReference ON collectionTicketReference.GroupId=productUri.GroupId AND collectionTicketReference.Attribute='CollectionTicketReference'
LEFT JOIN v_OrdersLookup_New AS fareCategoryClassification ON fareCategoryClassification.GroupId=productUri.GroupId AND fareCategoryClassification.Attribute='FareCategoryClassification'
INNER JOIN v_OrdersLookup_New AS departureDateUtc ON departureDateUtc.GroupId=productUri.GroupId AND departureDateUtc.Attribute='DepartureDateUtc'
WHERE productUri.Attribute = 'ProductUri'
AND (isRootInOrder.AttributeValue = 'True' AND (alternateIdentifier.AttributeValue = 'two' OR alternateIdentifier.AttributeValue = 'four' AND collectionTicketReference.AttributeValue = '46184dba-134d-4586-b1f6-5a84229674d1') AND (fareCategoryClassification.AttributeValue IS NULL OR fareCategoryClassification.AttributeValue<>'season'))
GROUP BY productUri.AttributeValue, orderUri.AttributeValue, productUri.GroupId
ORDER BY MIN(departureDateUtc.AttributeValue) ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

My View structure is as below.

Create view [dbo].[v_Orderslookup_New] as
SELECT  [GroupId]
      ,[Attribute]
      ,[AttributeValue]
  FROM [c22_OrderSearchService].[dbo].[Orderslookup]
  union all
  SELECT  [GroupId]
      ,[Attribute]
      ,[AttributeValue]
  FROM [c22_OrderSearchService].[dbo].[Orderslookup_New]
GO

The execution plan has HAsh Match and multiple concatenations. The estimation and actual row value is differing may be update statistics will help. however any other suggestions considering the view structure and the query written. When I used union in the view the query time reduced to 50 seconds but still 50 seconds is a huge time. execution_Plan

2 answers

Your answer

Answer 1

I get the impression that you have a so-called EAV design. EAV = Entity-AttributeValue.

Such designs have their place, as they permit a flexible schemas, and new attributes can easily be added.

However, they are not always the right choice. In many cases, it is better to make a "real" table design where the attribute are columns and not rows. This means that a misspelling of an attribute will yield an error and not just no row being returned. And moreover, EAV often leads to poor performance, because of all the joins you need to make.

So I would suggest that this performance issue is a wakeup call that it's time to make a redesign.

Answer 2

LiHongMSFT-4306 31,566

Hi @Mahesh Kharawatkar

The normal query which is running fine within10 seconds. My New query with View which is taking more than 5 minutes for execution.

Then why you use view as source dataset?

How about use temporary table instead of view?

Best regards,

Cosmog Hong

Mahesh Kharawatkar 0 Reputation points

2023-03-30T12:46:17.3833333+00:00

Hi CosmogHong-MSFT,

The table size of Orderslookup is very huge, We were facing high cpu consumption whie performing update stats on that table, so to resolve it, we have created 2 separate tables Orderslookup and Orderslookup_New on different filegroups. the new table is present on a new separate drive. to read the data from both old and new table we have created this view.
Mahesh Kharawatkar 0 Reputation points

2023-03-30T12:57:45.9+00:00

I have already tried creating a temp table but the query I shared is designed through an app and I can't modify the code of the source query so wanted an option to tune this View. I can't even create the index on this view as there is an union operator used.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-03-30T21:14:55.4633333+00:00

The table size of Orderslookup is very huge

How many rows?

We were facing high cpu consumption whie performing update stats on that table, so to resolve it, we have created 2 separate tables Orderslookup and Orderslookup_New on different filegroups.

That's what they call adding insult to injury. You could possibly get this to work, if you set up CHECK constraints on the underlying tables to make the view a proper partitioned view.

What is the table and index definition of the underlying tables?

Although, as I said in my previous post, I think you are in for a major redesign. Your current one simply is not tenable.
Mahesh Kharawatkar 0 Reputation points

2023-04-04T12:22:15.44+00:00

How many rows? Rows count is 10672554784 (ten billion) The index definition on both tables is as below.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-04T21:15:37.82+00:00
That only gives the name of the indexes. Not which columns that are included. Not that I am sure that I care. As I've said a few times before: this table screams for a redesign. Ten milliard rows certainly counts as many. With a proper design, with one column per attribute, the number of rows will be fewer: But, yes, they will be wider. Rather than doing these multi-joins, you could try using a pivot instead. Here is a draft for a CTE which you could refine further and use in your query. But don't be surprised if performance is still bad.

; WITH pivoted AS ( SELECT GroupID, MIN(CASE Attribute WHEN 'ProductUri' THEN AttributeValue END) AS ProductUri, MIN(CASE Attribute WHEN 'OrderUri' THEN AttributeValue END) AS OrderUri, MIN(CASE Attribute WHEN 'IsRootInOrder' THEN AttributeValue END) AS IsRootInOrder, ... FROM v_OrdersLookup_New GROUP BY GroupID )

Mahesh Kharawatkar 0

Thank you Erland Sommarskog for the valuable suggestion.

Below the index structures which is same for old and new table.

CREATE NONCLUSTERED INDEX [IX_OrdersLookup_Attribute_AttributeValue-GroupId_New] ON [dbo].[OrdersLookup_New]
(
	[Attribute] ASC,
	[AttributeValue] ASC
)
INCLUDE ( 	[GroupId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [OrdersLookup_ROW_DATA]
GO



ALTER TABLE [dbo].[OrdersLookup_New] ADD  CONSTRAINT [PK_OrdersLookup_New] PRIMARY KEY CLUSTERED 
(
	[GroupId] ASC,
	[Attribute] ASC,
	[AttributeValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [OrdersLookup_ROW_DATA]
GO

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-05T21:24:13.72+00:00

So you are saying that for the same GroupId and Attribute, there can be multiple values? This is getting even worse than I thought it was. OK, you know your data, and I don't. But I must say that I get an unpleasant feeling when I see the above.

I maintain my opinion that you need to make a major redesign.

Share via

My new View query is taking long for execution as compared to the actual table query.

2 answers

Your answer