Note: We no longer publish the latest version of our code here. We primarily use a kumc-bmi github organization. The heron ETL repository, in particular, is not public. Peers in the informatics community should see MultiSiteDev for details on requesting access.

source: heron_load/query_performance_compare.py @ 0:42ad7288920a

heron-michigan tip
Last change on this file since 0:42ad7288920a was 0:42ad7288920a, checked in by Matt Hoag <mhoag@…>, 6 years ago

Merge with demo_concepts_3800

File size: 3.9 KB
Line 
1''' Compare query performance of given description with the last run
2'''
3from pkg_resources import resource_string
4import db_util
5from html import HTML
6
7
8def main(argv, configure_db, html_write,
9         db_section='deid',
10         logger_name='heron_performance',
11         temp_table_name='temp_report',
12         report_sql=resource_string(__name__, 'query_performance_rpt'),
13         cleanup_sql=resource_string(__name__, 'query_performance_cleanup'),
14         get_cols_sql=resource_string(__name__, 'query_performance_get_cols')):
15
16    config_filename, test_run_desc, html_filename = argv[1:4]
17    db, host_port, events, sid = configure_db(config_filename,
18                                              db_section, logger_name)
19    other_sid = get_other_sid(sid)
20    cleanup_sql = cleanup_sql % {'temp_table_name': temp_table_name}
21    db_util.run_script(db, 'cleanup', cleanup_sql)
22
23    report_sql = report_sql % {'this_sid': sid, 'other_sid': other_sid,
24                               'run_desc': test_run_desc,
25                               'temp_table_name': temp_table_name}
26
27    with db.transaction('performance_report') as q:
28        q.execute(report_sql)
29        q.execute(get_cols_sql % {'temp_table_name': temp_table_name})
30        cols = [c1.split('.')[1] for c1 in
31                sorted([c[0] for c in q.fetchall()])]
32
33        q.execute('select * from %s' % temp_table_name)
34        rpt = q.fetchall()
35
36    html_report = format_report(cols, rpt)
37    html_file = html_write(html_filename)
38    html_file.write(html_report)
39    html_file.close()
40
41
42def get_other_sid(sid):
43    ''' Get the complementary (other) SID
44    >>> get_other_sid('bheronFA')
45    'bheronfb'
46    >>> get_other_sid('bheronFb')
47    'bheronfa'
48    >>> get_other_sid('bheronB2')
49    'bherona1'
50    >>> get_other_sid('BmId')
51    'bmid'
52    >>> get_other_sid('unknownSID') #doctest: +IGNORE_EXCEPTION_DETAIL
53    Traceback (most recent call last):
54        ...
55    ValueError:
56    '''
57    sid = sid.lower()
58    sids = (('bheronfa', 'bheronfb'),
59            ('bherona1', 'bheronb2'),
60            ('bmid', 'bmid'))
61    try:
62        group = [sid in p for p in sids].index(True)
63    except ValueError:
64        raise ValueError('Invalid or unknown SID (%s)!' % sid)
65    return sids[group][(sids[group].index(sid) + 1) % 2]
66
67
68def format_report(cols, report):
69    '''Format report as HTML
70    Highlight queries that are faster/slower
71    '''
72    for row in report:
73        if len(row) != len(cols):
74            raise IndexError('Result lengths don\'t match!')
75    h = HTML('html')
76    b = h.body()
77    t = b.table(border="1")
78    r = t.tr(bgcolor="#81F781")
79    for col in cols:
80        r.th(col)
81    for idx, row in enumerate(report):
82        color = "#D8D8D8" if idx % 2 else "#FFFFFF"
83        r = t.tr(bgcolor=color)
84        if 'SLOWER' in row:
85            r = t.tr(bgcolor="#F5A9A9")
86        elif 'FASTER' in row:
87            r = t.tr(bgcolor="#A9F5F2")
88
89        for col in row:
90            t.td(str(col))
91    return str(h)
92
93
94if __name__ == '__main__':
95    def _initial_caps(qp_section='query_performance'):
96        from datetime import date
97        from getpass import getuser
98        from logging import getLogger
99        from logging.config import fileConfig
100        from os import environ
101        from sys import argv
102        from time import time
103
104        def config_rd(n):
105            if n == argv[1]:
106                return open(n)
107
108        def html_write(n):
109            if n == argv[3]:
110                return open(n, 'w')
111
112        def real_cx():
113            import cx_Oracle
114            return cx_Oracle
115
116        configure_db = db_util.make_configure_db(
117            config_rd, fileConfig,
118            calendar=date, timer=time,
119            getLogger=getLogger, getuser=getuser, environ=environ,
120            dbi=real_cx)
121
122        return dict(argv=argv[:],
123                    configure_db=configure_db,
124                    html_write=html_write)
125
126    main(**_initial_caps())
Note: See TracBrowser for help on using the repository browser.