| Issue Number | 4305 |
|---|---|
| Summary | Adjust Production SQL Queries |
| Created | 2017-08-29 12:10:10 |
| Issue Type | Improvement |
| Submitted By | Englisch, Volker (NIH/NCI) [C] |
| Assigned To | Englisch, Volker (NIH/NCI) [C] |
| Status | Closed |
| Resolved | 2017-08-29 16:45:01 |
| Resolution | Fixed |
| Path | /home/bkline/backups/jira/ocecdr/issue.213560 |
We've identified a SQL query that's part of our nightly production job which suddenly changed from running within 20 seconds to taking 20 minutes.
Min suggested to modify the SQL query to remove the use of views where possible. This change restored the processing time in our tests.
For reasons unknown to the common programmer, the original query which recently took 20 minutes to finish is now back to the previous performance and finishes again in 20 seconds. This indicates there currently isn't an urgent need for the suggested modification.
Min still suggests to go ahead with the change to the SQL query as identified last Thursday to eliminate one of the many variables (eliminating a table view and using the table instead) that might cause the query to significantly slow down in the future.
I've replaced the view publishable_version with the table doc_version for all of our queries in the following document:
Primary.xml
The updated control document has been replaced on DEV.
As part of the CTS release we want to exclude the following document types from publishing by the end of the month:
Country (140 docs)
PoliticalSubUnit (211 docs)
CTGovProtocol (5,390 docs)
Organization (18,981 docs)
This will be done as part of this ticket.
Additional changes to the control document removing those options that publish individual document types, i.e. Export-Country, Export-CTGovProtocol, etc.
Primary.xml (a049cfd)
Changes are in branch: cdr4305
The updated control document has been placed on DEV and QA. The weekend job will run without processing the document types CTGovProtocol, Organization, Country, and PoliticalSubUnit.
A test during the week indicated that the publishing job on DEV ran for 3 hours instead of 4 hours when it included all document types.
Additional changes to suppress processing of document tytpes saved to git repo lib:
RepublishDocs.py [OCECDR-4305 1b579ee]
cdrpub.py [OCECDR-4305 1b579ee]
Additional changes to suppress processing of document types saved to git repo admin:
Republish.py [cdr4303 5f7d546e]
I've updated the publishing control document on PROD with the following commits:
These changes are running on PROD for nearly 2 weeks without problems. Closing ticket.
Elapsed: 0:00:00.001294