Re: SQL Humor

From: Mike Hodgson <mike.hodgson_at_mallesons.nospam.com>
Date: Fri, 19 Aug 2005 17:53:56 +1000
Message-ID: <#p0TpMJpFHA.1968@TK2MSFTNGP14.phx.gbl>

Mikito Harakiri wrote:

>Hugo Kornelis wrote:
>
>
>>On 18 Aug 2005 13:17:37 -0700, Mikito Harakiri wrote:
>>
>>
>>>pondering if
>>>
>>>select * from table
>>>
>>>is faster than
>>>
>>>select col1, col2, ... from table
>>>
>>>
>>No need to ponder that -- all SQL Server DBAs (and presumably all DBAs
>>for all serious RDBMS's) know that SELECT * should never be used in
>>production code (except in a EXISTS(..) subquery).
>>
>>
>
>My bad. I meant
>
>select count(1) from table
>
>vs.
>
>select count(*) from table
>
>

"select count(1) from table" and "select count(*) from table" will both come up with the same execution plan. They will both count the number of entries in the narrowest index on that table. That's, perhaps, a bad example as the optimiser is specifically designed to deal with that case I believe. See example below (both cases require 227 logical reads and return 121371 rows in this example):

StmtText

---

select count(1) from dbo.SalesOrderDetail

(1 row(s) affected)

StmtText

---

  |--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1003])))

       |--Stream Aggregate(DEFINE:([Expr1003]=Count(*)))
            |--Index 

Scan(OBJECT:([AdventureWorks2000].[dbo].[SalesOrderDetail].[IX_SalesOrderDetail_ProductID]))

(3 row(s) affected)

StmtText

---

select count(*) from dbo.SalesOrderDetail

(1 row(s) affected)

StmtText

---

  |--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1003])))

       |--Stream Aggregate(DEFINE:([Expr1003]=Count(*)))
            |--Index 

Scan(OBJECT:([AdventureWorks2000].[dbo].[SalesOrderDetail].[IX_SalesOrderDetail_ProductID]))

(3 row(s) affected)

>BTW, you triggered the other example: is EXISTS or IN faster? This
>question could come up only from somebody who is completely unaware of
>SQL expression equivalency and query rewrite. Well, making sure the
>extents and segments are layed out on disk properly, leaves little room
>for education and abstract thinking.
>
>

The EXISTS() predicate is typically a fairly efficient predicate because it only needs to scan until it gets a match, at which time it returns. The worst case scenario (it finds a match on the last physical row, or it doesn't find any matching row) is the same I/O as the IN() predicate case because IN() will evaluate the entire subquery.

--
*mike hodgson*
blog: http://sqlnerd.blogspot.com

Received on Fri Aug 19 2005 - 02:53:56 CDT