Non performant list of templates for a provisioning

[Ladas]

kbrock so this originates in templates list, when I select 1 templates and choose provision VM.

With 70k templates this was doing 70k SQL queries, so it took like 8 minutes to load the VM provision workflow page.

But for 70k images, each :include takes about 6s to load, which results in like 30s and then create_hash_struct_from_vm_or_template take 1ms, so 70s for 70k templates.

So this still takes about 1.5 minute to load.

---

So it's pretty bad we need to load 70k AR object + N*70k included objects. Just to verify if 1 template is allowed. :-) So a bigger rewrite will be needed in this area I suppose. :-)

---

if I go to provision from instance list, that is even worse, several n+1 here, that goes also through genealogy, so we probably can't :include that. This repeats for every template:

[----] D, [2017-02-09T11:10:03.592819 #19499:28dda54] DEBUG -- :   CACHE (0.0ms)  SELECT  "ext_management_systems".* FROM "ext_management_systems" WHERE "ext_management_systems"."id" = $1 LIMIT $2  [["id", 21], ["LIMIT", 1]]
[----] D, [2017-02-09T11:10:03.593971 #19499:28dda54] DEBUG -- :   ExtManagementSystem Inst Including Associations (0.3ms - 1rows)
[----] D, [2017-02-09T11:10:03.595458 #19499:28dda54] DEBUG -- :    (0.3ms)  SELECT COUNT(*) FROM "snapshots" WHERE "snapshots"."vm_or_template_id" = $1  [["vm_or_template_id", 862560]]
[----] D, [2017-02-09T11:10:03.596337 #19499:28dda54] DEBUG -- :   Query Trace > lib/extensions/ar_virtual.rb:741:in `calculate'
     from lib/extensions/virtual_total.rb:74:in `block in define_virtual_aggregate_method'
[----] D, [2017-02-09T11:10:03.597790 #19499:28dda54] DEBUG -- :   OperatingSystem Load (0.3ms)  SELECT  "operating_systems".* FROM "operating_systems" WHERE "operating_systems"."vm_or_template_id" = $1 LIMIT $2  [["vm_or_template_id", 886889], ["LIMIT", 1]]
[----] D, [2017-02-09T11:10:03.598921 #19499:28dda54] DEBUG -- :   OperatingSystem Inst Including Associations (0.0ms - 0rows)
[----] D, [2017-02-09T11:10:03.599937 #19499:28dda54] DEBUG -- :   Hardware Load (0.3ms)  SELECT  "hardwares".* FROM "hardwares" WHERE "hardwares"."vm_or_template_id" = $1 LIMIT $2  [["vm_or_template_id", 886889], ["LIMIT", 1]]
[----] D, [2017-02-09T11:10:03.600880 #19499:28dda54] DEBUG -- :   Query Trace > app/models/operating_system.rb:108:in `image_name'
     from app/models/operating_system.rb:126:in `platform'
     from app/models/vm_or_template.rb:511:in `platform'
[----] D, [2017-02-09T11:10:03.601211 #19499:28dda54] DEBUG -- :   Hardware Inst Including Associations (0.1ms - 1rows)
[----] D, [2017-02-09T11:10:03.603774 #19499:28dda54] DEBUG -- :    (0.2ms)  SELECT SUM("disks"."size") FROM "disks" WHERE "disks"."hardware_id" = $1  [["hardware_id", 824035]]
[----] D, [2017-02-09T11:10:03.604679 #19499:28dda54] DEBUG -- :   Query Trace > lib/extensions/ar_virtual.rb:741:in `calculate'
     from lib/extensions/virtual_total.rb:86:in `block in define_virtual_aggregate_method'
     from lib/extensions/ar_virtual.rb:93:in `allocated_disk_storage'

but if I remember correctly, this doesn't have any search and will try to print me all 70k templates on 1 page?

---

also it's strange that the each template list has a different code

• templates under provider - fast (either explorer or link under provider)
• allowed templates when provisioning from templates list - slow (like 1.5 minute after this PR)
• allowed templates when provisioning from Vm list - super slow (like at least 15 minutes)
  • not sure what happens when I pick template from this list

[kbrock]

At a high level, If the collection (instead of each template) were passed into create_hash_struct_from_vm_or_template, then the MiqPreload could be moved in there as well. The preload can be converted to using select with virtual and the number of queries/data downloaded would be reduced significantly. See [comment ref] for more details.

well in theory that is

[miq-bot]

This issue has been automatically marked as stale because it has not been updated for at least 6 months.

If you can still reproduce this issue on the current release or on master, please reply with all of the information you have about it in order to keep the issue open.

Thank you for all your contributions!

[JPrause]

@Ladas is this still a valid issue. If not can you close.
If there's no update by next week, I'll be closing this issue.

[Ladas]

I believe @lpichler fixed this a long time ago