We have upgraded our project based on Tridion 2013 and .Net DD4T 2 to SDL Web 8.5 and DD4T 2.2. We are using CIL and micro services to get data from broker. Once 10-15 concurrent user started browsing the website, the database CPU reached to 100%. We have verified that a particular type of query running on the broker database is taking time to execute and also increasing CPU. Here is the query:
(@P0 int,@P1 int,@P2 int,@P3 int,@P4 int,@P5 nvarchar(4000),@P6 float,@P7 int,@P8 int,@P9 nvarchar(4000))select distinct TOP(@P0)
itemmeta0_.PUBLICATION_ID as col_0_0_, itemmeta0_.ITEM_REFERENCE_ID as col_1_0_,
itemmeta0_.ITEM_TYPE as col_2_0_, itemmeta0_.LAST_PUBLISHED_DATE as col_3_0_
from ITEMS itemmeta0_ cross join
ITEM_CATEGORIES_AND_KEYWORDS relatedkey1_ cross join
TAXFACETS taxonomyit2_ cross join
ITEM_CATEGORIES_AND_KEYWORDS relatedkey3_ cross join
TAXFACETS taxonomyit4_ cross join COMPONENT componentm5_ inner join
ITEMS componentm5_1_ on
componentm5_.PUBLICATION_ID=componentm5_1_.PUBLICATION_ID and
componentm5_.NAMESPACE_ID=componentm5_1_.NAMESPACE_ID and
componentm5_.ITEM_REFERENCE_ID=componentm5_1_.ITEM_REFERENCE_ID cross join
CUSTOM_META custommeta6_ where itemmeta0_.PUBLICATION_ID=@P1 and
itemmeta0_.ITEM_REFERENCE_ID=componentm5_.ITEM_REFERENCE_ID and
itemmeta0_.PUBLICATION_ID=componentm5_.PUBLICATION_ID and
componentm5_.SCHEMA_ID=@P2 and
itemmeta0_.ITEM_REFERENCE_ID=relatedkey1_.ITEM_REFERENCE_ID and
itemmeta0_.PUBLICATION_ID=relatedkey1_.PUBLICATION_ID and
relatedkey1_.KEYWORD_ID=taxonomyit2_.FACET_ID and
relatedkey1_.PUBLICATION_ID=taxonomyit2_.PUBLICATION_ID and
relatedkey1_.PUBLICATION_ID=@P3 and taxonomyit2_.TAXONOMY_ID=@P4 and
taxonomyit2_.FACET_KEY=@P5 and
itemmeta0_.PUBLICATION_ID=custommeta6_.PUBLICATION_ID and
itemmeta0_.ITEM_REFERENCE_ID=custommeta6_.ITEM_ID and
itemmeta0_.ITEM_TYPE=custommeta6_.ITEM_TYPE and
custommeta6_.KEY_FLOAT_VALUE=@P6 and
itemmeta0_.ITEM_REFERENCE_ID=relatedkey3_.ITEM_REFERENCE_ID and
itemmeta0_.PUBLICATION_ID=relatedkey3_.PUBLICATION_ID and
relatedkey3_.KEYWORD_ID=taxonomyit4_.FACET_ID and
relatedkey3_.PUBLICATION_ID=taxonomyit4_.PUBLICATION_ID and
relatedkey3_.PUBLICATION_ID=@P7 and taxonomyit4_.TAXONOMY_ID=@P8 and
taxonomyit4_.FACET_KEY=@P9 order by itemmeta0_.LAST_PUBLISHED_DATE DESC
I believe application is generating this query while executing broker query but while upgrading the project we haven't changed anything in the code. When we run the same page in old environment it is generating some different query and that is smaller and faster in execution on DB server. I think CIL/CIS is generating this query which seems to be non optimized. Can anyone share if faced similar issue earlier?
