January 6, 2016 at 8:14 am
erics44 (1/6/2016)
below86 (1/6/2016)
I've had to work with a group of contractors for over a year now, and I find their use of CTE's to be excessive, and as you mentioned most are unnecessary. I chalk a lot of it up to these people truly don't understand the data and how to join the tables properly, and yes a group of us have tried to explain it many times. As I work on the code that they wrote I find I tend to remove a lot of the CTE's. I find that CTE's can be useful but they are also over used by some people.ill be honest when is started this thread this is the kind of answer I was hoping for
I am a contractor myself but I don't over use CTEs, in fact I believe they are for use at specific times and one of those times isn't to replace a sub query that is only used once in your code
i'm not saying this is the gospel or the way I do things is the way it should be done, im just saying I cant personally see how this type of use of CTEs really has any benefit at all in a sql query, particularly not with readability
It all comes down to preference and skill level as a team, an individual can write what ever and how ever, its when a team comes into play that the lead must make some rules as to when to use CTEs and when not to.. If the whole team can read subqueries better, then there you go. I'm more of a team lead and have to balance out my team's skill levels to create the right atmosphere for everyone to read all the queries quickly. There are instances for each option.
January 6, 2016 at 8:21 am
jaime.simancas 27970 (1/6/2016)
erics44 (1/6/2016)
below86 (1/6/2016)
I've had to work with a group of contractors for over a year now, and I find their use of CTE's to be excessive, and as you mentioned most are unnecessary. I chalk a lot of it up to these people truly don't understand the data and how to join the tables properly, and yes a group of us have tried to explain it many times. As I work on the code that they wrote I find I tend to remove a lot of the CTE's. I find that CTE's can be useful but they are also over used by some people.ill be honest when is started this thread this is the kind of answer I was hoping for
I am a contractor myself but I don't over use CTEs, in fact I believe they are for use at specific times and one of those times isn't to replace a sub query that is only used once in your code
i'm not saying this is the gospel or the way I do things is the way it should be done, im just saying I cant personally see how this type of use of CTEs really has any benefit at all in a sql query, particularly not with readability
It all comes down to preference and skill level as a team, an individual can write what ever and how ever, its when a team comes into play that the lead must make some rules as to when to use CTEs and when not to.. If the whole team can read subqueries better, then there you go. I'm more of a team lead and have to balance out my team's skill levels to create the right atmosphere for everyone to read all the queries quickly. There are instances for each option.
I find CTEs help when writing complex queries. They allow you to build up a query in steps, finding and isolating the data you need to complete the actual query. Once done, then you can go back and optimize the query eliminating CTEs where appropriate, while still having the original query to compare results to ensure you still return the correct results.
January 6, 2016 at 8:25 am
One thing that would be nice is if Microsoft implemented CTEs more like the way Oracle implemented their sub-query refactoring clause. Oracle can treat the sub-query refactoring clause as inline code or it can use it as a temporary table running it only once when used multiple times in a query. A CTE in SQL Server is run separately for each use in a single query.
January 6, 2016 at 8:26 am
Lynn Pettis (1/6/2016)
jaime.simancas 27970 (1/6/2016)
erics44 (1/6/2016)
below86 (1/6/2016)
I've had to work with a group of contractors for over a year now, and I find their use of CTE's to be excessive, and as you mentioned most are unnecessary. I chalk a lot of it up to these people truly don't understand the data and how to join the tables properly, and yes a group of us have tried to explain it many times. As I work on the code that they wrote I find I tend to remove a lot of the CTE's. I find that CTE's can be useful but they are also over used by some people.ill be honest when is started this thread this is the kind of answer I was hoping for
I am a contractor myself but I don't over use CTEs, in fact I believe they are for use at specific times and one of those times isn't to replace a sub query that is only used once in your code
i'm not saying this is the gospel or the way I do things is the way it should be done, im just saying I cant personally see how this type of use of CTEs really has any benefit at all in a sql query, particularly not with readability
It all comes down to preference and skill level as a team, an individual can write what ever and how ever, its when a team comes into play that the lead must make some rules as to when to use CTEs and when not to.. If the whole team can read subqueries better, then there you go. I'm more of a team lead and have to balance out my team's skill levels to create the right atmosphere for everyone to read all the queries quickly. There are instances for each option.
I find CTEs help when writing complex queries. They allow you to build up a query in steps, finding and isolating the data you need to complete the actual query. Once done, then you can go back and optimize the query eliminating CTEs where appropriate, while still having the original query to compare results to ensure you still return the correct results.
I also am more than comfortable with this approach
build a query using CTEs and then blend your CTE code into your query once comfortable with the results 🙂
January 6, 2016 at 8:28 am
jaime.simancas 27970 (1/6/2016)
erics44 (1/6/2016)
jaime.simancas 27970 (1/6/2016)
Now, I think the question you are asking is what preference is on CTE use?If so, a common answer is, for simplicity. To create a more readable script instead of using large sub queries and also very useful for window functions and aggregates prior to doing some other formulas of from those sets.
Also, another note, USE SET THEORY to solve your questions, in essence, working with "Data Sets" Joins of sets, is always better and faster, but CTEs have their place in making these "Sets" easier to read..
"Solve the right problem, but first find the right problem to solve."
thanks for the reply
ill just mention again this isn't a who's right and who's wrong question so its just my opinion
but I rarely come across a situation where a CTE is more "Readable" than a sub query
a large CTE is the same as a large subquery isn't it? just in a different place in your code
and if your subquery is so large that it isn't readable then perhaps theres an issue that wouldn't be solved with the question - should I use a subquery or should I use a CTE?
I agree, and it's definately not about who's correct or better. Based on experience, for each individual question one must find the right balance. For my business query writers, I have them write CTE's so they can "Step" through their questions, then we work on optimizations. Sometimes dropping a few CTE's and using set based queries works better. But they have definately found it easier to read and use vs. sub queries. I would think it has to do with the skill level honestly, what ever we end up using.
Is it more skilled to use CTEs or Subqueries?
the queries I came across were CTE based, but I would say the query writer wasn't skilled
That's not just because of the CTEs but his use of CTEs made the code unreadable
January 6, 2016 at 8:29 am
Lynn Pettis (1/6/2016)
One thing that would be nice is if Microsoft implemented CTEs more like the way Oracle implemented their sub-query refactoring clause. Oracle can treat the sub-query refactoring clause as inline code or it can use it as a temporary table running it only once when used multiple times in a query. A CTE in SQL Server is run separately for each use in a single query.
is that right? SQL recompiles the CTE each time it is used?
January 6, 2016 at 8:29 am
jaime.simancas 27970 (1/6/2016)
erics44 (1/6/2016)
below86 (1/6/2016)
I've had to work with a group of contractors for over a year now, and I find their use of CTE's to be excessive, and as you mentioned most are unnecessary. I chalk a lot of it up to these people truly don't understand the data and how to join the tables properly, and yes a group of us have tried to explain it many times. As I work on the code that they wrote I find I tend to remove a lot of the CTE's. I find that CTE's can be useful but they are also over used by some people.ill be honest when is started this thread this is the kind of answer I was hoping for
I am a contractor myself but I don't over use CTEs, in fact I believe they are for use at specific times and one of those times isn't to replace a sub query that is only used once in your code
i'm not saying this is the gospel or the way I do things is the way it should be done, im just saying I cant personally see how this type of use of CTEs really has any benefit at all in a sql query, particularly not with readability
It all comes down to preference and skill level as a team, an individual can write what ever and how ever, its when a team comes into play that the lead must make some rules as to when to use CTEs and when not to.. If the whole team can read subqueries better, then there you go. I'm more of a team lead and have to balance out my team's skill levels to create the right atmosphere for everyone to read all the queries quickly. There are instances for each option.
In and ideal world/team you would have such 'rules', but good luck enforcing that. Back in my COBOL days we had strict coding rules, but you still found code slip into production that didn't completely follow the rules. SQL, IMHO, is more of a personal preference in coding, you will be lucky to find two people who like to format their code the same way. I've found a lot of people I work with and contractors that all seem to prefer no formatting, indenting, and so on. When you have a ton of projects waiting in the wings corporate doesn't want you spending the time to follow some coding rules, just get it done, and working correctly.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 6, 2016 at 8:33 am
Lynn Pettis (1/6/2016)
jaime.simancas 27970 (1/6/2016)
erics44 (1/6/2016)
below86 (1/6/2016)
I've had to work with a group of contractors for over a year now, and I find their use of CTE's to be excessive, and as you mentioned most are unnecessary. I chalk a lot of it up to these people truly don't understand the data and how to join the tables properly, and yes a group of us have tried to explain it many times. As I work on the code that they wrote I find I tend to remove a lot of the CTE's. I find that CTE's can be useful but they are also over used by some people.ill be honest when is started this thread this is the kind of answer I was hoping for
I am a contractor myself but I don't over use CTEs, in fact I believe they are for use at specific times and one of those times isn't to replace a sub query that is only used once in your code
i'm not saying this is the gospel or the way I do things is the way it should be done, im just saying I cant personally see how this type of use of CTEs really has any benefit at all in a sql query, particularly not with readability
It all comes down to preference and skill level as a team, an individual can write what ever and how ever, its when a team comes into play that the lead must make some rules as to when to use CTEs and when not to.. If the whole team can read subqueries better, then there you go. I'm more of a team lead and have to balance out my team's skill levels to create the right atmosphere for everyone to read all the queries quickly. There are instances for each option.
I find CTEs help when writing complex queries. They allow you to build up a query in steps, finding and isolating the data you need to complete the actual query. Once done, then you can go back and optimize the query eliminating CTEs where appropriate, while still having the original query to compare results to ensure you still return the correct results.
I tend to follow what you are saying, I just tend to use tables or temp tables to 'step' through it. And once you get the correct results a lot of these tables or temp tables tend to stay in the code. At least where I work, you don't have time, or much time to rework the code.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 6, 2016 at 8:37 am
below86 (1/6/2016)
jaime.simancas 27970 (1/6/2016)
erics44 (1/6/2016)
below86 (1/6/2016)
I've had to work with a group of contractors for over a year now, and I find their use of CTE's to be excessive, and as you mentioned most are unnecessary. I chalk a lot of it up to these people truly don't understand the data and how to join the tables properly, and yes a group of us have tried to explain it many times. As I work on the code that they wrote I find I tend to remove a lot of the CTE's. I find that CTE's can be useful but they are also over used by some people.ill be honest when is started this thread this is the kind of answer I was hoping for
I am a contractor myself but I don't over use CTEs, in fact I believe they are for use at specific times and one of those times isn't to replace a sub query that is only used once in your code
i'm not saying this is the gospel or the way I do things is the way it should be done, im just saying I cant personally see how this type of use of CTEs really has any benefit at all in a sql query, particularly not with readability
It all comes down to preference and skill level as a team, an individual can write what ever and how ever, its when a team comes into play that the lead must make some rules as to when to use CTEs and when not to.. If the whole team can read subqueries better, then there you go. I'm more of a team lead and have to balance out my team's skill levels to create the right atmosphere for everyone to read all the queries quickly. There are instances for each option.
In and ideal world/team you would have such 'rules', but good luck enforcing that. Back in my COBOL days we had strict coding rules, but you still found code slip into production that didn't completely follow the rules. SQL, IMHO, is more of a personal preference in coding, you will be lucky to find two people who like to format their code the same way. I've found a lot of people I work with and contractors that all seem to prefer no formatting, indenting, and so on. When you have a ton of projects waiting in the wings corporate doesn't want you spending the time to follow some coding rules, just get it done, and working correctly.
you "sound" defensive. I mean no harm. I forget how literal text can "Sound", rules, guidance, support, pointing towards. I'm no dictator, but I do try to enforce teamwork and Foster conversation. Yes, you can't tell people what to do and how to do it, but, we can work together to come up with "Standard rules" we'd like to abide by. Luck is trivial.
My personal opinion in the end is, use what is comfortable to you, get as complex or as simplistic as you'd like. Skill is not defined by how you write a query, but by how you solve the right problem.
January 6, 2016 at 8:40 am
below86 (1/6/2016)
Lynn Pettis (1/6/2016)
jaime.simancas 27970 (1/6/2016)
erics44 (1/6/2016)
below86 (1/6/2016)
I've had to work with a group of contractors for over a year now, and I find their use of CTE's to be excessive, and as you mentioned most are unnecessary. I chalk a lot of it up to these people truly don't understand the data and how to join the tables properly, and yes a group of us have tried to explain it many times. As I work on the code that they wrote I find I tend to remove a lot of the CTE's. I find that CTE's can be useful but they are also over used by some people.ill be honest when is started this thread this is the kind of answer I was hoping for
I am a contractor myself but I don't over use CTEs, in fact I believe they are for use at specific times and one of those times isn't to replace a sub query that is only used once in your code
i'm not saying this is the gospel or the way I do things is the way it should be done, im just saying I cant personally see how this type of use of CTEs really has any benefit at all in a sql query, particularly not with readability
It all comes down to preference and skill level as a team, an individual can write what ever and how ever, its when a team comes into play that the lead must make some rules as to when to use CTEs and when not to.. If the whole team can read subqueries better, then there you go. I'm more of a team lead and have to balance out my team's skill levels to create the right atmosphere for everyone to read all the queries quickly. There are instances for each option.
I find CTEs help when writing complex queries. They allow you to build up a query in steps, finding and isolating the data you need to complete the actual query. Once done, then you can go back and optimize the query eliminating CTEs where appropriate, while still having the original query to compare results to ensure you still return the correct results.
I tend to follow what you are saying, I just tend to use tables or temp tables to 'step' through it. And once you get the correct results a lot of these tables or temp tables tend to stay in the code. At least where I work, you don't have time, or much time to rework the code.
thing is
I always have multiple query windows open
if I need to create/test a dataset away from my main query ill more often than not do it in a separate window
obviously ever situation is different and this isn't always ideal
January 6, 2016 at 8:42 am
below86 (1/6/2016)
jaime.simancas 27970 (1/6/2016)
erics44 (1/6/2016)
below86 (1/6/2016)
I've had to work with a group of contractors for over a year now, and I find their use of CTE's to be excessive, and as you mentioned most are unnecessary. I chalk a lot of it up to these people truly don't understand the data and how to join the tables properly, and yes a group of us have tried to explain it many times. As I work on the code that they wrote I find I tend to remove a lot of the CTE's. I find that CTE's can be useful but they are also over used by some people.ill be honest when is started this thread this is the kind of answer I was hoping for
I am a contractor myself but I don't over use CTEs, in fact I believe they are for use at specific times and one of those times isn't to replace a sub query that is only used once in your code
i'm not saying this is the gospel or the way I do things is the way it should be done, im just saying I cant personally see how this type of use of CTEs really has any benefit at all in a sql query, particularly not with readability
It all comes down to preference and skill level as a team, an individual can write what ever and how ever, its when a team comes into play that the lead must make some rules as to when to use CTEs and when not to.. If the whole team can read subqueries better, then there you go. I'm more of a team lead and have to balance out my team's skill levels to create the right atmosphere for everyone to read all the queries quickly. There are instances for each option.
In and ideal world/team you would have such 'rules', but good luck enforcing that. Back in my COBOL days we had strict coding rules, but you still found code slip into production that didn't completely follow the rules. SQL, IMHO, is more of a personal preference in coding, you will be lucky to find two people who like to format their code the same way. I've found a lot of people I work with and contractors that all seem to prefer no formatting, indenting, and so on. When you have a ton of projects waiting in the wings corporate doesn't want you spending the time to follow some coding rules, just get it done, and working correctly.
Redgate's SQL Promt <Format Query> comes to mind.
January 6, 2016 at 8:45 am
jaime.simancas 27970 (1/6/2016)
Skill is not defined by how you write a query, but by how you solve the right problem.
hmmmmmm, seems like a very profound thing to say
and at first I though, yeah I totally agree with that
then thinking about it, I don't really, its doesn't really ring true
you can get the perfect results, even with a query that performs well but if its unmaintainable due to (for example) hundreds of prefixing CTEs 🙂 - does that mean you are as skilled as a developer who solves the same problem with a query that is well structured and performs the same
so how you solve the problem can be judged by how you write the query
January 6, 2016 at 8:49 am
erics44 (1/6/2016)
Lynn Pettis (1/6/2016)
One thing that would be nice is if Microsoft implemented CTEs more like the way Oracle implemented their sub-query refactoring clause. Oracle can treat the sub-query refactoring clause as inline code or it can use it as a temporary table running it only once when used multiple times in a query. A CTE in SQL Server is run separately for each use in a single query.is that right? SQL recompiles the CTE each time it is used?
Depends on what eaxactly it is you ask.
If you are asking about compilation (turning a query text into a query plan), then the answer is that every query, with or without CTEs, may or may not have to be compiled before execution. SQL Server uses the plan cache to reuse execution plans if the same query is submitted again, but there are lots of caveats involved. Too much to describe here, there are one-hour sessions about purely the plan cache at conferences and they are still incomplete.
If you are asking about execution, i.e. whether the data and computation required to get the CTE, then the answer is a solid it depends. When compiling the execution plan, the optimizer will look at the whoile. So if a CTE joins tables 1, 2, and 3; and the main query joins table 4 to the CTE, then the compiler might well decide that table 2 is not actually needed and that the fastest plan is to first join tables 1 and 4, and then join 3.
When a single CTE is referenced multiple times, then each reference gets similarly optimized. So the second reference might enable the optimizer to leave out table 1 but now it cannot exclude 2 anymore.
In theory it is also possible that the optimizer decides to first get the results of the CTE, store them in a worktable (called a Spool in the execution plan), and then reuse the data from there. I don't know if that happens often; I do know that it does not happen all the time, I have seen execution plans where the (mostly) same steps were included twice because a CTE was referenced twice. Also, be aware that executing the logic once and reusing the results may seem to be a good idea but that doesn't have to be so - it defies the type of optimizations where parts are left out because they are not needed in that spot, and you also have to be aware that a Spool operator in itself is not very cheap.
January 6, 2016 at 9:03 am
jaime.simancas 27970 (1/6/2016)
below86 (1/6/2016)
jaime.simancas 27970 (1/6/2016)
erics44 (1/6/2016)
below86 (1/6/2016)
I've had to work with a group of contractors for over a year now, and I find their use of CTE's to be excessive, and as you mentioned most are unnecessary. I chalk a lot of it up to these people truly don't understand the data and how to join the tables properly, and yes a group of us have tried to explain it many times. As I work on the code that they wrote I find I tend to remove a lot of the CTE's. I find that CTE's can be useful but they are also over used by some people.ill be honest when is started this thread this is the kind of answer I was hoping for
I am a contractor myself but I don't over use CTEs, in fact I believe they are for use at specific times and one of those times isn't to replace a sub query that is only used once in your code
i'm not saying this is the gospel or the way I do things is the way it should be done, im just saying I cant personally see how this type of use of CTEs really has any benefit at all in a sql query, particularly not with readability
It all comes down to preference and skill level as a team, an individual can write what ever and how ever, its when a team comes into play that the lead must make some rules as to when to use CTEs and when not to.. If the whole team can read subqueries better, then there you go. I'm more of a team lead and have to balance out my team's skill levels to create the right atmosphere for everyone to read all the queries quickly. There are instances for each option.
In and ideal world/team you would have such 'rules', but good luck enforcing that. Back in my COBOL days we had strict coding rules, but you still found code slip into production that didn't completely follow the rules. SQL, IMHO, is more of a personal preference in coding, you will be lucky to find two people who like to format their code the same way. I've found a lot of people I work with and contractors that all seem to prefer no formatting, indenting, and so on. When you have a ton of projects waiting in the wings corporate doesn't want you spending the time to follow some coding rules, just get it done, and working correctly.
you "sound" defensive. I mean no harm. I forget how literal text can "Sound", rules, guidance, support, pointing towards. I'm no dictator, but I do try to enforce teamwork and Foster conversation. Yes, you can't tell people what to do and how to do it, but, we can work together to come up with "Standard rules" we'd like to abide by. Luck is trivial.
My personal opinion in the end is, use what is comfortable to you, get as complex or as simplistic as you'd like. Skill is not defined by how you write a query, but by how you solve the right problem.
Sorry, I didn't mean to come off as defensive, I was just trying to say we have tried at times to have standards or rules, they just seem to gradually fade from everyone's mind, and is hard to enforce. Then again we are human, I'm sure I've strayed from these at times, if not more than that.;-)
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 6, 2016 at 9:05 am
erics44 (1/6/2016)
jaime.simancas 27970 (1/6/2016)
Skill is not defined by how you write a query, but by how you solve the right problem.
hmmmmmm, seems like a very profound thing to say
and at first I though, yeah I totally agree with that
then thinking about it, I don't really, its doesn't really ring true
you can get the perfect results, even with a query that performs well but if its unmaintainable due to (for example) hundreds of prefixing CTEs 🙂 - does that mean you are as skilled as a developer who solves the same problem with a query that is well structured and performs the same
so how you solve the problem can be judged by how you write the query
My opinion comes from the wide range of people I have had the privileged to work with, in all honesty, most businesses can't afford highly trained experts at writing perfect if not genius SQL. But, I remember I had a boss once, who always wanted the one up, I don't want that, I just want to express an opinion that it's best just to work with the team you have and help them write better code if they are not skilled in "code" writing, and learn from their problem solving skills as that is what really matters. I don't believe in the "Get r Done" mentality, it's just, uneducated.
oh yes, and thank you for such a wonderful and very passionate conversation.
Viewing 15 posts - 46 through 60 (of 161 total)
You must be logged in to reply to this topic. Login to reply