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.
From Asset.ElementHierarchy eh
Where eh.Name = 'PIPoints'
The system is quite responsive.
Even the "Expensive query"
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?