Index Scan and Index Seek - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Index Scan and Index Seek

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-12 : 06:45:42

Hi Team,

I have two tables as listed below [The tables are heavy in number of records]. There is a clustered index on the #TempSecondCenter table. Still the query uses Clutered Index Scan.

Why is it not using SEEK?
What is the syntax for the query hint that will force SEEK?

Please explain.

CREATE TABLE #TempFirstArea
(
[Area_ID] [int] IDENTITY(1,1) NOT NULL,
[Area_No] [int] NOT NULL,
CONSTRAINT [PK_Area_Area_ID] PRIMARY KEY NONCLUSTERED ([Area_ID] ASC)
) ON [PRIMARY]

CREATE TABLE #TempSecondCenter
(
[Center_ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Area_ID] [int] NOT NULL,
[Center_No] [int] NOT NULL,
) ON [PRIMARY]

DECLARE @Counter INT
SET @Counter = 1

WHILE @Counter < 5000000
BEGIN
INSERT INTO #TempSecondCenter ([Area_ID],[Center_No]) VALUES (@Counter,@Counter)
SET @Counter = @Counter + 1
END

SET @Counter = 1
WHILE @Counter < 5000000
BEGIN
INSERT INTO #TempFirstArea ([Area_No]) VALUES (@Counter)
SET @Counter = @Counter + 1
END

SELECT Center_ID, A.Area_ID
FROM #TempSecondCenter C
INNER JOIN #TempFirstArea A ON A.AREA_ID = C.AREA_ID

DROP TABLE #TempSecondCenter
DROP TABLE #TempFirstArea

Thanks & Regards
Lijo Cheeran Joseph

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-12 : 06:58:48

I think it is because your select should return ALL values so there is no need to seek.

No, you're never too old to Yak'n'Roll if you're too young to die.

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-12 : 07:18:14

Thanks. Adding a WHERE condition make it using seek.[WHERE Center_ID < 5000001 ]

Two more questions

1) Even if there are no records in the tables, it still uses a SEEK (not a table scan) when I use WHERE condition. I have heared that it will use table scan if data is small. Why is this behavior?

2) What is the syntax for the query hint that will force SEEK?

---This is only for learning purpose

Thanks
Lijo

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-03-12 : 07:20:45

problem is a lack of "statistics" between the time of the insert and running of the query. at execution plan generation time the estimated size of the table is 0, so a scan is most efficient.

break it up into 4 stages.
create table
insert records
update statistics
select data

The lack of "where" clause also doesn't help.
no hint to force a seek, all you can do is nudge optimiser to use an index - HOW it gets used is upto the SQL Engine.

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-12 : 07:35:49

Hi,

As you mentioned, I executed it in 4 batches, as shown below. Still, it uses SEEK. Only 2 records are there in each table. Why optimizer does not take SCAN.

--------Batch 1 Execution
CREATE TABLE #TempFirstArea
(
[Area_ID] [int] IDENTITY(1,1) NOT NULL,
[Area_No] [int] NOT NULL,
CONSTRAINT [PK_Area_Area_ID] PRIMARY KEY NONCLUSTERED ([Area_ID] ASC)
) ON [PRIMARY]

CREATE TABLE #TempSecondCenter
(
[Center_ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Area_ID] [int] NOT NULL,
[Center_No] [int] NOT NULL,
) ON [PRIMARY]

--------Batch 2 Execution
DECLARE @Counter INT
SET @Counter = 1

WHILE @Counter < 3
BEGIN
INSERT INTO #TempSecondCenter ([Area_ID],[Center_No]) VALUES (@Counter,@Counter)
SET @Counter = @Counter + 1
END

SET @Counter = 1
WHILE @Counter < 3
BEGIN
INSERT INTO #TempFirstArea ([Area_No]) VALUES (@Counter)
SET @Counter = @Counter + 1
END

--------Batch 3 Execution
UPDATE STATISTICS #TempSecondCenter;
UPDATE STATISTICS #TempFirstArea;
GO

--------Batch 4 Execution
SELECT Center_ID, A.Area_ID
FROM #TempSecondCenter C
INNER JOIN #TempFirstArea A ON A.AREA_ID = C.AREA_ID
WHERE Center_ID < 5000001

--------Batch 5 Execution
DROP TABLE #TempSecondCenter
DROP TABLE #TempFirstArea

Thanks
Lijo Cheeran Joseph

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-12 : 11:02:07

This was a good article:

http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/

Has to do with the % of the Index over a table that would be 'Seeked' and after a certain amount a scan is just as good if not better than a seek.

For small tables I have read that a scan is better as well.

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-13 : 01:05:24

quote:
Originally posted by DP978

This was a good article:

http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/

For small tables I have read that a scan is better as well.

Thanks for the link.

Still one question remains - for small tables, whatever may the number of rows selected, it always should use Table Scan. But in the above example it does not. It uses a seek. [The above example has only 2 records]

Can you please explain why?

Thanks
Lijo Cheeran Joseph

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-13 : 12:10:36

There's no lower limit below which SQL will always do a scan. It has to do solely with the query, the indexes and the % of the table that the query will return (if the index is not covering)

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources