Why are Subqueries Dangerous?
If you go around to the various forums, you’ll see postings, including some I’ve put up, that say using subqueries, especially correlated subqueries in the SELECT statements of queries is bad because it effectively acts as a cursor.
I got called on it. So I had to do a bit of research.
Books Online talks about it right off when describing subqueries. That’s not proof though.
I did some more looking around. Adam Machanic does a nice job of slicing up Functions, which are basically the same thing. But it’s not exactly the same.
I kept looking. This blog entry is just flat wrong, but the second comment points out the fallacy. Jeff Moden would also like this example since it shows the disparity between the actual cost of queries and the estimated cost used by the graphical execution plans to display results. But this isn’t in the SELECT statement. Moving on.
Of course. The real answers always come from the SOURCE: Itzik
Performance Data Warehouse
The new functionality coming out with 2008 includes the Data Collector which feeds to the Performance Data Warehouse. I presented this topic at the SNESSUG Heroes Launch event. It’s pretty slick functionality. I posted about it once before. A new article by Derek Comingore is available now over at SQL Mag. It’s worth the read.
The beauty of the new tool is the fact that it’s not all that new. It’s doing all this work using tools that we know and are comfortable with. This makes it very easy to implement and maintain. I agree with Mr. Comingore and I hope they expand the client to cover SQL Server 2000 and 2005.
Home of the Scary DBA 