Table Scan vs Index

Question

Post reply

Table Scan vs Index

karthik M

One Orange Chip

Points: 29139
More actions
May 20, 2008 at 4:15 am

#182586

All,
I have one senario.
Table Name: Employee
Table Structure:
MIDvarchar10
MNamevarchar255
Classvarchar50
Stylevarchar100
StyleDetailvarchar100
Productvarchar255
ANamevarchar100
AScoredecimal5
BCodevarchar20
Firmvarchar100
StartDatedatetime8
Statusvarchar1
StatusChangeDatedatetime8
Pformvarchar25
Currencyvarchar10
No of Records : 5000
Index:
MID, Pform Non Clustered id00
Class Non Clustered id01
Style Non Clustered id02
StyleDetail Non Clustered id03
way #1:
My Query:
select MID, MName, Class, Style, StyleDetail, Product, BCode,
AName, AScore, Pform, Status, StartDate
from Employee
where Status <> ( 'T')
AND convert(datetime,(convert(varchar(10),convert(smalldatetime, StartDate),101))) <= convert(datetime,(convert(varchar(10),convert(smalldatetime, getdate()),101)))
AND Upper(Pform) = Upper(RTrim(lTrim(@Pform)))
ORDER BY MID
Statistical output:
Table: Employee scan count 1, logical reads: (regular=1782 apf=0 total=1782), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
SQL Server cpu time: 0 ms. SQL Server elapsed time: 66 ms.
way #2:
My Query:
select MID, MName, Class, Style, StyleDetail, Product, BCode,
AName, AScore, Pform, Status, StartDate
from Employee
-- where Status <> ( 'T')
where Status in ('','B','L')
AND convert(datetime,(convert(varchar(10),convert(smalldatetime, StartDate),101))) <= convert(datetime,(convert(varchar(10),convert(smalldatetime, getdate()),101)))
AND Upper(Pform) = Upper(RTrim(lTrim(@Pform)))
ORDER BY MID
Statistical Output:
Table: Employee scan count 4, logical reads: (regular=411 apf=0 total=411), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable1 scan count 0, logical reads: (regular=310 apf=0 total=310), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
SQL Server cpu time: 100 ms. SQL Server elapsed time: 60 ms.
My Questions:
1) which is the correct way to approach this query ?
2) do we need to look for highest Logical Read or lowest ?
Logical read means read the data from cahce instead of from the disk. But here i saw two logical reads,Thats why i confused. As of my knowledge we need to look for highest logical read,even though i am not very clear with this point.
Suggestions are welcome !
karthik

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

karthik M One Orange Chip Points: 29139 More actions · Answer 1

I heard that we should avoid <> operator because it leads to 'Table Scan'.

I think way #1 is best when we compare with way #2. Am i correct ?

karthik

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Forget the inequlity, all those functions on columns in there where clause mean you are going to get scans. (not that you have an index on StartDate that SQL could use)

You have no index on Status, therefore it's immaterial whether you do an inequality or a not in. There's no index that SQL can use to evaluate that

As for higher vs lower reads, which do you think is better, for SQL to read 1782 pages or for it to read 720?

Why do you think 1 is better

Upper(Pform) = Upper(RTrim(lTrim(@Pform)))

Is your database in a case-sensitive collation?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 3

As for higher vs lower reads, which do you think is better, for SQL to read 1782 pages or for it to read 720?

1782 is better. Am i correct ?

Why do you think 1 is better
Upper(Pform) = Upper(RTrim(lTrim(@Pform)))
Is your database in a case-sensitive collation?

Yes.

karthik

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

karthikeyan (5/20/2008)
As for higher vs lower reads, which do you think is better, for SQL to read 1782 pages or for it to read 720?
1782 is better. Am i correct ?

Why?

Which is quicker for you, reading a 100 page book or reading a 500 page book?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 5

oh..Ok...I thought that 1782 is the maximum page per read.

Thanks Gila for pointing some good points.

so, what is your suggestion ?

karthik

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

Put useful indexes on the table and remove the functions in the where clause.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 7

I did that one. I saw remarkable change in the execution time.

Earlier it took 2.5 hrs, now it is taking 50 minutes only, still i am not happy with the execution time.

Any more techniques to screw the query ?

karthik

John Rowan SSC Guru Points: 56440 More actions · Answer 8

Did I read correctly, it is taking 50 minutes to query a table with only 5000 rows?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

karthikeyan (5/20/2008)
I did that one. I saw remarkable change in the execution time.
Earlier it took 2.5 hrs, now it is taking 50 minutes only, still i am not happy with the execution time.
Any more techniques to screw the query ?

Since you've made changes - post the query again. Also - where is most of the time being spent?

50 minutes for this query? Like John mentioned - that's an AWFULLY long time. Is employee an actual table or a view?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?