|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
it cant be that easyThere 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 Seek. Could that really speed things up? TIA, ChrisR Chris,
What you see looks reasonable, but there is no reason to think a scan is any slower than a seek, if the seek 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 seek. The actual work done is identical. select * from Northwind..Orders select * from Northwind..Orders where OrderID > 0 Steve Kass Drew University ChrisR wrote: Show quoteHide quote >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 Seek. 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 seeking, 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. Show quoteHide quote "Steve Kass" <sk***@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 seek, if the seek 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 seek. 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 Seek. Could that really speed things up? >> >>TIA, ChrisR >> >> >> To add a subtle point here...
In this particular case, I think that the seek 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])) Scan(OBJECT:([tempdb].[dbo].[PKauthors].[PK_PKauthors] AS [a]))|--Clustered Index |--Clustered Index Seek(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 "seek," as Steve and Kalen mentioned is just a seek 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 seek 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])) Seek(OBJECT:([tempdb].[dbo].[PKauthors].[PK_PKauthors] AS [a]), |--Clustered Index SEEK:([a].[au_id] >= 0) ORDERED FORWARD) |--Clustered Index Seek(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. Show quoteHide quote "ChrisR" <Chr***@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 Seek. 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 Seek. CR Show quoteHide quote "ChrisR" <Chr***@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 Seek. Could that really speed things up? > > TIA, ChrisR > > Have you made your test on empty tables (or with the autostatistic option
set to false) ? If yes, then it is perfectly normal that SQL-Server is unable to distinguish the futility of using a seek with the condition « a.au_id >= 0 ». Show quoteHide quote "ChrisR" <Chr***@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 Seek. Could that really speed things up? > > TIA, ChrisR > > Hi Chris,
There are times when a scan is exactly what you want. It's far faster to scan 100,000 rows than it is to seek them individually. I'm curious, When you make the change what does it do to the actual performance time? Show quoteHide quote "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> wrote in message news:OPktco1eFHA.3836@tk2msftngp13.phx.gbl... > Have you made your test on empty tables (or with the autostatistic option > set to false) ? If yes, then it is perfectly normal that SQL-Server is > unable to distinguish the futility of using a seek with the condition « > a.au_id >= 0 ». > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: http://cerbermail.com/?QugbLEWINF > > > "ChrisR" <Chr***@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 Seek. Could that really speed things up? >> >> TIA, ChrisR >> >> > >  
Other interesting topics
|
|||||||||||||||||||||||
