| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-11-17 : 19:50:18
|
| My apologies for taking so long to post this. I've finally uploaded the PowerPoint slides from "What I Wish Developers Knew About SQL Server". I presented this at PASS 2005 in Dallas, the Kansas City SQL Server Users Group, the Kansas City .NET SIG, a couple of clients and any other group I could find that would listen to it. All the deliveries really helped make the PASS presentation smooth and polished. Article Link. |
|
|
karen_watterson
Starting Member
1 Post |
Posted - 2005-11-18 : 11:20:45
|
| Thanks for sharing the slides (and wisdom), Bill! I can see why it won the PASS 2005 Best Presentation award and suggest you consider doing a podcast (or otherwise capturing a "live" presentation as an audio file) next time you present. You're so modest that you didn't mention that folks can also download your excellent PASS 2004 slides on Profiler and SQLTrace in SQL 2005 from http://www.sqlteam.com/downloads/pass2004_306.zip.PS I blogged about this at http://blog.pinpub.com/sqlblog/.Karen |
 |
|
|
mattb
Starting Member
5 Posts |
Posted - 2005-11-20 : 11:13:30
|
| Great info, Graz. One item in the presentation caught my attention that I hope you can expand upon. You mention that the following syntax isn't preferrable:WHERE Col=COALESCE(@Var, Col)and, instead, you like to see:WHERE (@Var is NULL or Col = @Var)I often use the first syntax when creating queries that will be called from different areas of an application where one request needs to restrict based on @Var and the other does not. The alternative, creating two separate queries or creating two very similar logic blocks in the same proc tends to become a maintenance nightmare since you have to change both as your schema changes or the data required by the app changes. Furthermore, I'll even use the following syntax on occasion when Col accepts NULLs and I want to include those in the resultset:WHERE IsNull(Col, '') = COALESCE(@Var, Col, '')I'm interested to hear your experience on this approach and why you recommend against it (performance, readability, other...).Matt |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2005-11-20 : 11:17:00
|
| The biggest problem is see is in performance. I don't know that either really does what I want though. If you use the first example if won't use the index in the query even if you do pass it a parameter. For example, if you're wrapping the COALESCE around the CustomerID and you pass in a CustomerID it won't use any index on CustomerID. I'd encourage you to test it yourself and verify those results. QA will allow you to display the actual query plan that's executed.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-11-20 : 11:22:51
|
When using (@Var is NULL or Col = @Var), the optimizer can do two things the other versions can't support:1. Short-circuit evaluation of the OR condition (if @var is null, then the rest of the expression does not need to be evaluated)2. Allow the optimizer to use indexes on ColThe last syntax you posted cannot use an index because the searchable expression is wrapped in the IsNull() function. While the COALESCE() syntax is logically equivalent to the preferred syntax, it can't do a logical short-circuit, and may end up choosing a less optimal plan.Yeah, what he said!  |
|
|
|
mattb
Starting Member
5 Posts |
Posted - 2005-11-20 : 20:40:15
|
| I feel silly for not recognizing that out the gate, great points! I ran through several scenarios and the query plans support your assertions. Time to update my toolbox.Thanks guys! |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2005-11-21 : 08:09:12
|
Thats a nice persentation with some useful ideas You mention thisIF EXISTS (SELECT * …)To check for existance, which It certainly does, Coming from a Sybase System 10/ SQL Server 4.3 background I have always usedIF EXISTS (SELECT 1 …)As that is what I was told produced more optimised code. So I had a look at a database table I know isn't particularly blessed with indexs and has a reasonable number of rows 100K (Not huge either Need to look for a bigger table, I've got some somewhere with 10's of Millions), then just simply did a If Exists ( Select ... ) Print 'Found'Subtree Cost was 0.613 in both instances, Using the Primary Key dropped it to an even smaller amount. I would hazard a guess that the optimiser internally does a Select 1 Or a Select * (Rowsize is a constant in both at 1022) regardless of what you put in the Select Statement. Interestingly I just did the same thing putting a couple of fields in instead of * or 1 and got exactly the same result.So what does it mean in the real world? Not a lot I guess, Unless someone can come up with a reason why not to, I will continue to use Select 1 as I would be crucified by my developers if they ever saw Select * in a piece of my code  -- RegardsTony The DBA |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-21 : 10:03:12
|
quote:
Originally posted by TonyTheDBA...I will continue to use Select 1 as I would be crucified by my developers if they ever saw Select * in a piece of my code
YOU are the DBA! YOU decide what code is executed against the database. YOU are (or should be) the authority on indexing, SQL, execution plans, etc.Can't be a DBA without a spine. Now walk out to their cubicles and let 'em know who's da boss. |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2005-11-22 : 05:35:51
|
quote:
Originally posted by blindmanYOU are the DBA! YOU decide what code is executed against the database. YOU are (or should be) the authority on indexing, SQL, execution plans, etc.Can't be a DBA without a spine. Now walk out to their cubicles and let 'em know who's da boss.
 Funny you should say that, I keep telling them its in my job description to say No, and be Obstructive and unhelpful but do they listen . . . .-- RegardsTony The DBA |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2005-11-22 : 11:46:47
