SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SCAN count + OR Strategy


SCAN count + OR Strategy

Author
Message
karthik M
karthik M
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6667 Visits: 2586
All,

I found some wierd thing today when i ran the below two
version of queries.

select * from sub_fund
where sub_fund_id in (1,2,3,4,5,6,7,8,9,10)

scan count = 10

select * from sub_fund
where sub_fund_id in (1,2,3,4,5,6,7,8,9,10,11)

scan count = 1

how? It should be 11 right.


Table has 118 records.


index_name
sub_fund_pk

index_keys
sub_fund_id

index_description
clustered, unique


create table sub_fund(
sub_fund_id numeric(19,0) not null,
name varchar(100) not null,
short_name varchar(30) null,
val_start_date datetime not null,
val_end_date datetime null,
active_version_id numeric(19,0) null,
client_internal_code varchar(30) null,
version_timestamp datetime not null,
is_active_version int not null,
fund_id numeric(19,0) null,
transfer_agent_id numeric(19,0) null,
custodian_id numeric(19,0) null,
fund_admin_id numeric(19,0) null,
portfolio_id numeric(19,0) null,
margin_id numeric(20,0) null,
constraint sub_fund_pk primary key clustered ( sub_fund_id )
)

karthik
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12902 Visits: 4077
karthik M (1/21/2013)
All,

I found some wierd thing today when i ran the below two
version of queries.

select * from sub_fund
where sub_fund_id in (1,2,3,4,5,6,7,8,9,10)

scan count = 10

select * from sub_fund
where sub_fund_id in (1,2,3,4,5,6,7,8,9,10,11)

scan count = 1

how? It should be 11 right.


Table has 118 records.


Here are you talking about set statisctics io ? if yes then post complete output

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218587 Visits: 46278
karthik M (1/21/2013)
All,

I found some wierd thing today when i ran the below two
version of queries.

select * from sub_fund
where sub_fund_id in (1,2,3,4,5,6,7,8,9,10)

scan count = 10

select * from sub_fund
where sub_fund_id in (1,2,3,4,5,6,7,8,9,10,11)

scan count = 1

how? It should be 11 right.


Not necessarily. Without the execution plan it's a guess, but I'd guess that the first one ran as an index seek. 10 seek operations (for 10 values). The second ran as a scan, a single table scan.

You're better off ignoring the scan count. It is not a count of the number of times a table was scanned. I've seen cases where the scan count was 0 despite the table being accessed. Use the logical reads as a measure of the amount of data read.

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
karthik M
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6667 Visits: 2586
Gail,

You are right. The first one uses index seek and the second one uses "Table Scan". How? I used the indexed column only. Um..I guess..The optimizer choose "TableScan" as the number of records are less and to resolve this query "Table scan" is better than "Index Seek".
Right? Thats why the second one is showing '1' for scan count.

#1)
Logical Read : 20

#2)
Logical Read:2

which one is better? Less Logical Read. But it uses "Table Scan". Again I think as the table record is very less, it is not the right time to take any decision ( i mean based on 18 records). right?

karthik
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218587 Visits: 46278
karthik M (1/22/2013)
#1)
Logical Read : 20

#2)
Logical Read:2

which one is better? Less Logical Read. But it uses "Table Scan". Again I think as the table record is very less, it is not the right time to take any decision ( i mean based on 18 records). right?


Well which would you prefer, more reads (more data read) or less?

But yes, can't do meaningful performance tests on 18 rows.

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search