| Issue Number | 5363 |
|---|---|
| Summary | Speed up document version history report |
| Created | 2025-01-16 10:18:43 |
| Issue Type | Improvement |
| Submitted By | Kline, Bob (NIH/NCI) [C] |
| Assigned To | Kline, Bob (NIH/NCI) [C] |
| Status | Resolved |
| Resolved | 2025-01-16 10:22:56 |
| Resolution | Fixed |
| Path | /home/bkline/backups/jira/ocecdr/issue.488019 |
The document version history report is too slow, sometimes taking multiple minutes if the report hasn't been run in so long that the cached data behind the report are all cleared.
Added an index on the pub_proc table and rewrote the
query which selects the publishing events associated with the report's
document. The original query used views which defeated SQL Server's
ability to figure out the optimization logic, so I avoided the views and
queried the tables directly. I also hoisted the lookup of the primary
publishing control document's ID into a separate query. Now the report
takes a couple of seconds for the Summary document with the most
versions.
Elapsed: 0:00:00.001464