5 Replies Latest reply on May 2, 2016 6:09 PM by skwan

    PI OLEDB Question - Simple Join?

    TomMcCarthyNeoPLM

      HI All,

       

      I am working with PI OLEDB against the Asset Framework database and have what I think should be a simple question.  When I do a simple query like.

       

      Select eh.path,

          eh.name,

          eh.elementid

      From Asset.ElementHierarchy eh

      Where eh.Name = 'PIPoints'

       

      The system is quite responsive.

       

      Even the "Expensive query"

       

      select ea.*

      from Asset.ElementAttribute ea

      where ea.name like '%FIC%'

      OPTION (ALLOW EXPENSIVE)

       

      is quite responsive.

       

      When I combine the two to get

       

      select eh.path, eh.name, eh.elementid, ea.name

      from Asset.ElementHierarchy eh

        inner join Asset.ElementAttribute ea

        ON ea.ElementID = eh.ElementID

      where eh.Name = 'PIPoints'

        ea.Name like '%FIC01%'

       

      The performance slows to a crawl and always times out, even if I use the option (force order) directive.  

       

      This is a customer machine, so I am assuming that they are doing sql index rebuilding, and whatever other maintenance that is recommended by OSI Soft.  The only thing I can think of is that they have a large ish number of attributes (a few thousand) under the element that I am searching for.  Is there effectively a maximum number of attributes that you can configure under an element, or is there anything else that should be tried here?

       

      Thanks

       

      Tom