SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Presentation: What I Wish Developers Knew About SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 11/17/2005 :  19:50:18  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 Posts

Posted - 11/18/2005 :  11:20:45  Show Profile  Reply with Quote
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
Go to Top of Page

mattb
Starting Member

USA
5 Posts

Posted - 11/20/2005 :  11:13:30  Show Profile  Reply with Quote
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
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 11/20/2005 :  11:17:00  Show Profile  Visit graz's Homepage  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15668 Posts

Posted - 11/20/2005 :  11:22:51  Show Profile  Visit robvolk's Homepage  Reply with Quote
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 Col

The 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!
Go to Top of Page

mattb
Starting Member

USA
5 Posts

Posted - 11/20/2005 :  20:40:15  Show Profile  Reply with Quote
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!
Go to Top of Page

TonyTheDBA
Posting Yak Master

United Kingdom
121 Posts

Posted - 11/21/2005 :  08:09:12  Show Profile  Reply with Quote
Thats a nice persentation with some useful ideas You mention this

IF EXISTS (SELECT * …)

To check for existance, which It certainly does, Coming from a Sybase System 10/ SQL Server 4.3 background I have always used

IF 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

--
Regards
Tony The DBA
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 11/21/2005 :  10:03:12  Show Profile  Reply with Quote
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.
Go to Top of Page

TonyTheDBA
Posting Yak Master

United Kingdom
121 Posts

Posted - 11/22/2005 :  05:35:51  Show Profile  Reply with Quote
quote:
Originally posted by blindman
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.



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 . . . .

--
Regards
Tony The DBA
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 11/22/2005 :  11:46:47  Show Profile  Visit graz's Homepage  Reply with Quote
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.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 11/22/2005 :  13:30:22  Show Profile  Visit nr's Homepage  Reply with Quote
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.

Edited by - nr on 11/22/2005 20:40:34
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 11/23/2005 :  00:15:06  Show Profile  Reply with Quote
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...).
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 11/23/2005 :  02:12:53  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
Thoughts about indexes and relational theory come to mind. Funny how many people, even experts, don't realize WHY indexes are faster......nevermind.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/23/2005 :  08:52:00  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
>>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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/23/2005 :  10:18:05  Show Profile  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/23/2005 :  10:54:38  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Very true .... I discuss this a little bit here

http://weblogs.sqlteam.com/jeffs/archive/2005/04/29/4832.aspx
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 11/23/2005 :  14:47:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
Thanks Bill for posting it! Since my flight left around 4pm on Friday, I wasn't able to attend your session at PASS.

Tara Kizer
aka tduggan
Go to Top of Page

KirbyWallace
Starting Member

2 Posts

Posted - 01/26/2006 :  13:14:02  Show Profile  Reply with Quote

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.
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 01/27/2006 :  04:57:11  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
I have an issue with page 26:
quote:

Full Recovery
All transactions stored until explicitly backed up
Log file (.LDF) grows and grows and grows
Simple 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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000