I know im spamming this thread, but i really need help, and i would like to add as much information as i can.
http://i.imgur.com/cjHynO0.png
This is the database over the past month. I upgraded it right AFTER it increased in avarage DTU.
before update this method is running alot:
INSERT INTO #FilterableSpecs ([ProductId], [SpecificationAttributeOptionId]) SELECT DISTINCT [psam].ProductId, [psam].SpecificationAttributeOptionId FROM [Product_SpecificationAttribute_Mapping] [psam] with (NOLOCK) WHERE [psam].[ProductId] IN (SELECT [pi].ProductId FROM #PageIndex [pi])
after upgradering this method running alot more than the previous one:
INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId])
SELECT DISTINCT [psam].SpecificationAttributeOptionId
FROM [Product_SpecificationAttribute_Mapping] [psam] WITH (NOLOCK)
WHERE [psam].[AllowFiltering] = 1
AND [psam].[ProductId] IN (
SELECT p.Id
FROM
Product p with (NOLOCK)
LEFT JOIN Product_Category_Mapping pcm with (NOLOCK)
ON p.Id = pcm.ProductId
WHERE
p.Deleted = 0
AND pcm.CategoryId IN (SELECT CategoryId FROM #FilteredCategoryIds)
AND pcm.IsFeaturedProduct = 0
AND p.VisibleIndividually = 1
AND p.Published = 1
AND p.Deleted = 0
AND (getutcdate() BETWEEN ISNULL(p.AvailableStartDateTimeUtc, '1/1/1900') and ISNULL(p.AvailableEndDateTimeUtc, '1/1/2999'))
AND (p.LimitedToStores = 0 OR EXISTS (
SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
WHERE [sm].EntityId = p.Id AND [sm].EntityName = 'Product' and [sm].StoreId=1
)))