I have been given the following slow performing query which is taking a cut from 1.2 million rows to return about 51,000. It is taking about 56 seconds which is slow for a webpage.
Can I write the JOIN in a different way to speed things up or should I be looking at indexes and so on for the tables? Also I am surprised that sort of join still exists and still runs on SQL Server!
asked Feb 24, 2010 at 10:15 AM in Default
As long as it's an inner join it will work in 2008. The ANSI 89 outer join syntax is deprecated. Based on the tags you're running in this in '05, so it'll work.
As to whether or not changing the syntax will speed it up, it's hard to know without seeing the execution plans for the query. But, in general, I shy away from joining views to views where I can. SQL Server can do a very good job of resolving the view for a given query, but, depending on the view and the query, it might not bother to resolve the view at all and will merely run it as written, whether or not you're referencing all the tables & columns in the view or not. Clearly, that can lead to huge performance problems.
answered Feb 24, 2010 at 10:29 AM
Grant Fritchey ♦♦
Ian... I see no join criteria between the two tables. What you have there is a full blown cross-join. There has to be something that both tables can relate to so you can join them without them being a cross-join.
answered Feb 25, 2010 at 04:18 AM