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.