Are the posted questions getting worse?
-
GSquared (12/16/2010)
Brandie Tarvin (12/16/2010)
So, I have this query that yesterday is working fine and quick when I left outer join three tables. But when I change them over to inner joins takes forever to run before killing the tempdb. Cannot for the life of me figure out what's wrong. Today, I take a look at the estimated execution plan and find one of the inner joins saying "Warnings: No Join Predicate".I'm freaking out here because I know the key I joined on exists in both tables 2 & 3 and why won't the damn thing realize it has a predicate?
So I move the three joins to a separate query on their own (away from the rest of the query) to see what's going on. I nix the first table because that's not where the problem is, try to run a simple select on my join columns...
And get a warning that alias used on my third (now second) join doesn't exist. Because, apparently, I joined table 2 back to table 1 instead of table 3 (all of which have the same column in them).
<headthunk>
It's going to be one of those weeks, isn't it?
I used to know a DBA who insisted on using 1-letter aliases for all tables in queries, because it made them "faster to type" in the other parts of the query. One day, he brought the production server to its knees because he got lost in his aliases, and joined A to B on A.Col = A.Col. On two multi-million row tables.
I wonder if he's still doing that now that there's Intellisense. I like aliases, even one letter ones, but they have to be representative of the table they're aliasing.
If he was using A B C, was he from an Oracle background?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams -
The Dixie Flatline (12/15/2010)
You tell 'em, Steve !!!I wander in here for the first time in a month and get bombarded with utterly useful technical information.
Now I know how a groundhog feels when he sees his shadow:
Back to hibernation for me!
I'll try again next year.
P.S. Alvin, good luck with the Hula-Hoop thing. Maybe this year will be the year.
Thank you sir. I'm heading up to the Great Wet White North for Xmas. Hopefully customs won't take the hula-hoop away. 😉
Alvin Ramard
Memphis PASS Chapter[/url]All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
-
Stefan Krzywicki (12/16/2010)
GSquared (12/16/2010)
Brandie Tarvin (12/16/2010)
So, I have this query that yesterday is working fine and quick when I left outer join three tables. But when I change them over to inner joins takes forever to run before killing the tempdb. Cannot for the life of me figure out what's wrong. Today, I take a look at the estimated execution plan and find one of the inner joins saying "Warnings: No Join Predicate".I'm freaking out here because I know the key I joined on exists in both tables 2 & 3 and why won't the damn thing realize it has a predicate?
So I move the three joins to a separate query on their own (away from the rest of the query) to see what's going on. I nix the first table because that's not where the problem is, try to run a simple select on my join columns...
And get a warning that alias used on my third (now second) join doesn't exist. Because, apparently, I joined table 2 back to table 1 instead of table 3 (all of which have the same column in them).
<headthunk>
It's going to be one of those weeks, isn't it?
I used to know a DBA who insisted on using 1-letter aliases for all tables in queries, because it made them "faster to type" in the other parts of the query. One day, he brought the production server to its knees because he got lost in his aliases, and joined A to B on A.Col = A.Col. On two multi-million row tables.
I wonder if he's still doing that now that there's Intellisense. I like aliases, even one letter ones, but they have to be representative of the table they're aliasing.
If he was using A B C, was he from an Oracle background?
Nope. Just thought it made the typing easier. Never got to see him use intellisense in SSMS, so no clue what he does these days.
- 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
-
Jeff Moden (12/15/2010)
Steve Jones - SSC Editor (12/14/2010)
Is it now more, or less, frightening?It depends... where's your other hand? 🙂
A knife, a real knife
-
Stefan Krzywicki (12/16/2010)
I wonder if he's still doing that now that there's Intellisense. I like aliases, even one letter ones, but they have to be representative of the table they're aliasing.
If he was using A B C, was he from an Oracle background?
I like 1-2 letter alias, but I tend to use the table names, like PD for ProductDetail, C for Customer, CA for CustomerAddress, etc.
I think Intellisense can make this just as bad. If you're used to using "p", downarrow to get to ProductDetail and someone adds a new table, you might end up with some weird joins. Hopefully that gets caught, but I find that Intellisense messes me up as often as it helps.
-
Steve Jones - SSC Editor (12/16/2010)
I think Intellisense can make this just as bad. If you're used to using "p", downarrow to get to ProductDetail and someone adds a new table, you might end up with some weird joins. Hopefully that gets caught, but I find that Intellisense messes me up as often as it helps.Intellisense annoys me. I type too fast for it and end up with either the wrong object or having to type extra characters to get out of it. So I've given up on it and turned it off.
-
Brandie Tarvin (12/16/2010)
Steve Jones - SSC Editor (12/16/2010)
I think Intellisense can make this just as bad. If you're used to using "p", downarrow to get to ProductDetail and someone adds a new table, you might end up with some weird joins. Hopefully that gets caught, but I find that Intellisense messes me up as often as it helps.Intellisense annoys me. I type too fast for it and end up with either the wrong object or having to type extra characters to get out of it. So I've given up on it and turned it off.
You too, 'eh? The other thing that annoys me is you have to build the from clause first before doing the select, or it won't behave for things like bc.<column>. I hit period and end up with strangeness.
The few times I've used it, I've ended up in the options playing hide and seek for the 'off' switch.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
I like the Red Gate intellisense more than the SSMS default, but I have to admit, they both get in the way as often as they help, till you get used to them.
I've always thought From should come before Select anyway. Makes more sense to me. So starting with "Select * From", building the From clause, and then going back and building the Select clause, and getting Intellisense prompts that way, seems okay to me.
- 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
-
Brandie Tarvin (12/16/2010)
Steve Jones - SSC Editor (12/16/2010)
I think Intellisense can make this just as bad. If you're used to using "p", downarrow to get to ProductDetail and someone adds a new table, you might end up with some weird joins. Hopefully that gets caught, but I find that Intellisense messes me up as often as it helps.Intellisense annoys me. I type too fast for it and end up with either the wrong object or having to type extra characters to get out of it. So I've given up on it and turned it off.
I have the same problem. It is very frustrating at times. I have found that Intellisense screws with me more if I use just single character aliases.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
WayneS (12/15/2010)
My second ever QotD is being published tomorrow. Can you'll let me know what you think of it please?Wayne I believe it was a good question, and I for one of many people learned from it. I did not answer correctly, but garnishing points is not the award, learning is the true purpose of the QOD and in that regard many, many people benefited from it.
Thanks for a good QOD.
-
Craig Farrell (12/16/2010)
Brandie Tarvin (12/16/2010)
Steve Jones - SSC Editor (12/16/2010)
I think Intellisense can make this just as bad. If you're used to using "p", downarrow to get to ProductDetail and someone adds a new table, you might end up with some weird joins. Hopefully that gets caught, but I find that Intellisense messes me up as often as it helps.Intellisense annoys me. I type too fast for it and end up with either the wrong object or having to type extra characters to get out of it. So I've given up on it and turned it off.
You too, 'eh? The other thing that annoys me is you have to build the from clause first before doing the select, or it won't behave for things like bc.<column>. I hit period and end up with strangeness.
The few times I've used it, I've ended up in the options playing hide and seek for the 'off' switch.
Yup, it just takes some getting used to and if you alias the tables and use them to preface every single column in the SELECT you don't end up with anything not in that table as a possible result.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams -
GSquared (12/16/2010)
I like the Red Gate intellisense more than the SSMS default, but I have to admit, they both get in the way as often as they help, till you get used to them.I've always thought From should come before Select anyway. Makes more sense to me. So starting with "Select * From", building the From clause, and then going back and building the Select clause, and getting Intellisense prompts that way, seems okay to me.
I try to start with the from clause since it seems to help out a bit. But, it doesn't always hold true.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
CirquedeSQLeil (12/16/2010)
GSquared (12/16/2010)
I like the Red Gate intellisense more than the SSMS default, but I have to admit, they both get in the way as often as they help, till you get used to them.I've always thought From should come before Select anyway. Makes more sense to me. So starting with "Select * From", building the From clause, and then going back and building the Select clause, and getting Intellisense prompts that way, seems okay to me.
I try to start with the from clause since it seems to help out a bit. But, it doesn't always hold true.
Gotta agree with that. Even being used to them, they only barely increase productivity, and even then, it's mainly because of having to deal with poorly and/or obscurely named database objects, where the drop-down helps.
- 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
-
I know I'm biased, but I really do like SQL Prompt and the way it works. I think SSMS intelisence is a good idea, poorly executed. Red Gate's product on the other hand really does help me both while typing the code and when it comes time to format it. Yes, you do actually have to adjust to using it, which is a pain, and every once in a long while, I'll turn it off because I'm writing some odd-ball piece of code that the stupid thing is getting in the way of, but overall... it's from heaven.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
GSquared (12/16/2010)
CirquedeSQLeil (12/16/2010)
GSquared (12/16/2010)
I like the Red Gate intellisense more than the SSMS default, but I have to admit, they both get in the way as often as they help, till you get used to them.I've always thought From should come before Select anyway. Makes more sense to me. So starting with "Select * From", building the From clause, and then going back and building the Select clause, and getting Intellisense prompts that way, seems okay to me.
I try to start with the from clause since it seems to help out a bit. But, it doesn't always hold true.
Gotta agree with that. Even being used to them, they only barely increase productivity, and even then, it's mainly because of having to deal with poorly and/or obscurely named database objects, where the drop-down helps.
I haven't used the Intellisense in SSMS at all. But from my short experience with it in Visual Studios, I can see that it might be a help when you're learning the language so you can make sure you have all the parts of the command and stuff that you need. But, on the other hand, you can also use it so that you don't learn the language and depend on the prompts and then when you go to a shop without it, you're dead in the water. Goes both ways, I suppose.
Since I type pretty fast, the Intellisense gets in the way. Guess I'll just have to wait for the mind-reading Intellisense that figures out what I'm trying to do and then does it, rather than going by that passe "I'm doing what you're typing/telling me to do". 😀
-- Kit
Viewing 15 posts - 22,546 through 22,560 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply