InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 08:21:15 GMT20http://www.sqlservercentral.com/Forums/Topic1409803-392-1.aspx[quote][b]karthik M (1/22/2013)[/b][hr]#1) Logical Read : 20#2)Logical Read:2which 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?[/quote]Well which would you prefer, more reads (more data read) or less?But yes, can't do meaningful performance tests on 18 rows.Tue, 22 Jan 2013 03:18:30 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1409803-392-1.aspxGail, 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:2which 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?Tue, 22 Jan 2013 02:12:11 GMTkarthik Mhttp://www.sqlservercentral.com/Forums/Topic1409803-392-1.aspx[quote][b]karthik M (1/21/2013)[/b][hr]All,I found some wierd thing today when i ran the below twoversion of queries.select * from sub_fundwhere sub_fund_id in (1,2,3,4,5,6,7,8,9,10)scan count = 10select * from sub_fundwhere sub_fund_id in (1,2,3,4,5,6,7,8,9,10,11)scan count = 1how? It should be 11 right.[/quote]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.Tue, 22 Jan 2013 01:33:22 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1409803-392-1.aspx[quote][b]karthik M (1/21/2013)[/b][hr]All,I found some wierd thing today when i ran the below twoversion of queries.select * from sub_fundwhere sub_fund_id in (1,2,3,4,5,6,7,8,9,10)scan count = 10select * from sub_fundwhere sub_fund_id in (1,2,3,4,5,6,7,8,9,10,11)scan count = 1how? It should be 11 right.Table has 118 records.[/quote]Here are you talking about [i]set statisctics io[/i] ? if yes then post complete outputTue, 22 Jan 2013 00:14:38 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1409803-392-1.aspxAll,I found some wierd thing today when i ran the below twoversion of queries.select * from sub_fundwhere sub_fund_id in (1,2,3,4,5,6,7,8,9,10)scan count = 10select * from sub_fundwhere sub_fund_id in (1,2,3,4,5,6,7,8,9,10,11)scan count = 1how? It should be 11 right.Table has 118 records.index_namesub_fund_pkindex_keyssub_fund_idindex_descriptionclustered, uniquecreate 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 ))Mon, 21 Jan 2013 23:37:26 GMTkarthik M