January 21, 2013 at 11:37 pm
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
January 22, 2013 at 12:14 am
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;-)
January 22, 2013 at 1:33 am
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
January 22, 2013 at 2:12 am
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
January 22, 2013 at 3:18 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply