September 14, 2009 at 4:33 am
hmm, good point. I changed your second query to use * rather than be identical to the first and the plans were still the same.
Perhaps older DBs did not optimise this out quite as well and that's where the methodology comes from?
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
September 14, 2009 at 4:40 am
I would imagine select 1 would be marginally more efficient, because it doesn't then have to look at meta data in order to determine what * would actually be...
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 14, 2009 at 4:43 am
Rob Fisk (9/14/2009)
I changed your second query to use * rather than be identical to the first and the plans were still the same.
I think the difference would be in coming up with the plan, personally. Sure, it would be a *really* small difference - but I've always tried to engineer my DBs in such a way that it would scale as best as it could. A *really* small difference * x thousand transactions per second must add up...
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 14, 2009 at 4:44 am
Will the '1 is more efficient than *' myth never die? It was briefly true for DB2 and Oracle (in different ways) but that was all a very, very, very long time ago.
My personal favourites are:
COUNT(1) - which the XML query plan shows as being implemented by a 'count_star' internal function; and
EXISTS (SELECT NULL - which seems to be a sign of very muddled thinking...
Latest research shows that COUNT(0) is even more efficient :laugh:
COUNT(*) and EXISTS(SELECT * seem most logical to me, but it's not a religious thing. Use SQRT(PI()) if you must.
Paul
P.S. Alex - you and I are going to fall out spectacularly if you use that 'colloquially' quip with Excel again!!!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2009 at 4:59 am
To be absolutely 100% clear on this: SQL Server contains code to recognize COUNT(*) and EXISTS (SELECT * constructs early on. No column meta data is ever accessed. COUNT(constant) and EXISTS (constant) behave identically in all cases, and without exception. There is not even the smallest benefit in using a constant. It may even work the other way around.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2009 at 5:04 am
you and I are going to fall out spectacularly if you use that 'colloquially' quip with Excel again!!!
OK, OK. I was going to ask a question about the default font and colour of a column, but I forgot anyway. 😎
September 14, 2009 at 5:08 am
Paul.
It's good to know this and have it spelled out in the clear once and for all.
Surely using a constant can't be a negative though. I mean, COUNT(0) has the old school and William Gibson cool factor.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
September 14, 2009 at 5:10 am
Rob Fisk (9/14/2009)
Surely using a constant can't be a negative though. I mean, COUNT(0) has the old school and William Gibson cool factor.
I will give you that. I'm just jealous because I am not 5% cool enough to use it (seriously, I'm not!)
Your signature is awesome.
@alex: ROFL!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2009 at 5:16 am
Paul White (9/14/2009)
To be absolutely 100% clear on this: SQL Server contains code to recognize COUNT(*) and EXISTS (SELECT * constructs early on. No column meta data is ever accessed. COUNT(constant) and EXISTS (constant) behave identically in all cases, and without exception. There is not even the smallest benefit in using a constant. It may even work the other way around.Paul
Your avatar is *so* appropriate (and that's coming from someone who always thought the dark side should have won the whole star wars thing).
Thanks for clearing it up 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 14, 2009 at 5:27 am
As a proof by authority, this is what Itzik Ben-Gan has to say about the EXISTS subquery:
The use of * here is perfectly safe, even though in general it's not a good practice. The optimizer ignores the SELECT list specified in the subquery because EXISTS cares only about the existence of rows and not about any specific attributes.
("Inside Microsoft® SQL Server™ 2005 T-SQL Querying", Chapter 4)
September 14, 2009 at 6:29 am
Just one note for the naysayers. This relates to the OP.
I LIKE using SELECT *, since it allows full table data to be funnelled seamlessly up (along ?) through several levels of nested views without specifically mentioning columns. Sometimes, you do only want specific columns, but other times you really want everything in the table now and at any time in the future.
BUT there is the problem described here, with views as well.
Call it brute force, but since I have a program which keeps a database rev number and all the update scripts necessary to go from any rev up to the current rev, I simply put at the end of the update process a script which refreshes all views in the database using sp_refreshview (similar to this http://yellowduckguy.spaces.live.com/Blog/cns!DA380C13569E8907!336.entry).
I run it about 7 times (to allow for nested views having SELECT * -yes all views in the db are refreshed 7 times, which is more than my maximum required nested view depth).
Looks like you could refresh all the other (non-view) objects with sp_refreshsqlmodule.
This refresh process takes a minute but it's part of a software uprgade process, so that time's not a big issue.
In the dev environment, I'm onto it enough to remember to refresh views myself or call this global refresh if things get too messy.
I still think it's much better than typing all those names(and re-typing them five times as they percolate up through the views). Saves a heap of time and verbiage. A line of code written is a line of code to be maintained. If done right, this technique is sexy.
A warning: probably not for team environments.
September 14, 2009 at 6:39 am
Did you see the DDL trigger I posted?
You could add the guts of that instead of the 'refresh everything 7 times' with two distinct advantages:
1) Everything would be refreshed once.
2) There limit on nesting levels would be much higher (100 - the limit of a recursive CTE's nesting levels).
I've got to say though, the thought of having to actually use that sort of code fills my soul with darkness...
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 14, 2009 at 6:48 am
ben.mcintyre (9/14/2009)
A warning: probably not for team environments.
You or the code? :blink:
No, seriously, have you considered using the right-click on object, script as SELECT to...option in Management Studio?
Paul
P.S. Matt - OPTION MAXRECURSION (0) gives you 32767 levels IIRC
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2009 at 7:09 am
Paul White (9/14/2009)
P.S. Matt - OPTION MAXRECURSION (0) gives you 32767 levels IIRC
You are a goldmine sir.
From MSDN: 'When 0 is specified, no limit is applied' - if you specify a number though it must be between 0 and 32767.
To be honest though, the thought of seeing a schema which had a dependency tree 100 levels deep also fills my soul with BLACK http://www.youtube.com/watch?v=QRJxafiqHvw
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 14, 2009 at 7:15 am
Matt - no I hadn't waded through all the pages, but I just did and have to say your little script is a keeper ! It makes my solution look like the ugly brute force hack it is. I hadn't plumbed the depths of the dependencies table before.
More generally, my point is that SELECT * is a feature with certain risks associated with it. Like any feature, it can yield benefits as long as the risks are fully understood and managed.
I know that SELECT * is almost universally regarded as bad practice, even as a coding error, but I simply don't agree that this is a blanket truth. * means 'all the columns in the table', and that in itself is not a bad thing.
I think in team environments that the potential for confusion and unintended effects on other developers is much greater, and that this tips the balance, and is probably why it is regarded badly in the industry.
For small teams, it can increase the agility of the code quite substantially. Add one column, and it can 'bubble up' through whole series of views to make itself available to the presentation layer without one single further keystroke. That's pretty powerful.
While the idea of doing these refreshes is a bit ugly (much less so, thanks Matt), it's like a medicine. It doesn't taste good, but once you've swallowed it, it cures the disease and you've got a handy feature you didn't have before. It's a hack that doesn't pollute your actual code, it's a 'meta-hack' really.
Viewing 15 posts - 121 through 135 (of 151 total)
You must be logged in to reply to this topic. Login to reply