Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#2285 closed defect (fixed)

HERON performance page doesn't include timeline queries

Reported by: rwaitman Owned by: dconnolly
Priority: major Milestone: heron-arkansas-update
Component: data-repository Keywords: performance, public-web
Cc: dconnolly, ngraham, rwaitman, mnair, tmcmahon, mhoag Blocked By:
Blocking: #2581 Sensitive: no

Description

In a meeting with Russ, Dan and Nathan, we noticed that the performance page doesn't include timeline queries.

Change History (25)

comment:1 Changed 7 years ago by dconnolly

Keywords: performance added

comment:2 Changed 7 years ago by dconnolly

The data seems to be in the QT_PDO_QUERY_MASTER table.

comment:3 Changed 7 years ago by dconnolly

Milestone: heron-lovewell-updateHERONv.Next

comment:4 Changed 7 years ago by dconnolly

Cc: mnair tmcmahon mhoag added
Milestone: HERONv.Nextheron-beaver-update
Reporter: changed from ngraham to rwaitman

Russ made another request to fix this (#2522).

I'll try to work this in to milestone:heron-beaver-update planning discussions.

comment:5 Changed 7 years ago by ngraham

Owner: changed from dconnolly to schandaka
Status: newassigned

Today Dan, Matt, Bhargav, Nathan, and Sravani met to discuss goals for milestone:heron-beaver-update and decided to assign this ticket to Sravani. Bhargav mentioned he was willing to help.

comment:6 Changed 7 years ago by dconnolly

Blocking: 2530 added

comment:7 Changed 7 years ago by dconnolly

We did meet on Mon, 3 Feb, but rather than get the whole thing done during the meeting, we agreed to bring Sravani up to speed on WritingQualityCode#pydev and such in due course.

I think Bhargav did some exploration in sqldeveloper. Please attach those notes or otherwise summarize the discussion, Sravani and Bhargav.

comment:8 Changed 7 years ago by schandaka

Milestone: heron-beaver-updateheron-arkansas-update

comment:9 Changed 7 years ago by ngraham

Blocking: 2530 removed

Milestone changed in comment:8, but dependency didn't.

comment:10 Changed 7 years ago by mhoag

Blocking: 2530 added

(In #2530) Dan mentioned that trac pub should probably not be blocking this ticket and that trac-pub simply being in the milestone was enough. I agree.

comment:11 Changed 7 years ago by mhoag

Blocking: 2530 removed

(In #2530) Mistakenly added Blocked by 2285 back because I didn't refresh after comment:10.

comment:12 Changed 7 years ago by schandaka

Query to include timeline queries in the recent queries.

select * from (select qm.query_master_id, qm.name, qm.user_id, qt.name as status,
  nvl(cast(qi.end_date as timestamp),
      -- round to nearest second by converting to date and back
      cast(cast(current_timestamp as date) as timestamp))
  - cast(qm.create_date as timestamp) elapsed,
  qm.create_date,
  qi.end_date,
  qi.batch_mode,
  qm.request_xml  ,
  rt.result_type_id,
  rt.description result_type_description
FROM (
  select * from (
   select * from blueherondata.qt_query_master qm
   where qm.delete_flag != 'Y'
   order by qm.query_master_id desc
   ) where rownum < 40) qm
JOIN blueherondata.qt_query_instance qi
ON qm.query_master_id = qi.query_master_id

left JOIN blueherondata.qt_query_result_instance qri
ON qi.query_instance_id = qri.query_instance_id

left JOIN blueherondata.qt_query_result_type rt
ON rt.result_type_id = qri.result_type_id
left JOIN blueherondata.qt_query_status_type qt
ON qt.status_type_id = qi.status_type_id

UNION ALL


select 
 qm.query_master_id
,'' as name
,qm.user_id
,'' as status
,cast(cast(qm.create_date as date) as timestamp)
  - cast(qm.create_date as timestamp)   elapsed
,qm.create_date
,qm.create_date as end_date
,'' as batch_mode
,qm.request_xml
,9 as result_type_id
,'Timeline' as result_type_description

 from BlueHeronData.qt_pdo_query_master qm
join I2B2PM.pm_user_data ud on qm.user_id=ud.user_id 
)

order by create_date desc,user_id;


comment:13 Changed 7 years ago by dconnolly

deployment is (sort of) documented in #254

comment:14 Changed 7 years ago by schandaka

Query to parse the qt_pdo_query_master.request_xml and obtain the timeline query description(name) which is stored in qt_query_result_instance.description

select * from (select qm.query_master_id, qm.name, qm.user_id, qt.name as status,
  nvl(cast(qi.end_date as timestamp),
      -- round to nearest second by converting to date and back
      cast(cast(current_timestamp as date) as timestamp))
  - cast(qm.create_date as timestamp) elapsed,
  qm.create_date,
  qi.end_date,
  qi.batch_mode,
  qm.request_xml  ,
  rt.result_type_id,
  rt.description result_type_description
FROM (
  select * from (
   select * from blueherondata.qt_query_master qm
   where qm.delete_flag != 'Y'
   order by qm.query_master_id desc
   ) where rownum < 40) qm
JOIN blueherondata.qt_query_instance qi
ON qm.query_master_id = qi.query_master_id

left JOIN blueherondata.qt_query_result_instance qri
ON qi.query_instance_id = qri.query_instance_id

left JOIN blueherondata.qt_query_result_type rt
ON rt.result_type_id = qri.result_type_id
left JOIN blueherondata.qt_query_status_type qt
ON qt.status_type_id = qi.status_type_id

UNION ALL


select 
 qm.query_master_id
,(select qri.description from blueherondata.qt_query_result_instance qri
 where qri.result_instance_id= 
 cast(regexp_replace(
dbms_lob.substr(qm.request_xml,
abs(INSTR(qm.request_xml,'<patient_set_coll_id>',1,1) +21
-INSTR(qm.request_xml,'</patient_set_coll_id>',1,1))
,instr(qm.request_xml,'<patient_set_coll_id>',1,1)+21
) 
, '[^0-9]+', '') as number))  as name
,qm.user_id
,'' as status
,cast(cast(qm.create_date as date) as timestamp)
  - cast(qm.create_date as timestamp)   elapsed
,qm.create_date
,qm.create_date as end_date
,'' as batch_mode
,qm.request_xml
,9 as result_type_id
,'Timeline' as result_type_description

 from BlueHeronData.qt_pdo_query_master qm
join I2B2PM.pm_user_data ud on qm.user_id=ud.user_id 
)

order by create_date desc,user_id;

comment:15 Changed 7 years ago by dconnolly

for testing on test-jboss-server:

  • clone the code there
  • copy your edits over
  • set up a PythonVirtualEnvironment (mine is called heron-admin)
  • install dependencies: python setup.py develop where setup.py is source:raven-j/setup.py
    • evidently py-dom-xpath is stubborn
      • download from py-dom-xpath files
      • scp to test-jboss-server
      • untar
      • cd py-dom...; python setup.py install
        • be sure you've got your python environment activated while you do this
      • go back to your raven-j (aka heron-admin) source directory and re-run python setup.py develop
  • try the reporting integration test:
(heron-admin)dconnolly@test-jboss-server:~/projects/heron-admin/heron_wsgi/admin_lib> python audit_usage.py

comment:16 Changed 7 years ago by schandaka

Owner: changed from schandaka to dconnolly

Timeline query usage has been added to the recent query performance. Code for this has been committed in [7d65beea3f99/raven-j].

comment:17 Changed 7 years ago by dconnolly

Owner: changed from dconnolly to schandaka

The password for the demo user was null. Nathan fixed it. And I released <test DB server>.

Try some timeline queries now and see if they show up in the integration test output?

comment:18 Changed 7 years ago by schandaka

Ran timeline queries on test-jboss-server. They are showing up in the test output.

(heron-admin)schandaka@test-jboss-server:~/raven-j/heron_wsgi/admin_lib> python audit_usage.py 2>,out
(heron-admin)schandaka@test-jboss-server:~/raven-j/heron_wsgi/admin_lib> grep -i timel ,out
INFO:__main__:Recent queries: [(2, u'Patient Set for "CULTURE-BLOOD W@17:17:42"', u'demo', None, datetime.timedelta(0), datetime.datetime(2014, 3, 12, 17, 18, 13), datetime.datetime(2014, 3, 12, 17, 18, 13), None, '<ns6:request xmlns:ns2="http://www.i2b2.org/xsd/hive/pdo/1.1/" xmlns:ns4="http://www.i2b2.org/xsd/cell/crc/psm/1.1/" xmlns:ns3="http://www.i2b2.org/xsd/cell/crc/pdo/1.1/" xmlns:ns5="http://www.i2b2.org/xsd/hive/plugin/" xmlns:ns6="http://www.i2b2.org/xsd/hive/msg/1.1/" xmlns:ns7="http://www.i2b2.org/xsd/cell/crc/psm/querydefinition/1.1/">\n\t<message_header>\n\t\t<proxy>\n            <redirect_url>http://0.0.0.0:8080/i2b2/rest/QueryToolService/pdorequest</redirect_url>\n        </proxy>\n\t\t<sending_application>\n\t\t\t<application_name>i2b2_QueryTool</application_name>\n\t\t\t<application_version>1.6</application_version>\n\t\t</sending_application>\n\t\t<sending_facility>\n\t\t\t<facility_name>PHS</facility_name>\n\t\t</sending_facility>\n\t\t<receiving_application>\n\t\t\t<application_name>i2b2_DataRepositoryCell</application_name>\n\t\t\t<application_version>1.6</application_version>\n\t\t</receiving_application>\n\t\t<receiving_facility>\n\t\t\t<facility_name>PHS</facility_name>\n\t\t</receiving_facility>\n\t\t<message_type>\n\t\t\t<message_code>Q04</message_code>\n\t\t\t<event_type>EQQ</event_type>\n\t\t</message_type>\n\t\t<security>\n\t\t\t<domain>i2b2demo</domain>\n\t\t\t<username>demo</username>\n\t\t\t<password token_ms_timeout="1800000" is_token="true">SessionKey:EybgcLFSYLmRulW5qHTe</password>\n\t\t</security>\n\t\t<message_control_id>\n\t\t\t<message_num>13lM9jfVeM86O6VJoRp5I</message_num>\n\t\t\t<instance_num>0</instance_num>\n\t\t</message_control_id>\n\t\t<processing_id>\n\t\t\t<processing_id>P</processing_id>\n\t\t\t<processing_mode>I</processing_mode>\n\t\t</processing_id>\n\t\t<accept_acknowledgement_type>messageId</accept_acknowledgement_type>\n\t\t<project_id>BlueHeron</project_id>\n\t</message_header>\n\t<request_header>\n\t\t<result_waittime_ms>180000</result_waittime_ms>\n\t</request_header>\n\t<message_body>\n\t\t<ns3:pdoheader>\n\t\t\t<patient_set_limit />\n\t\t\t<estimated_time>180000</estimated_time>\n\t\t\t<request_type>getPDO_fromInputList</request_type>\n\t\t</ns3:pdoheader>\n\t\t<ns3:request xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns3:GetPDOFromInputList_requestType">\n\t\t\t<input_list>\n\t<patient_list max="99999" min="0">\n\t\t<patient_set_coll_id>106</patient_set_coll_id>\n\t</patient_list>\n</input_list>\n<filter_list />\n<output_option>\n\t<patient_set select="using_input_list" onlykeys="false" />\n</output_option>\n\t\t</ns3:request>\n\t</message_body>\n</ns6:request>', 9, u'Timeline'),
 (1, u'Patient Set for "Male@16:55:23"', u'demo', None, datetime.timedelta(0), datetime.datetime(2014, 3, 12, 16, 55, 38), datetime.datetime(2014, 3, 12, 16, 55, 38), None, '<ns6:request xmlns:ns2="http://www.i2b2.org/xsd/hive/pdo/1.1/" xmlns:ns4="http://www.i2b2.org/xsd/cell/crc/psm/1.1/" xmlns:ns3="http://www.i2b2.org/xsd/cell/crc/pdo/1.1/" xmlns:ns5="http://www.i2b2.org/xsd/hive/plugin/" xmlns:ns6="http://www.i2b2.org/xsd/hive/msg/1.1/" xmlns:ns7="http://www.i2b2.org/xsd/cell/crc/psm/querydefinition/1.1/">\n\t<message_header>\n\t\t<proxy>\n            <redirect_url>http://0.0.0.0:8080/i2b2/rest/QueryToolService/pdorequest</redirect_url>\n        </proxy>\n\t\t<sending_application>\n\t\t\t<application_name>i2b2_QueryTool</application_name>\n\t\t\t<application_version>1.6</application_version>\n\t\t</sending_application>\n\t\t<sending_facility>\n\t\t\t<facility_name>PHS</facility_name>\n\t\t</sending_facility>\n\t\t<receiving_application>\n\t\t\t<application_name>i2b2_DataRepositoryCell</application_name>\n\t\t\t<application_version>1.6</application_version>\n\t\t</receiving_application>\n\t\t<receiving_facility>\n\t\t\t<facility_name>PHS</facility_name>\n\t\t</receiving_facility>\n\t\t<message_type>\n\t\t\t<message_code>Q04</message_code>\n\t\t\t<event_type>EQQ</event_type>\n\t\t</message_type>\n\t\t<security>\n\t\t\t<domain>i2b2demo</domain>\n\t\t\t<username>demo</username>\n\t\t\t<password token_ms_timeout="1800000" is_token="true">SessionKey:MdOepI8cNSRnUNJC1wbJ</password>\n\t\t</security>\n\t\t<message_control_id>\n\t\t\t<message_num>z9azXiqCuwxx3197fs8WF</message_num>\n\t\t\t<instance_num>0</instance_num>\n\t\t</message_control_id>\n\t\t<processing_id>\n\t\t\t<processing_id>P</processing_id>\n\t\t\t<processing_mode>I</processing_mode>\n\t\t</processing_id>\n\t\t<accept_acknowledgement_type>messageId</accept_acknowledgement_type>\n\t\t<project_id>BlueHeron</project_id>\n\t</message_header>\n\t<request_header>\n\t\t<result_waittime_ms>180000</result_waittime_ms>\n\t</request_header>\n\t<message_body>\n\t\t<ns3:pdoheader>\n\t\t\t<patient_set_limit />\n\t\t\t<estimated_time>180000</estimated_time>\n\t\t\t<request_type>getPDO_fromInputList</request_type>\n\t\t</ns3:pdoheader>\n\t\t<ns3:request xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns3:GetPDOFromInputList_requestType">\n\t\t\t<input_list>\n\t<patient_list max="99999" min="0">\n\t\t<patient_set_coll_id>104</patient_set_coll_id>\n\t</patient_list>\n</input_list>\n<filter_list />\n<output_option>\n\t<patient_set select="using_input_list" onlykeys="false" />\n</output_option>\n\t\t</ns3:request>\n\t</message_body>\n</ns6:request>', 9, u'Timeline'),

comment:19 Changed 7 years ago by dconnolly

Blocking: 2581 added

comment:20 Changed 7 years ago by schandaka

As per 16, this is done except for testing.

With Nathan Watching:

Currently deployed to <test app server>:

schandaka@test-app-server:/usr/local/heron_admin> cat .hg_archival.txt
repo: cdad0a8a13f9d7d8920cbe3eecbf8a32c7984ec9
node: d157ca10b1642a81a1f2ca4d46cb30ccec04cc06
branch: default
latesttag: null
latesttagdistance: 766
schandaka@test-app-server:/usr/local/src/raven-j> hg update TimelineQueries_2285
schandaka@test-app-server:/usr/local/src/raven-j> sudo -E fab deploy_hg_tip

Restarted apache. Everything looks like a timeline query. Next step is to fix the bug.

comment:21 Changed 7 years ago by dconnolly

to keep from showing timeline queries since the dawn of time, add where qqm.create_date >= sysdate - 14.

comment:22 Changed 7 years ago by schandaka

Fixed and committed code for the recent query performance in [06c71cf2cbd5/raven-j].
Tested the code on test-app-server.

schandaka@test-app-server:/usr/local/src/raven-j> hg update TimelineQueries_2285
schandaka@test-app-server:/usr/local/src/raven-j> sudo -E fab deploy_hg_tip
schandaka@test-app-server:/usr/local/src/raven-j> sudo /etc/init.d/apache2 stop
schandaka@test-app-server:/usr/local/src/raven-j> sudo /etc/init.d/apache2 start

comment:23 Changed 7 years ago by schandaka

Owner: changed from schandaka to dconnolly

Changes to the timeline queries have been committed in [06c71cf2cbd5/raven-j].
Dan, can you review the code? Please reassign this to someone else if you are busy.

comment:24 Changed 7 years ago by dconnolly

Resolution: fixed
Status: assignedclosed
Type: enhancementdefect

I fixed a few PEP8 style issues in [c166150da72b/raven-j]. Otherwise, this looks right on target. I merged it in [4b40f6f9dcdb/raven-j].

Nathan, I expect you to deploy this as part of #2581. I expect to be available to provide a second set of eyeballs.

I didn't fix the lack of a copyright statement nor the lack of unit tests, but those problems pre-existed this change.

CodeReviewNotes

  • Is the use-case clear? yes. bug.
  • Is the use-case reasonably well captured in automated tests?
    • yes; integration test extended to include the new query
  • Is the code something you would be happy to maintain? yes, it's consistent with existing code
    • Is unit test coverage adequate? no relevant changes (but no, we haven't found it cost-effective to put in place mock DB code sufficient to unit test this code)
  • Is the code secure? no relevant changes
  • Are external design constraints clear? in context, yes.
  • Is installation, deployment reasonably well documented? no relevant changes
    • Can you reproduce the results, i.e. can you run integration tests? Yes:
      • (heron_admin)dconnolly@test-app-server:/usr/local/heron_admin/heron_wsgi/admin_lib> python audit_usage.py
    • Is logging sufficient to diagnose problems? no relevant changes
  • Is the copyright notice clear and correct? no relevant changes (but no, it's not)

comment:25 Changed 7 years ago by mhoag

Keywords: public-web added
Note: See TracTickets for help on using tickets.