| Issue Number | 4304 |
|---|---|
| Summary | Identify SQL Query Times |
| Created | 2017-08-24 12:32:52 |
| Issue Type | Task |
| Submitted By | Englisch, Volker (NIH/NCI) [C] |
| Assigned To | Englisch, Volker (NIH/NCI) [C] |
| Status | Closed |
| Resolved | 2017-09-06 15:50:53 |
| Resolution | Fixed |
| Path | /home/bkline/backups/jira/ocecdr/issue.213410 |
Bryan would like us to look into optimizing our publishing job SQL queries. As a first step we want to comb through the log files to identify how long the queries take and if the performance has changed significantly over time as we have seen for one query of the nightly publishing job.
I've looked through our log files to identify the times it takes for the SQL queries to finish for both, the nightly as well as the weekly publishing job. All but a few queries take no more than 2 seconds on PROD. These are the times for the few SQL queries taking longer than 2 seconds:
18 sec: CTGovProtocol (nightly job)
7 sec: Media (nightly job)
12 sec: Summary (nightly job)
6 sec: Media (weekend job)
The complete list is available in Collaborate:
https://collaborate.nci.nih.gov/display/OCECTBWIKI/CDR+Document+Publishing+Details
Adding ~bkline as a watcher before closing this ticket.
It doesn't appear that there is the need to make any additional changes to the SQL queries based on the past query times. All of our queries are executing within seconds. The longest running one - CTGovProtocol - will go away by the end of the month and the implemented changes in OCECDR-4305 are currently running on DEV.
Closing this ticket.
Elapsed: 0:00:00.001438