August 10, 2015 at 1:27 pm
I'm sorry, the ON clause shouldn't be there.
When you changed the code to include this:
FROM cteClients c
CROSS JOIN dbo.dimDate d where c.DATESERV = d.DateFull
You actually converted the CROSS JOIN into an INNER JOIN.
Check the corrected version in my post as I edited it to prevent the error.
August 10, 2015 at 2:05 pm
Thanks Luis
This is getting what I need .
It's great that it works and I'm sure I can muttle my way thru to understand, but do you have any suggestions on reading I can do ?
Thanks Again
Joe
August 10, 2015 at 2:12 pm
The easiest way is to understand what each CTE is doing. Just go step by step.
The first one is your original query. The second one is a list of clients. The third one creates a cartesian product for clients and dates, in other words, you get one row for each combination of client and date. The last part is just the outer join with your original query.
August 10, 2015 at 2:15 pm
Yes, so it a way(my weird way of thinking) its sort of like creating a series of temp tables, but more effective ?
August 10, 2015 at 2:21 pm
More like inline views or single use views. You could also think of them as rearranged subqueries.
The engine will process all of this as a single statement, so you want to be sure that you're not creating it too complex or it might affect the performance. If this happens, you can insert some of the CTEs into temp tables to divide and conquer. 😉
I hope I'm not confusing you more.
August 10, 2015 at 2:23 pm
:w00t::w00t:
No I like being confused , it helps me think more!!
August 10, 2015 at 2:26 pm
Luis Cazares (8/10/2015)
sgmunson (8/10/2015)
Luis Cazares (8/10/2015)
sgmunson (8/10/2015)
Given the rather small size of the likely range of values here, any suffered performance is likely to be irrelevant, and the recursive CTE is a lot easier to understand, so I'm going to fall back on Jeff Moden's signature line: "Just because I can do something in T-SQL, doesn't mean I shouldn't", as I'll take readability over irrelevant performance hits every day of the week. I understand where you're coming from, and if this user's application of the code will be in a heavy volume OLTP application, then you are exactly right. If it's reporting, as it appears to be, this shouldn't be an issue.I know that performance might not be an issue here, but it's a matter of habits. If you're used to the cross join style tally table generator, you won't suffer to understand it and will be able to write it easily for any amount of data needed. And if you use some 3rd party add-ins, you can create templates or snippets to generate the code immediately.
As an absolute rule, I do not get into "habits", where SQL coding is concerned. I code based on the need and take the entire requirement into account, including performance characteristics as well as maintainability. Doing otherwise tends to create problems.
So you're saying that you choose to use a poor performance option just because you expect to have a low number of rows?
That's exactly the reason that most developers write crappy SQL code (and I'm not referring to you). They test over a small number of rows and don't realise that the number of rows will grow and performance will suffer.
When you share code in a public forum, anyone can take it and might apply it to a larger set of rows than what it was intended for. That's why it's preferred to give the best option available instead of the one that's "good enough".
When needed to avoid a maintainability problem, yes, and that's assuming I'm sure that the usage will NOT be in a situation where record count will expand to the point where performance will suffer. It saves on support call like you wouldn't believe. Sometimes you have to provide code that can be easily understood by people who just aren't ever going to understand anything else, and it's more common than I'd like, but I can't begin to tell you how much grief that avoids. If they don't understand the code, they get tempted to play with it until they do, and the support call comes right at the point where they think they understand it, but don't, and have changed it into something that no longer works and has caused some other fatal flaw that needs data massage to resolve. Then they get mad at me for introducing "rocket science". If you think working for a company with problem developers is tough, try being a contractor... and working with managers that don't realize how incompetent their developers are, and aren't in any way interested in receiving such a message. Alternatively, try putting said same "rocket science" into an environment where the business understands you quite well, but the IT department has standards that prevent you from doing things in any way that remotely resembles common sense...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 11, 2015 at 8:16 am
sgmunson (8/10/2015)
Given the rather small size of the likely range of values here, any suffered performance is likely to be irrelevant, and the recursive CTE is a lot easier to understand, so I'm going to fall back on Jeff Moden's signature line: "Just because I can do something in T-SQL, doesn't mean I shouldn't", as I'll take readability over irrelevant performance hits every day of the week. I understand where you're coming from, and if this user's application of the code will be in a heavy volume OLTP application, then you are exactly right. If it's reporting, as it appears to be, this shouldn't be an issue.
I have to say that no performance hit is "irrelevant" because no one has control over the future size of the data or how many times something will be used nor where it will be used if someone picks up on the code to solve a different problem. One of my specialties is fixing "irrelevant" performance hits that are crippling the server. If you're using an rCTE that counts, then you've most likely made a mistake because even a WHILE loop will be faster and use fewer resources.
My quote is based on doing tasks in SQL Server that most people would write external code for... it's not meant to be used as an excuse to justify poorly performing code by citing supposedly guaranteed low row counts. If you look at the following article, you'll also see that rCTEs that count suffer greatly even at low row counts.
http://www.sqlservercentral.com/articles/T-SQL/74118/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply