We hope you'll join us for our 4/23 webinar on using data tables to apply reference ranges and AE codes in OC4. For more information and to register, visit https://register.gotowebinar.com/register/2882170018956684555

Administer Subjects - slow loading

Hi,

just wondering if anybody here noticed an issue that with increasing amount of subjects the Administer Subjects page loading is not scaling well. The subjects are paginated but the page loads slower and slower and with 5k subjects the load time is starting to be noticeable. Similar slowness is present when navigating between Subjects datatable pages or when sorting the datatable. Have anybody seen such behaviour? Could it be that SQL query for this view is not optimised well?

Seem to me that this is the case because e.g. when loading Subject Matrix for study with 3k patients the page load is fast, as well as sorting and navigation between StudySubjects datatable pages.

any feedback on this issue is welcome

thanks and best

Tomas

Comments

  • mvirtosumvirtosu Posts: 276 ✭✭
    Tomas,

    The Administer Subjects has been very slow, to the point of it being unusable, for us as well. Our data managers have just given up on it. I do not have a workaround for you.

    Sorry,

    Mihai
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    In postgres, try setting log_min_duration_statement [1] to a low value or 0
    so you can identify the queries involved.

    There seems to be some use of "distinct" in the subject and studysubject
    dao queries (e.g. [2]) which may be the cause.

    If you can't change the OpenClinica code to address this, you can try
    helping postgres by increasing the work_mem setting which is used for sorts
    which tend to go with distinct's.

    [1] https://www.postgresql.org/docs/8.4/static/runtime-config-logging.html
    [2]
    https://github.com/OpenClinica/OpenClinica/blob/c3fbcce3dc8327c883ed1fa826ef000f6b363fac/core/src/main/resources/properties/subject_dao.xml
  • toskriptoskrip Posts: 279 ✭✭✭
    Thanks Lindsay, the problem is actually not optimised query for getting the count of subjects ("getCountWithFilter") that is later used for pagination in UI to allow lazy loading. And also with sorting query ("getWithFilterAndSort"). For some reason this queries do cartesian product (all combinations) with user_account table (which makes non sense).

    Removing the "user_account ua" from these queries solves the performance issue. E.g. in my setting the count was optimised from 1241ms to 12ms.

    I will see if I can get patch for this to the OC master.

    best

    Tomas
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    Wow, nice find!
  • ccollinsccollins Posts: 383 admin
    via Email
    Tomas, thanks we'll get this merged into 3.12.

    Cal
Sign In or Register to comment.