SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 HAVING, WHERE or ON clause, query slows down hard
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

djorre
Yak Posting Veteran

94 Posts

Posted - 11/06/2012 :  14:15:46  Show Profile  Reply with Quote
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.

Edited by - djorre on 11/06/2012 14:38:24

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/06/2012 :  14:46:49  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

djorre
Yak Posting Veteran

94 Posts

Posted - 11/06/2012 :  16:17:46  Show Profile  Reply with Quote
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 MVP
http://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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/07/2012 :  09:30:36  Show Profile  Reply with Quote
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 MVP
http://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 results
sql 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000