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_at_TK2MSFTNGP14.phx.gbl>
Mikito Harakiri wrote:
"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)
Received on Fri Aug 19 2005 - 09:53:56 CEST
Date: Fri, 19 Aug 2005 17:53:56 +1000
Message-ID: <#p0TpMJpFHA.1968_at_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 tableNo 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)
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.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.
--
mike
hodgson
blog: http://sqlnerd.blogspot.com
Received on Fri Aug 19 2005 - 09:53:56 CEST