Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
djorre
Yak Posting Veteran
94 Posts |
 Posted - 2012-11-06 : 14:15:46
|
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.ValidFROM 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 secondsI 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 lotdjorreP.S. this is actually a SQL 2012 environment but I posted it here because the 2008 forum has more T-SQL users. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-06 : 14:46:49
|
| what are the indexes you've on dbo.tbl_EquipmentLink table? did you have a look at execution plan and see difference between case where extra condition is in and otherwise?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
djorre
Yak Posting Veteran
94 Posts |
Posted - 2012-11-06 : 16:17:46
|
quote:
Originally posted by visakh16
what are the indexes you've on dbo.tbl_EquipmentLink table? did you have a look at execution plan and see difference between case where extra condition is in and otherwise?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
There were no indexes on any table.I putted indexes on tbl_EquipmentLink (used columns) and that did not resolve it.Then I also put an index on tbl_EquipmentProperty (used columns) and that did it, the query now returns results on less then 1 second.I did not know that indexes were that imported, guessed sql (2012) would create a good execution plan but that was thought wrong.Thank you! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-07 : 09:30:36
|
quote:
Originally posted by djorre
quote:
Originally posted by visakh16
what are the indexes you've on dbo.tbl_EquipmentLink table? did you have a look at execution plan and see difference between case where extra condition is in and otherwise?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
There were no indexes on any table.I putted indexes on tbl_EquipmentLink (used columns) and that did not resolve it.Then I also put an index on tbl_EquipmentProperty (used columns) and that did it, the query now returns results on less then 1 second.I did not know that indexes were that imported, guessed sql (2012) would create a good execution plan but that was thought wrong.Thank you!
Indexes are important while retrieving resultssql 2012 or any version for that matter wont automatically create indexes . it will give you recommendations but its left to you to decide whether or not to add it. You also have to consider lot of other factors like how frequently query is used, is table already having lots of indexes, is it target of bulk DML operations etc before you really go ahead and add the indexes.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
HAVING, WHERE or ON clause, query slows down hard