AnsweredAssumed Answered

How can you get best performance out of referenced attributes

Question asked by NHarper on May 25, 2016
Latest reply on May 25, 2016 by NHarper

Hi Community,

First time posting and new (ish) to PI. So thankyou in advance

I am testing some different methods to extract PIAF attribute values using the OLEDB link, with performance being a key factor. My query runs quite well when grabbing static and PIpoint values. The current query is below (edited for privacy reasons)

 

SELECT eh.Attribute1, tc.Attribute2Date, tc.Attribute3, tc.Attribute4, tc.Attribute5, tc.Attribute6

FROM [AMR].[Asset].[Element] e left join [AMR].[Asset].[ElementHierarchy] eh ON e.ID = eh.ElementID

INNER JOIN [AMR].[DataT].[ft_TransposeSnapshot_ServicePoint] tc ON tc.ElementID = eh.ElementID

Where eh.Path Like N'\root\subroot\%' and Attribute3 is not null and Attribute2Date > '*-7d'

OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

 

This runs in around 3 minutes and returns ~34,000 records.

If I then include an additional attribute. One which is a referenced string builder ".\element[@index=1]|ID" which grabs the value from an attribute child element. The run time increases massively (I gave up after an hour). I'm also seeing this sort of performance hits when introducing static attributes which are fed by Analyses. I understand writing to a PIpoint would help with the analyses performance however I'm restricted with licenses at work and its unfortunately not an option.

 

Are there any best practices or tricks to allow for performing these queries in a reasonable time frame?

 

Look forward to your responses and advice.

Nathan

Outcomes