My first call today had to do with a report that was working, but took 20 minutes to complete.\u00a0 Other similar reports ran in a few seconds so I was asked to find the difference.\u00a0 I noticed several added tables and a different table configuration which were likely places to start.\u00a0 But the one difference that looked most promising was the number of join lines between the tables.\u00a0 In the reports that ran quickly, every pair of tables had one additional join line between them.\u00a0 And in all cases the field being joined was called SystemID.<\/p>\n
Apparently, this software package allows you to maintain the data for several completely independent ‘systems’ in the same database.\u00a0 This SystemID identifies the system of each record. The customer explained that since they only use one ‘system’ they didn’t think the link on SystemID was essential. And the report did seem to generate accurate data without the SystemID link, if you didn’t mind the wait.<\/p>\n
I explained that the indexes that facilitate the joins between tables were all probably created with the SystemID as the primary key. So any join without a SystemId value couldn’t tap into the index.\u00a0 Without the help of an index, the database would have to do a ‘serial read’ (record by record) to find the matching records.\u00a0 Think of finding a topic in a book without an index.<\/p>\n
So we added the extra line for SystemID between every pair of tables and tested the report.\u00a0 The report ran in about 8 seconds. The lesson here is that even links that seem redundant can make a significant difference in the performance.<\/p>\n","protected":false},"excerpt":{"rendered":"
My first call today had to do with a report that was working, but took 20 minutes to complete.\u00a0 Other similar reports ran in a few seconds so I was asked to find the difference.\u00a0 I noticed several added tables and a different table configuration which were likely places to start.\u00a0 But the one difference […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-2653","post","type-post","status-publish","format-standard","hentry","category-tips"],"_links":{"self":[{"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/posts\/2653","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/comments?post=2653"}],"version-history":[{"count":0,"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/posts\/2653\/revisions"}],"wp:attachment":[{"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/media?parent=2653"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/categories?post=2653"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kenhamady.com\/cru\/wp-json\/wp\/v2\/tags?post=2653"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}