QAD BI v3 - Tech Level 2 Certification - Part 4 > Performance Tuning > Execution Plans – Sample Scenario
  PPT
Execution Plans – Sample Scenario
 
A job in purchasing was taking quite a long time to load for a fairly small record set. It was taking around 45 minutes to run a job for 28 thousand records. An execution plan was run on the query, and on the function call in the query. What you see here is part of the execution plan for the function call. The call to the perm table was one of the biggest resource offenders and we’re supposed to have indexes on perm tables. Instead of referencing indexes though, a Full Table Scan is occurring on that perm table.
Execution Plans – Sample Scenario
Execution Plans – Sample Scenario
 
Create an index using the elements that make up the business key. These also tied to the WHERE clause in the function that is being called.
Execution Plans – Sample Scenario
 
After the indexes were put on the perm_shop_cal table, the performance of the job for 28 thousand records improved from 44 minutes to 4 minutes. However, it looks like the index was still not being properly used. A Table Scan is being done on that table for 28% of the resources. Why aren’t the indexes being utilized?
Execution Plans – Sample Scenario
 
Looking at the node in the execution plan that referenced this table, we can see that all of our joins are doing a CONVERT_IMPLICIT on each of the character type column.
Execution Plans – Sample Scenario
 
Reviewing the function call that this execution plan was run for, we can see that when it was written, it was created with varchar inputs instead of nvarchar inputs (either because it was written prior to us converting all of our character columns to nvarchar or as a simple oversight). Because the values being passed in were nvarchar, each join parameter value had to be converted to nvarchar. As a result, the index on the table was not being utilized.
Execution Plans – Sample Scenario
 
The improvement in calling the query in part was due to it properly using the index now. Before, when the nvarchar vs. varchar issue existed, the implicit conversion of the varchar column to nvarchar was causing the query to not use the index.
Execution Plans – Sample Scenario
 
Examining the node again shows that the CONVERT_IMPLICIT are no longer there.