Uploaded image for project: 'i2b2 Core Software'
  1. i2b2 Core Software
  2. CORE-255

OMOP: views wont scale for large data

Details

    • Improvement
    • Status: Closed
    • Major
    • Resolution: Won't Do
    • None
    • None
    • None
    • None

    Description

      The views utilize casts of the concept to varchar ... this will in turn cause a full table scan – the optimizer can’t take advantage of any indexes that may already exist on the underlying table because it has to do a real time conversion which disrupts the order (1, 2, 5, 10) goes to (1, 10, 2, 5). As a result, you’re going to have issues running this on anything sizable because the queries will perform as if unindexed. One option would be to implement indexed or materialized views which get tied to the underlying schema – these write the data to disk (essentially creating a mirror of the data in its transformed state). The trade off, of course, would be that you would need to twice the space to house these.

      Attachments

        Activity

          lcp5 Lori Phillips created issue -
          lcp5 Lori Phillips made changes -
          Field Original Value New Value
          Assignee Lori Phillips [ lcp5 ]
          jmd86 Janice Donahoe made changes -
          Status New [ 10000 ] Open [ 1 ]
          jmd86 Janice Donahoe made changes -
          Issue Type Bug [ 1 ] Improvement [ 4 ]
          jmd86 Janice Donahoe made changes -
          Assignee Lori Phillips [ lcp5 ] Janice Donahoe [ jmd86 ]
          jmd86 Janice Donahoe made changes -
          Resolution Won't Do [ 10100 ]
          Status Open [ 1 ] Resolved [ 5 ]
          jmd86 Janice Donahoe made changes -
          Status Resolved [ 5 ] Closed [ 6 ]

          People

            jmd86 Janice Donahoe
            lcp5 Lori Phillips
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: