PI OLEDB Question - Simple Join?

Discussion created by TomMcCarthyNeoPLM on Apr 21, 2016
Latest reply on May 2, 2016 by skwan

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



is quite responsive.


When I combine the two to get


select eh.path,, eh.elementid,

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?