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

    PI OLEDB Question - Simple Join?


      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,



      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%'



      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?