AnsweredAssumed Answered

How to optimise a slow PI-OLEDB query?

Question asked by AlistairFrith on Oct 25, 2016
Latest reply on Oct 27, 2016 by rene

I have the following query, which I am testing in PI SQL Commander:

 

  SELECT ea.ElementId

         ,CASE WHEN ea.Path<>'\' THEN CONCAT(REPLACE(ea.Path,'\',''),'|',ea.Name) ELSE ea.Name END

         ,s.Value

  FROM [Machine Reliability].Asset.ElementAttribute ea

       INNER JOIN [Machine Reliability].Data.Snapshot s ON s.ElementAttributeID = ea.ID

  WHERE ea.Name IN ('Asset Alarm Count','Asset Trips Count','MTD','YTD','MTD Count','YTD Count','Last Performed')

  OPTION (FORCE ORDER, IGNORE ERRORS)

 

it takes 42 seconds to run!

If I comment the lines retrieving data from the Snapshot table, like this:

 

  SELECT ea.ElementId

         ,CASE WHEN ea.Path<>'\' THEN CONCAT(REPLACE(ea.Path,'\',''),'|',ea.Name) ELSE ea.Name END

         -- ,s.Value

  FROM [Machine Reliability].Asset.ElementAttribute ea

       -- INNER JOIN [Machine Reliability].Data.Snapshot s ON s.ElementAttributeID = ea.ID

  WHERE ea.Name IN ('Asset Alarm Count','Asset Trips Count','MTD','YTD','MTD Count','YTD Count','Last Performed')

  OPTION (FORCE ORDER, IGNORE ERRORS)

 

Then it runs in 0.3 seconds.

 

So there seems to be a huge performance hit when joining to the Snapshot table. Is there any way of re-writing this query to make it more efficient?

 

Regards,

 

--- Alistair.

Outcomes