it cant be that easy

sql2k
There is no way this can be.
CREATE TABLE [dbo].[PKauthors] (
[au_id] [int] NOT NULL ,
[au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contract] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PKtitleauthor] (
[au_id] [int] NOT NULL ,
[title_id] [tid] NOT NULL ,
[au_ord] [tinyint] NULL ,
[royaltyper] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PKauthors] WITH NOCHECK ADD
CONSTRAINT [PK_PKauthors] PRIMARY KEY CLUSTERED
(
[au_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PKtitleauthor] WITH NOCHECK ADD
CONSTRAINT [PK_PKtitleauthor] PRIMARY KEY CLUSTERED
(
[au_id]
) ON [PRIMARY]
GO
select a.*,ta.title_id from PKauthors a
inner join PKtitleauthor ta on a.au_id = ta.au_id
This query shows a Clustered Index Scan.
But when I change the query:
select a.*,ta.title_id from PKauthors a
inner join PKtitleauthor ta on a.au_id = ta.au_id
and a.au_id >= 0
I get a S. Could that really speed things up?
TIA, ChrisRChris,
What you see looks reasonable, but there is no reason to think a
scan is any slower than a s, if the s identifies the same
rows of the table. The following two queries return
the same 830 rows, yet the plan for the first is a scan, and for the
second it is a s. The actual work done is identical.
select * from Northwind..Orders
select * from Northwind..Orders
where OrderID > 0
Steve Kass
Drew University
ChrisR wrote:

>sql2k
>There is no way this can be.
>CREATE TABLE [dbo].[PKauthors] (
> [au_id] [int] NOT NULL ,
> [au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [contract] [bit] NOT NULL
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[PKtitleauthor] (
> [au_id] [int] NOT NULL ,
> [title_id] [tid] NOT NULL ,
> [au_ord] [tinyint] NULL ,
> [royaltyper] [int] NULL
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[PKauthors] WITH NOCHECK ADD
> CONSTRAINT [PK_PKauthors] PRIMARY KEY CLUSTERED
> (
> [au_id]
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[PKtitleauthor] WITH NOCHECK ADD
> CONSTRAINT [PK_PKtitleauthor] PRIMARY KEY CLUSTERED
> (
> [au_id]
> ) ON [PRIMARY]
>GO
>
>select a.*,ta.title_id from PKauthors a
>inner join PKtitleauthor ta on a.au_id = ta.au_id
>This query shows a Clustered Index Scan.
>But when I change the query:
>select a.*,ta.title_id from PKauthors a
>inner join PKtitleauthor ta on a.au_id = ta.au_id
>and a.au_id >= 0
>I get a S. Could that really speed things up?
>TIA, ChrisR
>
>|||To elaborate on what Steve said, anytime a condition in the where clause
indicates a starting or stopping position, the plan says it is sing, even
if it is scanning EVERYTHING from that point onward. This is an extreme
situation, where the condition is saying to stop at the first value greater
than 0, which just happens to be the first row. So the work done will be
essentially the same.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Steve Kass" <skass@.drew.edu> wrote in message
news:uCMfKW$ZFHA.1456@.TK2MSFTNGP15.phx.gbl...
> Chris,
> What you see looks reasonable, but there is no reason to think a
> scan is any slower than a s, if the s identifies the same
> rows of the table. The following two queries return
> the same 830 rows, yet the plan for the first is a scan, and for the
> second it is a s. The actual work done is identical.
> select * from Northwind..Orders
> select * from Northwind..Orders
> where OrderID > 0
> Steve Kass
> Drew University
> ChrisR wrote:
>|||To add a subtle point here...
In this particular case, I think that the s can be considered as less
efficient than the scan.
If you look at the plan for the first query, the scan of PK_PKauthors is not
ordered (no mention of ordered forward or backward next to the index name):
|--Nested Loops(Inner Join, OUTER REFERENCES:([a].[au_id]))
|--Clustered Index
Scan(OBJECT:([tempdb].[dbo].[PKauthors].[PK_PKauthors] AS [a]))
|--Clustered Index
S(OBJECT:([tempdb].[dbo].[PKtitleauthor].[PK_PKtitleauthor] AS [ta]),
SEEK:([ta].[au_id]=[a].[au_id]) ORDERED FORWARD)
What you see is basically a table scan where the optimizer can choose any
technique to grab the data, without needing to guarantee any order. Usually,
this means scanning the extents based on physical order on disk using IAM
bitmaps.
While the plan for the second query which says a "s," as Steve and Kalen
mentioned is just a s to the first point that matches the filter, and
then performing an ordered forward scan of all qualifying rows. In this
case, it's all table rows. This type of s can be referred to as a partial
scan, and in this case the "partial" happens to be "all".
|--Nested Loops(Inner Join, OUTER REFERENCES:([a].[au_id]))
|--Clustered Index
S(OBJECT:([tempdb].[dbo].[PKauthors].[PK_PKauthors] AS [a]),
SEEK:([a].[au_id] >= 0) ORDERED FORWARD)
|--Clustered Index
S(OBJECT:([tempdb].[dbo].[PKtitleauthor].[PK_PKtitleauthor] AS [ta]),
SEEK:([ta].[au_id]=[a].[au_id]) ORDERED FORWARD)
What I'm trying to say is:
1. You can identify that both queries ultimately perform a full scan of the
table (clustered index)
2. The first does it unordered, while the second does it ordered
Scanning in order has a cost. In this case, it's scanning the leaf of the
index following the linked list (as opposed to physical order). The
difference between the performance of the two will probably be determined by
the level of fragmentation of the index.
Your conclusion in this case should probably be, don't add the artificial
filter assuming it would improve performance. If anything, it would probably
hurt performance.
BG, SQL Server MVP
www.SolidQualityLearning.com
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:On0mc6%23ZFHA.3536@.TK2MSFTNGP10.phx.gbl...
> sql2k
> There is no way this can be.
> CREATE TABLE [dbo].[PKauthors] (
> [au_id] [int] NOT NULL ,
> [au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [contract] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[PKtitleauthor] (
> [au_id] [int] NOT NULL ,
> [title_id] [tid] NOT NULL ,
> [au_ord] [tinyint] NULL ,
> [royaltyper] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PKauthors] WITH NOCHECK ADD
> CONSTRAINT [PK_PKauthors] PRIMARY KEY CLUSTERED
> (
> [au_id]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PKtitleauthor] WITH NOCHECK ADD
> CONSTRAINT [PK_PKtitleauthor] PRIMARY KEY CLUSTERED
> (
> [au_id]
> ) ON [PRIMARY]
> GO
>
> select a.*,ta.title_id from PKauthors a
> inner join PKtitleauthor ta on a.au_id = ta.au_id
> This query shows a Clustered Index Scan.
> But when I change the query:
> select a.*,ta.title_id from PKauthors a
> inner join PKtitleauthor ta on a.au_id = ta.au_id
> and a.au_id >= 0
> I get a S. Could that really speed things up?
> TIA, ChrisR
>|||Thanks to all. I had assumed that if things were that easy, everyone would
use them. But was always under the impression a Scan was always worse than a
S.
CR
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:On0mc6%23ZFHA.3536@.TK2MSFTNGP10.phx.gbl...
> sql2k
> There is no way this can be.
> CREATE TABLE [dbo].[PKauthors] (
> [au_id] [int] NOT NULL ,
> [au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [contract] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[PKtitleauthor] (
> [au_id] [int] NOT NULL ,
> [title_id] [tid] NOT NULL ,
> [au_ord] [tinyint] NULL ,
> [royaltyper] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PKauthors] WITH NOCHECK ADD
> CONSTRAINT [PK_PKauthors] PRIMARY KEY CLUSTERED
> (
> [au_id]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PKtitleauthor] WITH NOCHECK ADD
> CONSTRAINT [PK_PKtitleauthor] PRIMARY KEY CLUSTERED
> (
> [au_id]
> ) ON [PRIMARY]
> GO
>
> select a.*,ta.title_id from PKauthors a
> inner join PKtitleauthor ta on a.au_id = ta.au_id
> This query shows a Clustered Index Scan.
> But when I change the query:
> select a.*,ta.title_id from PKauthors a
> inner join PKtitleauthor ta on a.au_id = ta.au_id
> and a.au_id >= 0
> I get a S. Could that really speed things up?
> TIA, ChrisR
>