January 26, 2015 at 3:13 pm
srajpx (1/26/2015)
I believe that for each row of the first subquery, the second subquery is executed. Say, the first sub query returns 100 rows, for each of this row the second subquery is executed.
You're thinking about a correlated subquery. Not all subqueries are correlated though. It's perfectly possible for a subquery to be executed only once.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 26, 2015 at 3:41 pm
Great tip. I had to figure this out the hard way.
January 26, 2015 at 3:42 pm
C64DBA (1/26/2015)
They do have use. For some complex queries, the engine sometimes needs help. Adding a derived table in just the right part of the query can make it easier for the engine to find the optimal plan.
Yes, sub-queries do have uses. As clearly demonstrated by the execution plans, the original query could be problematic in performance with 4 scans of the clustered index taking place, when only 1 is actually needed. Using a sub-query to derive an aggregate from the main table of the main query isn't usually necessary, and if it is, then I'd be wanting to examine whether that database or table was properly designed. Deriving an aggregate from a primary table via sub-query should be rare, if ever.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 26, 2015 at 4:56 pm
sgmunson (1/26/2015)
Yes, sub-queries do have uses. As clearly demonstrated by the execution plans, the original query could be problematic in performance with 4 scans of the clustered index taking place, when only 1 is actually needed. Using a sub-query to derive an aggregate from the main table of the main query isn't usually necessary, and if it is, then I'd be wanting to examine whether that database or table was properly designed. Deriving an aggregate from a primary table via sub-query should be rare, if ever.
Said better that I did! Having to work with many vendor databases where schema changes will break support agreements, but still having to reach into these databases on occasion, the derived table approach can be the simplest solution.
January 26, 2015 at 11:47 pm
Hi,
I was wondering, would you say that this is the same as using a view in a query? The view is in effect a subquery. If you have tables that you need to join frequently in queries, is it better to make a view out of them and then use the view in the query. Wouln't this make the query more readable (if the view has a sensible name)?
The consensus here seems to be that CTEs are better than using subqueries as tables. Please can someone explain why?
Thanks,
John
January 27, 2015 at 2:04 am
Well John the other consensus is that a CTE and a derived table in a subquery are about the same thing. The big advantage for me would be readability, testing, and documentation. The keyword WITH right at the top tells me that I'm looking at as CTE while the table in a subquery can come as a surprise. As for testing I construct the CTE one level at a time with a test query at the bottom so that I can check the results. While I'm in progress I put in block comments to remind me later what I was trying to accomplish.
For a production facility they wanted a "Station Throughput" report. The table had the product number, the station number, the product weight, and the date/time one row per item produced. For the time range that is simple and there is even an index for that. But they want to see the station name and that is in the station table. Sometimes they want all stations in the plant and sometimes just the stations in a certain department. Department ID is in the station table and department name is in the department table. Oh, the results need to be shown in items per hour.
Using CTEs this query is quite readable. I needed station number within a department in two places. That would have been complex with subqueries.
ATBCharles Kincaid
January 27, 2015 at 7:30 am
John Corkett MBCS (1/26/2015)
Hi,I was wondering, would you say that this is the same as using a view in a query? The view is in effect a subquery. If you have tables that you need to join frequently in queries, is it better to make a view out of them and then use the view in the query. Wouln't this make the query more readable (if the view has a sensible name)?
The consensus here seems to be that CTEs are better than using subqueries as tables. Please can someone explain why?
Thanks,
John
John,
The use of a view should rightly be reserved for their proper use, which is generally to take a complex table relationship and allow it to be used like a table, and/or present a limited subset of things to help protect a set of base tables, as part of either a reporting set up, or as a participating method in an overall security mechanism. The use of CTEs is generally a good idea when separate, complex elements must be JOINed. A CTE is, effectively, an in-line view. The optimizer often benefits from this kind of query construction because it can look across the final query's JOINs and determine a better query plan by limiting each CTE element based on those JOINs, whereas a sequence of sub-queries rarely benefits from this kind of optimization.
EDIT: No element of the original query was sufficiently complex to justify the use of a CTE, as even the sub-queries weren't necessary.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 28, 2015 at 4:24 am
I think the information was too short for an article. "You need to put an alias for the subquery", that's it, that was all the info.
It could have been a QOD infact. Such as
Q: Would this query run fine?
4 Options given as answer.
Explained Ans: You need to put an alias for the subquery.
Done. Information passed.
January 28, 2015 at 4:31 am
Divine Flame (1/28/2015)
I think the information was too short for an article. "You need to put an alias for the subquery", that's it, that was the all info.It could have been a QOD infact. Such as
Q: Would this query run fine?
4 Options given as answer.
Explained Ans: You need to put an alias for the subquery.
Done. Information passed.
True, but not everyone does the QotD. Most people will see the headlines in the newsletter though.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 28, 2015 at 5:14 am
Koen Verbeeck (1/28/2015)
Divine Flame (1/28/2015)
I think the information was too short for an article. "You need to put an alias for the subquery", that's it, that was the all info.It could have been a QOD infact. Such as
Q: Would this query run fine?
4 Options given as answer.
Explained Ans: You need to put an alias for the subquery.
Done. Information passed.
True, but not everyone does the QotD. Most people will see the headlines in the newsletter though.
True, but then again most people who actually wait for & want to read an interesting article from SSC, will be disappointed after seeing this kind of information as article.
First of all, lot of people know this information already.
Second, people who don't know about this, are really not working as SQL Developer or DBA in some serious project (to care for the article).
Just my thought.
January 28, 2015 at 4:45 pm
Divine Flame (1/28/2015)
True, but then again most people who actually wait for & want to read an interesting article from SSC, will be disappointed after seeing this kind of information as article.First of all, lot of people know this information already.
Second, people who don't know about this, are really not working as SQL Developer or DBA in some serious project (to care for the article).
Just my thought.
I agree, and given that the example used just to demonstrate the need for an alias was a rather poor one; as there was no actual need for the sub-query; perhaps it can best serve as a bad example...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 31, 2015 at 10:32 pm
please always mention the version and edition of SQL server for which the queries were written.
January 31, 2015 at 11:34 pm
shahzadsafdar2002 (1/31/2015)
please always mention the version and edition of SQL server for which the queries were written.
The original article on which this discussion is based was not intended to be version-specific, and since CTE's have been available since at least SQL 2005, and the most current version of SQL Server (2014) is 4 versions down the road from SQL 2005, there's just not that much value in doing so on this particular thread.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply