More database index for the mdb-database

Idea created by Andreas_Eriksson on Feb 9, 2018
    Currently Planned
    Score7
    • Andreas_Eriksson
    • Robert_Forsman
    • martin.hermansson
    • Chi_Chen
    • PerttiP
    • hikonen
    • FredrikRonning

    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.

     

    Example:

     

    Query 1. 600.000 to 7 million readings. 2- to 30 seconds in execution time, depending on data.

    declare @p1 int
    set @p1=1073742491
    declare @p2 int
    set @p2=180150841
    declare @p7 int
    set @p7=-1
    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
    set @p1=NULL
    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'
    select p16459

     

    Regards,

    Andreas Eriksson

    Netgain AB