TomMcCarthyNeoPLM

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,

    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

Outcomes