Bug 1399765

In instances where we have a large # of Consumers and Entitlements there are SELECT statements generated during a GET API request for a Consumer that can execute in 500ms or more.

When you have an environment with large numbers of clients checking in this can result in poor performance as the Satellite will spend all of its resources running these SELECT calls which can cause UI and other performance problems.

The query is as follows (excuse the length):

2016-11-29 08:09:59 EST LOG:  duration: 522.412 ms  execute <unnamed>: select entitlemen0_.consumer_id as consumer8_8_12_, entitlemen0_.id as id1_23_12_, entitlemen0_.id as id1_23_11_, entitlemen0_.created as created2_23_11_, entitlemen0_.updated as updated3_23_11_, entitlemen0_.consumer_id as consumer8_23_11_, entitlemen0_.dirty as dirty4_23_11_, entitlemen0_.endDateOverride as endDateO5_23_11_, entitlemen0_.owner_id as owner_id9_23_11_, entitlemen0_.pool_id as pool_id10_23_11_, entitlemen0_.quantity as quantity6_23_11_, entitlemen0_.updatedOnStart as updatedO7_23_11_, owner1_.id as id1_34_0_, owner1_.created as created2_34_0_, owner1_.updated as updated3_34_0_, owner1_.autobind_disabled as autobind4_34_0_, owner1_.contentPrefix as contentP5_34_0_, owner1_.defaultServiceLevel as defaultS6_34_0_, owner1_.displayName as displayN7_34_0_, owner1_.account as account8_34_0_, owner1_.logLevel as logLevel9_34_0_, owner1_.parent_owner as parent_10_34_0_, owner1_.upstream_id as upstrea11_34_0_, owner2_.id as id1_34_1_, owner2_.created as created2_34_1_, owner2_.updated as updated3_34_1_, owner2_.autobind_disabled as autobind4_34_1_, owner2_.contentPrefix as contentP5_34_1_, owner2_.defaultServiceLevel as defaultS6_34_1_, owner2_.displayName as displayN7_34_1_, owner2_.account as account8_34_1_, owner2_.logLevel as logLevel9_34_1_, owner2_.parent_owner as parent_10_34_1_, owner2_.upstream_id as upstrea11_34_1_, upstreamco3_.id as id1_56_2_, upstreamco3_.created as created2_56_2_, upstreamco3_.updated as updated3_56_2_, upstreamco3_.consumer_idcert_id as consumer9_56_2_, upstreamco3_.name as name4_56_2_, upstreamco3_.owner_id as owner_id5_56_2_, upstreamco3_.prefix_url_api as prefix_u6_56_2_, upstreamco3_.prefix_url_web as prefix_u7_56_2_, upstreamco3_.type_id as type_id10_56_2_, upstreamco3_.uuid as uuid8_56_2_, identityce4_.id as id1_28_3_, identityce4_.created as created2_28_3_, identityce4_.updated as updated3_28_3_, identityce4_.cert as cert4_28_3_, identityce4_.privatekey as privatek5_28_3_, identityce4_.serial_id as serial_i6_28_3_, certificat5_.id as id1_6_4_, certificat5_.created as created2_6_4_, certificat5_.updated as updated3_6_4_, certificat5_.collected as collecte4_6_4_, certificat5_.expiration as expirati5_6_4_, (CASE ((SELECT count(entcert.id) FROM cp_ent_certificate entcert where entcert.serial_id = certificat5_.id) + (SELECT count(cdncert.id) FROM cp_cdn_certificate cdncert where cdncert.serial_id = certificat5_.id) + (SELECT count(subcert.id) FROM cp_certificate subcert where subcert.serial_id = certificat5_.id) + (SELECT count(idcert.id) FROM cp_id_cert idcert where idcert.serial_id = certificat5_.id)) WHEN 0 THEN 1 ELSE 0 END) as formula4_4_, consumerty6_.id as id1_15_5_, consumerty6_.created as created2_15_5_, consumerty6_.updated as updated3_15_5_, consumerty6_.label as label4_15_5_, consumerty6_.manifest as manifest5_15_5_, pool7_.id as id1_36_6_, pool7_.created as created2_36_6_, pool7_.updated as updated3_36_6_, pool7_.accountNumber as accountN4_36_6_, pool7_.activeSubscription as activeSu5_36_6_, pool7_.contractNumber as contract6_36_6_, pool7_.derivedProductId as derivedP7_36_6_, pool7_.derivedProductName as derivedP8_36_6_, pool7_.endDate as endDate9_36_6_, pool7_.orderNumber as orderNu10_36_6_, pool7_.owner_id as owner_i18_36_6_, pool7_.productId as product11_36_6_, pool7_.productName as product12_36_6_, pool7_.quantity as quantit13_36_6_, pool7_.restrictedToUsername as restric14_36_6_, pool7_.sourceEntitlement_id as sourceE19_36_6_, pool7_.startDate as startDa15_36_6_, pool7_.type as type16_36_6_, pool7_.version as version17_36_6_, (select sum(ent.quantity) from cp_entitlement ent where ent.pool_id = pool7_.id) as formula2_6_, (select sum(ent.quantity) from cp_entitlement ent, cp_consumer cons, cp_consumer_type ctype where ent.pool_id = pool7_.id and ent.consumer_id = cons.id and cons.type_id = ctype.id and ctype.manifest = 'Y') as formula3_6_, owner8_.id as id1_34_7_, owner8_.created as created2_34_7_, owner8_.updated as updated3_34_7_, owner8_.autobind_disabled as autobind4_34_7_, owner8_.contentPrefix as contentP5_34_7_, owner8_.defaultServiceLevel as defaultS6_34_7_, owner8_.displayName as displayN7_34_7_, owner8_.account as account8_34_7_, owner8_.logLevel as logLevel9_34_7_, owner8_.parent_owner as parent_10_34_7_, owner8_.upstream_id as upstrea11_34_7_, entitlemen9_.id as id1_23_8_, entitlemen9_.created as created2_23_8_, entitlemen9_.updated as updated3_23_8_, entitlemen9_.consumer_id as consumer8_23_8_, entitlemen9_.dirty as dirty4_23_8_, entitlemen9_.endDateOverride as endDateO5_23_8_, entitlemen9_.owner_id as owner_id9_23_8_, entitlemen9_.pool_id as pool_id10_23_8_, entitlemen9_.quantity as quantity6_23_8_, entitlemen9_.updatedOnStart as updatedO7_23_8_, sourcestac10_.id as id1_40_9_, sourcestac10_.created as created2_40_9_, sourcestac10_.updated as updated3_40_9_, sourcestac10_.derivedPool_id as derivedP5_40_9_, sourcestac10_.sourceConsumer_id as sourceCo6_40_9_, sourcestac10_.sourceStackId as sourceSt4_40_9_, sourcesubs11_.id as id1_41_10_, sourcesubs11_.created as created2_41_10_, sourcesubs11_.updated as updated3_41_10_, sourcesubs11_.pool_id as pool_id6_41_10_, sourcesubs11_.subscriptionId as subscrip4_41_10_, sourcesubs11_.subscriptionSubKey as subscrip5_41_10_ from cp_entitlement entitlemen0_ inner join cp_owner owner1_ on entitlemen0_.owner_id=owner1_.id left outer join cp_owner owner2_ on owner1_.parent_owner=owner2_.id left outer join cp_upstream_consumer upstreamco3_ on owner2_.upstream_id=upstreamco3_.id left outer join cp_id_cert identityce4_ on upstreamco3_.consumer_idcert_id=identityce4_.id left outer join cp_cert_serial certificat5_ on identityce4_.serial_id=certificat5_.id left outer join cp_consumer_type consumerty6_ on upstreamco3_.type_id=consumerty6_.id left outer join cp_pool pool7_ on entitlemen0_.pool_id=pool7_.id left outer join cp_owner owner8_ on pool7_.owner_id=owner8_.id left outer join cp_entitlement entitlemen9_ on pool7_.sourceEntitlement_id=entitlemen9_.id left outer join cp_pool_source_stack sourcestac10_ on pool7_.id=sourcestac10_.derivedPool_id left outer join cp_pool_source_sub sourcesubs11_ on pool7_.id=sourcesubs11_.pool_id where entitlemen0_.consumer_id=$1
2016-11-29 08:09:59 EST DETAIL:  parameters: $1 = '8a5e669e5549e0e701554a2f61741e9a'