jsnider

Expensive PI SQL Query (AF)

Discussion created by jsnider on Apr 27, 2012
Latest reply on Apr 30, 2012 by mhamel

I am having a problem with this query.  It times out after about 4 minutes and never completes.

 

It works fairly fast without the join to the snapshot table, but I really need the snapshot data for what I am trying to do with this query...

 

Can somebody take a look at this and let me know if there is a way to achieve the same thing but make the query less expensive?

 
SELECT e.Name Element, ea.Name Attribute, s.Time, s.Value
FROM [SWG-Paiute].Asset.ElementTemplate et
INNER JOIN [SWG-Paiute].Asset.ElementTemplateAttribute eta ON eta.ElementTemplateID = et.ID
INNER JOIN [SWG-Paiute].Asset.ElementAttribute ea ON ea.ElementTemplateAttributeID = eta.ID
INNER JOIN [SWG-Paiute].Asset.Element e ON e.ID = ea.ElementID
INNER JOIN [SWG-Paiute].Asset.ElementAttributeCategory eac ON eac.ElementAttributeID = ea.ID
INNER JOIN [SWG-Paiute].Asset.Category c ON c.ID = eac.CategoryID
INNER JOIN [SWG-Paiute].Data.Snapshot s ON s.ElementAttributeID = ea.ID
WHERE et.Name = N'CustomerRTU' and c.name = N'GTS Data' 

 

Outcomes