Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SCAN count + OR Strategy Expand / Collapse
Author
Message
Posted Monday, January 21, 2013 11:37 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 27, 2014 12:52 AM
Points: 2,031, Visits: 2,535
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
Posted Tuesday, January 22, 2013 12:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 2,841, Visits: 3,985
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
Post #1409815
Posted Tuesday, January 22, 2013 1:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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
Posted Tuesday, January 22, 2013 2:12 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 27, 2014 12:52 AM
Points: 2,031, Visits: 2,535
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
Posted Tuesday, January 22, 2013 3:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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