clustered index scan - SQL Server 2k5 SP3

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

General SQL Server Forums

New to SQL Server Programming

clustered index scan - SQL Server 2k5 SP3

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2009-09-04 : 13:41:11

drop table encounter
go
drop table patient
go
drop table appointment
go

create table patient (patientID int primary key identity(1,1), patientName varchar(50), clientID int)
create table encounter (encounterID int primary key identity(1,1), EncounterName varchar(50), hospitalID int, PatientID int references patient(PatientID))
create table appointment (appointmentID int primary key identity(1,1), HospitalID int, appointmentDate datetime )

insert patient
select 'FeeFee', 100 union all
select 'Max', 100 union all
select 'Bones', 100 union all
select 'Fort', 100

insert encounter
select 'Enc - Daniel', 10, 1 union all
select 'Enc - Jose', 10, 2 union all
select 'Enc - Mary', 11, 3 union all
select 'Enc - Luis', 11, 4

insert appointment
select 12, getdate()-5 union all
select 1, getdate()-3 union all
select 10, getdate()-10

select * from patient
select * from encounter
select * from appointment

/*

select a.hospitalid, a.appointmentDate, a.appointmentid
from appointment a
join encounter b
on a.hospitalID = b.hospitalID
join patient c
on c.patientid = b.patientid

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-04 : 13:43:07

I commend you for providing DDL and sample data, but is there a question?

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2009-09-04 : 14:03:52

oh sorry. when u look at the execution plan. there is a clustered index scan. I like to know how to get rid of it to improve performance. Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-04 : 14:23:43

1. Without a WHERE clause, you'll get a clustered scan since it will need to return all rows.
2. You are also returning all columns from the table. If it had done a seek, it would need to do an additional bookmark lookup. The scan would be more efficient
3. Why are you assuming the clustered scan performs poorly? Have you tested query hints that provide better performance?

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2009-09-09 : 11:30:50

i read query hints are bad for performance.

Pradip
Starting Member

32 Posts

Posted - 2009-11-17 : 06:50:12

You can add non-clustered index on hospitalid. It will change your hash match to Nested loop.
www.silicus.com

pradipjain

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-17 : 08:41:03

with 4 records in the table you should expect a scan