|
quote:
So what does it mean in the real world? Not a lot I guess, Unless someone can come up with a reason why not to, I will continue to use Select 1 as I would be crucified by my developers if they ever saw Select * in a piece of my code
I mentioned that exact thing in my presentation. In regards to your entire post I think the optimizer ignores everything between the SELECT and FROM when the query is inside an EXISTS function. At least I've never been able to put anything in there that affected the query plan.quote:
I'm interested to hear your experience on this approach and why you recommend against it (performance, readability, other...).
Rob already addressed this but I wanted to add a little. I did quite a bit of research on query plans and such for this presentation. I tried to test everyting I said if possible. It was a very educational experience :) One of the things I always encourage people to do is test it yourself. Write a few sample queries and see which works faster.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-11-22 : 13:30:22
|
| Think oracle used to optime select 1 better, sql at least used to work better with select * as it allowed it to select it's own index.Now it doesn't matter with sql server just that select * seems more readable i.e. are there any rows here.And more importantly - is Graz giving out sweets.I just remembered that I was going to post something about the sp recompile thing - i.e. how much of an sp recompiles.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-23 : 00:15:06
|
| Last year I did a review of a data warehouse design for a client that had built their schema based on seminars given by Ralph Kimbal. In each of their tables I found a column with nothing but the value "1" in it for every record.Why? Because Kimbal told them it was faster to sum([1column]) than count(*).Interestingly, they had this column even in a denormalized star schema (Kimbal's one-size-fits-all datawarehouse design...). |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-23 : 02:12:53
|
| Thoughts about indexes and relational theory come to mind. Funny how many people, even experts, don't realize WHY indexes are faster......nevermind.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-11-23 : 08:52:00
|
| >>Thoughts about indexes and relational theory come to mind. Funny how many people, even experts, don't realize WHY indexes are faster......nevermind.very true ... I often feel that every developer (*and* DBA!) should be required to take a computer science class on Data Structures. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-23 : 10:18:05
|
That is really good advise. I see a lot of posts on SQLTeam where people ask questions about query performance, or if something will work. My first thought is "Why don't you just test it and find our yourself?"There seem to be a lot of people who would rather ask an "expert" than just try it and see real numbers. Maybe you should include the Scientific Method in "What I Wish Developers Knew About SQL Server"quote:
Originally posted by graz...One of the things I always encourage people to do is test it yourself. Write a few sample queries and see which works faster...
CODO ERGO SUM |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-11-23 : 10:54:38
|
| Very true .... I discuss this a little bit herehttp://weblogs.sqlteam.com/jeffs/archive/2005/04/29/4832.aspx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-11-23 : 14:47:55
|
| Thanks Bill for posting it! Since my flight left around 4pm on Friday, I wasn't able to attend your session at PASS.Tara Kizeraka tduggan |
|
|
|
KirbyWallace
Starting Member
2 Posts |
Posted - 2006-01-26 : 13:14:02
|
| This PPT would be more helpful to me if it included the dialog that would have accompanied it's live presentation. Doesn't PPT have a way of "commenting" or "dialoging" a presentation? Is it perhaps there and I'm just not hearing it?I'm not exactly a PPT guru, so it could be the simplest of things that I'm missing. |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2006-01-27 : 04:57:11
|
I have an issue with page 26:quote:
Full Recovery All transactions stored until explicitly backed up Log file (.LDF) grows and grows and growsSimple Recovery Transactions purged after data is written to disk Log file doesn’t grow (much)
I think that part is missleading. The most important difference is that full recovery make point in time recovery possible. Log grows if transaction log backups are not scheduled. |
|
|
|
|
Article: Presentation: What I Wish Developers Knew About SQL Server