QAD BI v3 - Tech Level 2 Certification - Part 4 > Troubleshooting Performance > Performance Issue Troubleshooting
  PPT
Performance Issue Troubleshooting
Performance Issue Troubleshooting
Performance Issue Troubleshooting
Performance Issue Troubleshooting
 
While looking at the procedure, it’s clear that it does more than just insert data into the snapshot tables. First it does two deletes before inserting the data into the snapshot tables. It’s basically removing data already in the fact table that exists also in two staging tables prior to inserting the data into the fact tables from one of those staging tables.
Performance Issue Troubleshooting
Performance Issue Troubleshooting
 
Declare the time variable near where all the other DECLARE columns occurred.
Before and after each of the three SQL deletes/inserts, add variable definitions and the EXEC WSWrkAudit to send audit messages to the Audit Log portion of the Scheduler.
Performance Issue Troubleshooting
 
Insert the various audit information into the procedure before, between and after the three SQL parts and recompile the procedure.
Performance Issue Troubleshooting
 
After running the job via the scheduler, you can now see which portions of the job took the longest. In this example, it’s clear that the biggest time gap during the first delete which finished 11 minutes after the prior audit log message. That’s where the biggest amount of processing time is taking and will be the focus of the research.
Performance Issue Troubleshooting
Performance Issue Troubleshooting
 
Indexes that are referenced only partially will only use the columns in the sequence they are defined in the index up to the point that there is a gap in columns referenced.
Performance Issue Troubleshooting
 
Index is being called properly. What else could be slowing things down?
Performance Issue Troubleshooting
Performance Issue Troubleshooting
 
The count is the same for all three queries, but something is being overlooked.
Performance Issue Troubleshooting
 
You can imagine what would happen if there are tens of thousands of records in the stage table and the fact table in this comparison. Potentially millions of records would be matched unnecessarily, taking up resources before the actual delete occurs.
Performance Issue Troubleshooting
 
Streamline the query to ensure the matches are unique.
Performance Issue Troubleshooting