Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » SCAN count + OR Strategy
Add to briefcase

SCAN count + OR Strategy Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
karthik M
Posted Monday, January 21, 2013 11:37 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:34 AM
Points: 2,008, Visits: 2,472
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
Post #1409803
Bhuvnesh
Posted Tuesday, January 22, 2013 12:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1409815
GilaMonster
Posted Tuesday, January 22, 2013 1:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 38,095, Visits: 30,388
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 2008, MVP
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
Post #1409858
karthik M
Posted Tuesday, January 22, 2013 2:12 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:34 AM
Points: 2,008, Visits: 2,472
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
Post #1409868
GilaMonster
Posted Tuesday, January 22, 2013 3:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 38,095, Visits: 30,388
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 2008, MVP
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
Post #1409888
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.