Hello,
I have this problem I cannot resolve.
I have a view that is this one:
SELECT EL.EquipmentClassId, E.Id, E.Tagname, L.EquipmentClassId, T.Description AS Type,cast(EW.Value as real) AS EmptyWeight, E.Valid
FROM dbo.tbl_EquipmentClass AS T
INNER JOIN dbo.tbl_EquipmentLink AS L ON T.Id = L.EquipmentClassId and L.EquipmentClassId not in (1t,7)
INNER JOIN dbo.tbl_Equipment AS E ON L.EquipmentId = E.Id
INNER JOIN dbo.tbl_EquipmentLink AS EL ON E.Id = EL.EquipmentId AND (EL.EquipmentClassId = 7)
LEFT OUTER JOIN dbo.tbl_EquipmentProperty AS EW ON E.Id = EW.EquipmentId AND EW.EquipmentClassPropertyId = 6
The problem is that it takes like 18 seconds to load the view when the underlined part is in it.
If I remove the underlined part, it runs in less then 1 second, and returns me 12000 records, with the EL.EquipmentClassId right in front of it.
So the only difference is that these 12000 records need to be filtered on a value of the first Column, but that takes 18 seconds
I have tried moving the ON to a WHERE and to a GROUP BY and HAVING, but it remains slow. Why is this please? It is so strange.
Can someone please help me?
Thanks a lot
djorre
P.S. this is actually a SQL 2012 environment but I posted it here because the 2008 forum has more T-SQL users.
HAVING, WHERE or ON clause, query slows down hard
Posted - 11/06/2012 : 14:15:46
