It would be great if CA can add more index for the mdb-database in future releases of the Service Catalog. We can see at least three queries that are very heavy. This was seen in old the 12.8 release with plenty of data in the database. I am unsure if this problem has already been adressed in the 17.1 relase. Thanks beforehand.
Query 1. 600.000 to 7 million readings. 2- to 30 seconds in execution time, depending on data.
declare @p1 int
declare @p2 int
declare @p7 int
exec sp_cursorprepexec @p1 output,@p2 output,N'@P0 int',N'select sd3.*, f.form_elem_value as effective_form_field_value from usm_subscription_detail sd, usm_rate_definition rd, usm_subscription_detail sd2, usm_request_item_form f, usm_subscription_detail sd3 where sd.request_id = @P0 and rd.item_id = sd.item_id and rd.rate_plan_id = sd.rate_plan_id and rd.item_type = 3 and rd.enum_5 = 7 and rd.text_2 is not null and sd2.request_id = sd.request_id and sd2.offering_id = sd.offering_id and sd2.rate_plan_id = sd.rate_plan_id and sd2.group_id = sd.group_id and sd2.sd_row = sd.sd_row and sd2.subscription_type = 5 and sd2.text_1 = rd.enum_15 and sd2.id = f.subscription_detail_id and sd.offering_id = sd3.offering_id and sd.rate_plan_id = sd3.rate_plan_id and sd.group_id = sd3.group_id and sd.sd_row = sd3.sd_row and rd.text_2 + ''_fdms$$'' collate database_default = f.form_elem_name collate database_default ',4112,8193,@p7 output,594455
select @p1, @p2, @p7
Query 2. 240.000 to 340.000 readings, 2- to 5 seconds in execution time. Run several times each second.
declare @p1 int
exec sp_prepexec @p1 output,NULL,N'SELECT "Tbl1030"."subscription_detail_id" "Col1207","Tbl1030"."form_elem_value" "Col1209" FROM "mdb"."dbo"."usm_request_item_form" "Tbl1030" WITH (NOLOCK) WHERE "Tbl1030"."form_elem_name"=N''need_by_date_co'' ORDER BY "Col1207" ASC'