Why does Statement1 taking 30% more (in duration) than Statement2; clearly cpu & reads are smaller?

SQL_DV 2014-07-10 14:05:59

Table has ~5million records. A copy was created with variable types changed to smaller length (smallint, tinyint…). Statement 1 and Statement 2 are using different indices; however, even if they use the same indicies (using hint) Statement2 is better.

Statement 1: Duration 132ms, logical read 4.7k
Statement 2: Duration 100ms, logical read 6.1k

Query: Simple aggregate -> select count(*) from table

Aaron Bertrand 2014-07-11 15:53:40
30% more time sounds a lot worse than 30 extra milliseconds. Just saying, is this the biggest performance problem on your system? If so, sit back and play some minesweeper, you're doing pretty good. 🙂
Aaron Bertrand 2014-07-11 15:57:22
Also, which one is faster – the one that uses the skinnier data types? I got lost on which one is represented by statement1 and statement2.
SQL_DV 2014-07-10 20:13:49
Thanks Edward for the quick answer!

Yes both are different indexes in the sense that they're in different tables. Structure looks like the following:

declare @c as int = 1, @n int = 4262433;
create table ##table1 (id int not null, data1 int, data2 int );
create table ##table2 (id int not null, data1 smallint, data2 int );
while @c <= @n
       insert into ##table1 select @c, round(RAND()*36129-30000,0,0), round(RAND()*268275,0,0);
       select @c = @c + 1
insert into ##table2 select * from ##table1;
create nonclustered index idx_data1 ON ##table1(data1);
create nonclustered index idx_data2 ON ##table1(data2);
create nonclustered index idx_data1 ON ##table2(data1);
create nonclustered index idx_data2 ON ##table2(data2);
--set statistics time on;
select COUNT(*) from ##table1 option(recompile)
select COUNT(*) from ##table2 option(recompile)
--drop table ##table1;
--drop table ##table2;

Please note that the code doesn't generate the plan possibly because of difference in data ditribution (# of data page is significantly different, don't know why).

SQL_DV 2014-07-10 20:17:21
I forgot to mention environ…
SQL 08 Std Edition; win 08 8GB 2 quad-cores
Edward Norris 2014-07-11 16:04:39
ust a theory here… line 20: Set Statistics IO On; dbcc dropcleanbuffers(); –removes caches select COUNT() from ##table1; dbcc dropcleanbuffers(); select COUNT() from ##table2;

and see what the number of reads are. My first thought is that SQL reads from the hard drive in extents (8 pages of 8k each page). So, with the smaller data types, you are able to store more data per page. Therefore, the number of physical reads would be less. As a record of int, int, int uses, for simplistic sake, uses 4+4+4=12 bytes, with a page holding roughly 8192/12 = 682 records per page. Now the second table uses int, smallint, it or 4+2+4=10 bytes, or 8192/10 = 819 records per page. So for each physical read at the data level, an extent (assuming 100% fill) would contain 5456 records versus 6552 records. Therefore, the use of the smallint would allow 1096 additional records to be read per physical read. Thus, explaining why you get better performance…the second query has less physical reads. This should be shown in the message window from the set statistics io on command.