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_rpt @ 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: 2.8 KB
Line 
1create global temporary table %(temp_table_name)s
2on commit preserve rows as
3with
4-- All tests from both SIDs
5all_tests as (
6  select * from (
7    select t.*, '%(this_sid)s' dbsid from timingtests t
8    union all
9    select t.*, '%(other_sid)s' dbsid from timingtests@%(other_sid)s t
10    )
11  where endtime is not null
12  ),
13all_other_tests as(
14  select * from all_tests where testrundesc != '%(run_desc)s'
15  ),
16-- Latest start time for each test name
17latest as (
18  select
19    queryname, max(starttime) maxstart
20  from
21    all_other_tests
22  group by
23    queryname
24    ),
25/* testid is only unique per sid.  And, we can have more than one test that
26starts during the same second.  So, narrow down to just one of them.
27*/
28just_one as (
29  select max(all_other_tests.testid) maxid, max(all_other_tests.dbsid) maxsid, all_other_tests.queryname
30  from latest
31  join all_other_tests on latest.queryname = all_other_tests.queryname
32    and all_other_tests.starttime = latest.maxstart
33  group by all_other_tests.queryname
34  ),
35-- Join back with all_other_tests to get all columns
36last_results as (
37  select all_other_tests.*
38  from just_one
39  join all_other_tests on all_other_tests.testid = just_one.maxid
40    and all_other_tests.dbsid = just_one.maxsid
41  ),
42/* Last query execution from this current run (a single description should only
43exist in a single SID
44*/
45these_results as (
46  select all_tests.* from (
47    select max(testid) maxid, queryname
48    from all_tests where testrundesc = '%(run_desc)s'
49    group by queryname
50    ) these
51  join all_tests on all_tests.testid = these.maxid
52    and all_tests.testrundesc = '%(run_desc)s'
53  ),
54deltas as (
55  select
56    -- Common to both test runs
57    -- Note column naming so we can sort in the proper order later
58    last_results.queryname "0.Query Name", last_results.querydesc "1.Query Description",
59   
60    -- Last run results
61    round(last_results.testduration, 2) "2.Last Duration (sec)",
62    last_results.dbsid "3.Last SID", last_results.testrundesc "4.Last Run Description",
63   
64    -- This run results
65    round(these_results.testduration, 2) "5.This Duration (sec)",
66    these_results.dbsid "6.This SID", these_results.testrundesc "7.This Run Description",
67   
68    -- Delta
69    round(these_results.testduration - last_results.testduration) "8.Delta (sec)"
70  from
71    last_results
72  join these_results on these_results.queryname = last_results.queryname
73  )
74select
75deltas.*,
76  case
77    when deltas."8.Delta (sec)" < 0 then 'FASTER'
78    when deltas."8.Delta (sec)" > 0 then 'SLOWER'
79    when deltas."8.Delta (sec)" = 0  then 'SAME'
80    else '?'
81  end "9.FASTER/SLOWER",
82  case when deltas."2.Last Duration (sec)" = 0 or deltas."8.Delta (sec)" = 0 then 'N/A'
83  else round(abs(deltas."8.Delta (sec)") * 100/deltas."2.Last Duration (sec)", 2) || ' percent'
84  end "9.Result Percent"
85from deltas
Note: See TracBrowser for help on using the repository browser.