March 6, 2009 at 1:27 pm
Mangal9i (3/6/2009)
AVoid making comments apart from technical stuff, as Bob's comments about "Comment" was not in good taste.
John Smith
March 6, 2009 at 1:29 pm
Is there can be any logical reasoning for liking something?
John Smith
March 6, 2009 at 1:38 pm
Mangal9i (3/6/2009)
Is there can be any logical reasoning for liking something?
Yes. Personal aesthetic preferences are purely subjective. I like chocolate. Even if you don't, that makes neither of us "wrong".
Engineering solutions aren't particularly subjective. Would I be "wrong", or a "cruddy DBA", if I used global cursors on global temp tables for ALL of my solutions, and justified it as "I like them"? Yes, I most certainly would be a really bad DBA. I think just about everyone who knows anything about SQL Server would agree with that.
On the other hand, there have been times where I've deliberately implemented less "perfect" code, because it would have ended up being unmaintanable by the vast majority of DBAs. A slight performance hit, in order to make the code more maintainable, easier to document, etc., is not a subjective decision, it's one that can be based on cold, hard analysis. My previous employer is saving a lot of money because I made that decision based on actual facts gathered from the actual people who are charged with maintaining that code.
So, do you have a cold, hard analytical reason to use the recursive CTE instead of the more efficient, faster solution? Something that can be measured and verified? That's what I'm after here. That's what I want to know.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 6, 2009 at 1:43 pm
Mangal9i (3/6/2009)
Is there can be any logical reasoning for liking something?
I would think there would have to be correct? If I like coffee better than tea for example, it would be required of me to have a reason for that.
As a DBA there should be a reason for liking a particular solution over another and that will in most cases be performance, unless there are some other requirements that force you to take something that doesn't perform as well in order to satisfy those requirements (and even then that would necessitate some discussion).
Hopefully you understand my point.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 6, 2009 at 1:48 pm
People will call me rude if I don't reply you.
You talked about Global Cursor, I don't like them, I never wrote a single cursor in my life.
Why I like CTE? There 100 reasons, don't take this example only. As they keep me away from cursors.
What else?
You din't say anything about credentials?
John Smith
March 6, 2009 at 1:51 pm
Mangal9i (3/6/2009)
GSquared (3/6/2009)
I'm surprised, with your credentials, .
I most certainly am surprised. You have top-notch credentials. Doesn't get better than "Most Valuable Player", which is what "MVP" means. I would expect a "Most Valuable Player" to know a bit about how execution plans work.
I'm not one, and I know that. I expect MVPs to know stuff I don't. That's why they are MVPs and I'm not. Doesn't mean I expect you to know everything about SQL Server. No single person knows that. But execution plan analysis is pretty basic. It's pretty much the key to everything that has anything to do with performance tuning. And since performance tuning is what you're offering advice on, I'd expect you to either know the key, basic data, or to start out with "This isn't my area of expertise".
A lot of people come to me for advice on writing T-SQL and on database architecture. I'm pretty good at those two things. There are better, but I'm pretty good. On those subjects, I just offer the advice.
On the other hand, if someone comes to me for advice on XML, I'm just barely good enough to get to the job done most of the time, usually with a lot of digging around on various websites and in BOL. So, when I offer advice on that, I start out with "I'm far from an expert on XML, but I think...".
See the difference?
If your area of expertise is BI, cool. In that case, I'd expect your BI advice to be spot-on, top-of-the-line, game-winning advice. But I'd also expect that you would be clear when offering advice on things you aren't an expert in. You have credentials that will give what you say a lot of weight and importance, so you need to offset that weight in areas where the credentials may not apply.
That's just good sense, so far as I can tell.
To make it less personal, perhaps, let's say you had a medical condition about your heart. So you go see a doctor, and he tells you, "you need to take 2 aspirin every morning, and 1 at dinner time, and you should be fine". Would you take his advice? Of course you would, he's a doctor! What if you found out later that he's a podiatrist (foot and ankle specialist) and doesn't really know much about hearts and lungs and such? Would you be upset that he gave you advice and didn't tell you to go see someone more qualified? Of course you would!
That's my point. That's why I was surprised that you didn't know basic concepts about execution plans. You're the SQL equivalent of, not just a doctor, but a "Most Valuable Player". MOST Valuable Player. Top honors. Doesn't get better than that.
As an example of this, Gail, who is also an SQL MVP, offers excellent advice on performance tuning. Top of the line. Doesn't get better. Always spot-on and very clear and easy to understand. But when it comes to something like SSRS or SSIS, she either stays away from the subject, or leads in with a disclaimer, because she's weak on those and knows it.
Does that make it more clear?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 6, 2009 at 1:57 pm
Mangal9i (3/6/2009)
People will call me rude if I don't reply you.You talked about Global Cursor, I don't like them, I never wrote a single cursor in my life.
Why I like CTE? There 100 reasons, don't take this example only. As they keep me away from cursors.
What else?
You din't say anything about credentials?
I use CTEs for what they are good for. They work well as a replacement for derived tables. They can sometime work quite well for breaking processes up into smaller pieces that are easier to understand and/or easier for the server to get done. And they are the best method I know of for resolving adjacency hierarchies.
But I don't use them when there are better solutions available. Why would I do that?
If you need to drive a nail into a piece of wood, and you have a hammer and a screwdriver on your toolbelt, would you choose the screwdriver, because you "like it better", and because "it's very useful for lots of things", or would you use the hammer? There are psychotics who will use the screwdriver, but anyone sane will use the hammer, at least after someone demonstrates that it's better at driving nails. And, when they need to instal/remove a screw, they'll switch to the screwdriver, and be right.
That's not a question of "like" or "dislike", that's a question of "right" or "wrong", by the usual standards of those tools.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 6, 2009 at 1:58 pm
Coffee, chocolate...
See in my job I hardly write a sql query. I'm more into cube desiging, DW design, ETL strategy.
But still I participate on sql forums, as I seriously like SQL.
A "select" makes me feel good. I participate as its kind of my passion, no reasons out there.
So for me SQL and CTE is like a chocolate.
So i won't be able to satisfy you with my answer why I like CTE?
No reasons. I also do peotry, does that explain?
John Smith
March 6, 2009 at 2:04 pm
When we are speaking in the realm of personal tastes, coffee, chocolate, poetry, etc we can have our likes expressed freely (albeit carefully) but when we come to the world of technology there are tangible measures which should drive our preferences and when it comes to t-sql it has to be performance, scalable performance. Which, of course, as many have already stated can and should be measured and taken into consideration with every decision that we make.
Know what I mean?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 6, 2009 at 2:50 pm
Mangal,
Justify for me why I should use your recursive CTE in place of Bob's solution. I have tested the two side by side, and I find Bob's solution to be superior in performance. It uses 10% of the CPU time yours uses, and that is generating dates over a 1004 year period.
Both solutions are easily understood and maintainable, so there are two reasons not to use the recursive cte.
March 6, 2009 at 3:05 pm
You should use Bob's solution, best way create Time dimension and use it.
One question came, I given 1 solution, which is not that bad, another person gives another solution, which is better, use it. Isn't that simple?
That's why rating and "moderation" is important. OP can get wrong, moderator/experts steps in.
But without marking it is difficult to find better solution, when people do a google search.
On any thread there are 4-5 differnt solution given everytime, one can be better others can't, why so much of noise about it?
John Smith
March 6, 2009 at 3:16 pm
CTE (3/6/2009)
why so much of noise about it?
So people don't use the ones that cause performance problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2009 at 3:22 pm
CTE (3/6/2009)
You should use Bob's solution, best way create Time dimension and use it.One question came, I given 1 solution, which is not that bad, another person gives another solution, which is better, use it. Isn't that simple?
That's why rating and "moderation" is important. OP can get wrong, moderator/experts steps in.
But without marking it is difficult to find better solution, when people do a google search.
On any thread there are 4-5 differnt solution given everytime, one can be better others can't, why so much of noise about it?
Not the request I made. I know which I'd use. I want Mangal to justify to me why I should use his solution even though it is less efficient.
March 6, 2009 at 4:08 pm
Lynn Pettis (3/6/2009)
Not the request I made. I know which I'd use. I want Mangal to justify to me why I should use his solution even though it is less efficient.
Look back at the posts in this thread... CTE = Mangal.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2009 at 6:32 pm
I take the afternoon to focus on my day job and come back to find all this? My oh my.
Thanks to all y’all who supported “my” solution about producing a string of dates. Of course it’s not really “my” solution. Jeff taught me everything I know about tally tables, and I think it was Itzik Ben-Gan who came up with the CTE-generated numbers/tally table. But I’m really feeling the love, y’know?
I hold these truths to be self-evident:
1. CTEs are easier to follow than nested subqueries because they read from the top down.
2. Recursive CTEs are briefly entertaining, but their performance ain't that great.
3. Generally speaking, solutions that run faster are preferable to solutions that run slower.
4. If you want to produce a sequential string of any pattern, it's gonna be hard to beat using a tally table.
(But yes, Jeff, the FOR XML trick for delimited strings is still in my toolbox.)
5. Frozen porkchops hurt more than thoroughly cooked ones.
6. Expertise in one area does not translate into expertise in all areas.
7. Neither wisdom, nor common sense, nor additional IQ points are handed out with any degree, certificate, or credential.
8. It is better to hold one's tongue and be thought a fool than to open one's mouth to advertise the fact.
(I really do believe this, I'm just too foolish to practice it.)
9. Always store beer in a cool, dark place.
10. Rub her feet.
11. Lynn has a good chance of identifying whose notebooks I swiped those last two from.
12. The race is not always to the swift, nor the battle to the strong. But that's how you bet.
13. You can lead a horse to water but you can't make him drink.
14. There are some things a man should learn not to do against the wind or up a rope.
15. Laughter is not the best medicine. But sometimes it's the only one available.
16. I like Kipling.
This is from The Jungle Book.
His spots are the joy of the Leopard: his horns are the Buffalo's pride –
Be clean, for the strength of the hunter is known by the gloss of his hide.
If ye find that the Bullock can toss you, or the heavy-browed Sambhur can gore;
Ye need not stop work to inform us; we knew it ten seasons before.
Oppress not the cubs of the stranger, but hail them as Sister and Brother,
For though they are little and fubsy, it may be the Bear is their mother.
"There is none like to me!" says the Cub, in the pride of his earliest kill;
But the Jungle is large and the Cub he is small.... Let him think and be still.
-- Maxims of Baloo
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 31 through 45 (of 55 total)
You must be logged in to reply to this topic. Login to reply