sql server - Performance of Non Clustered Indexes on Heaps vs Clustered Indexes -
this 2007 white paper compares performance individual select/insert/delete/update , range select statements on table organized clustered index vs on table organized heap non clustered index on same key columns ci table.
generally clustered index option performed better in tests there 1 structure maintain , because there no need bookmark lookups.
one potentially interesting case not covered paper have been comparison between non clustered index on heap vs non clustered index on clustered index. in instance have expected heap might perform better once @ nci leaf level sql server has rid follow directly rather needing traverse clustered index.
is aware of similar formal testing has been carried out in area , if results?
to check request created 2 tables following scheme:
- 7.9 million records representing balance information.
- an identity field counting 1 7.9 million
- a number field grouping records in 500k groups.
the first table called heap got non clustered index on field group. second table called clust got clustered index on sequential field called key , nonclustered index on field group
the tests run on i5 m540 processor 2 hyperthreaded cores, 4gb memory , 64-bit windows 7.
microsoft sql server 2008 r2 (rtm) - 10.50.1600.1 (x64) apr 2 2010 15:48:46 developer edition (64-bit) on windows nt 6.1 <x64> (build 7601: service pack 1) update on 9 mar 2011: did second more extensive benchmark running following .net code , logging duration, cpu, reads, writes , rowcounts in sql server profiler. (the commandtext used mentioned in results.)
note: cpu , duration expressed in milliseconds
- 1000 queries
- zero cpu queries eliminated results
- 0 rows affected eliminated results
int[] idlist = new int[] { 6816588, 7086702, 6498815 ... }; // 1000 values here. using (var conn = new sqlconnection(@"data source=myserver;initial catalog=mydb;integrated security=sspi;")) { conn.open(); using (var cmd = new sqlcommand()) { cmd.connection = conn; cmd.commandtype = commandtype.text; cmd.commandtext = "select * heap common_key between @id , @id+1000"; cmd.parameters.add("@id", sqldbtype.int); cmd.prepare(); foreach (int id in idlist) { cmd.parameters[0].value = id; using (var reader = cmd.executereader()) { int count = 0; while (reader.read()) { count++; } console.writeline(string.format("key: {0} => {1} rows", id, count)); } } } } end of update on 9 mar 2011.
select performance
to check performanc numbers performed following queries once on heap table , once on clust table:
select * heap/clust group between 5678910 , 5679410 select * heap/clust group between 6234567 , 6234967 select * heap/clust group between 6455429 , 6455729 select * heap/clust group between 6655429 , 6655729 select * heap/clust group between 6955429 , 6955729 select * heap/clust group between 7195542 , 7155729 the results of benchmark heap:
rows reads cpu elapsed ----- ----- ----- -------- 1503 1510 31ms 309ms 401 405 15ms 283ms 2700 2709 0ms 472ms 0 3 0ms 30ms 2953 2962 32ms 257ms 0 0 0ms 0ms update on 9 mar 2011:
cmd.commandtext = "select * heap group between @id , @id+1000";
- 721 rows have > 0 cpu , affect more 0 rows
counter minimum maximum average weighted --------- ------- ---------- ------- --------- rowcounts 1001 69788 6368 - cpu 15 374 37 0.00754 reads 1069 91459 7682 1.20155 writes 0 0 0 0.00000 duration 0.3716 282.4850 10.3672 0.00180 end of update on 9 mar 2011.
for table clust results are:
rows reads cpu elapsed ----- ----- ----- -------- 1503 4827 31ms 327ms 401 1241 0ms 242ms 2700 8372 0ms 410ms 0 3 0ms 0ms 2953 9060 47ms 213ms 0 0 0ms 0ms update on 9 mar 2011:
cmd.commandtext = "select * clust group between @id , @id+1000";
- 721 rows have > 0 cpu , affect more 0 rows
counter minimum maximum average weighted --------- ------- ---------- ------- --------- rowcounts 1001 69788 6056 - cpu 15 468 38 0.00782 reads 3194 227018 20457 3.37618 writes 0 0 0 0.0 duration 0.3949 159.6223 11.5699 0.00214 end of update on 9 mar 2011.
select join performance
cmd.commandtext = "select * heap/clust h join keys k on h.group = k.group h.group between @id , @id+1000";
the results of benchmark heap:
873 rows have > 0 cpu , affect more 0 rows
counter minimum maximum average weighted --------- ------- ---------- ------- --------- rowcounts 1009 4170 1683 - cpu 15 47 18 0.01175 reads 2145 5518 2867 1.79246 writes 0 0 0 0.00000 duration 0.8215 131.9583 1.9095 0.00123 the results of benchmark clust:
865 rows have > 0 cpu , affect more 0 rows
counter minimum maximum average weighted --------- ------- ---------- ------- --------- rowcounts 1000 4143 1685 - cpu 15 47 18 0.01193 reads 5320 18690 8237 4.97813 writes 0 0 0 0.00000 duration 0.9699 20.3217 1.7934 0.00109 update performance
the second batch of queries update statements:
update heap/clust set amount = amount + 0 group between 5678910 , 5679410 update heap/clust set amount = amount + 0 group between 6234567 , 6234967 update heap/clust set amount = amount + 0 group between 6455429 , 6455729 update heap/clust set amount = amount + 0 group between 6655429 , 6655729 update heap/clust set amount = amount + 0 group between 6955429 , 6955729 update heap/clust set amount = amount + 0 group between 7195542 , 7155729 the results of benchmark heap:
rows reads cpu elapsed ----- ----- ----- -------- 1503 3013 31ms 175ms 401 806 0ms 22ms 2700 5409 47ms 100ms 0 3 0ms 0ms 2953 5915 31ms 88ms 0 0 0ms 0ms update on 9 mar 2011:
cmd.commandtext = "update heap set amount = amount + @id group between @id , @id+1000";
- 811 rows have > 0 cpu , affect more 0 rows
counter minimum maximum average weighted --------- ------- ---------- ------- --------- rowcounts 1001 69788 5598 811 cpu 15 873 56 0.01199 reads 2080 167593 11809 2.11217 writes 0 1687 121 0.02170 duration 0.6705 514.5347 17.2041 0.00344 end of update on 9 mar 2011.
the results of benchmark clust:
rows reads cpu elapsed ----- ----- ----- -------- 1503 9126 16ms 35ms 401 2444 0ms 4ms 2700 16385 31ms 54ms 0 3 0ms 0ms 2953 17919 31ms 35ms 0 0 0ms 0ms update on 9 mar 2011:
cmd.commandtext = "update clust set amount = amount + @id group between @id , @id+1000";
- 853 rows have > 0 cpu , affect more 0 rows
counter minimum maximum average weighted --------- ------- ---------- ------- --------- rowcounts 1001 69788 5420 - cpu 15 594 50 0.01073 reads 6226 432237 33597 6.20450 writes 0 1730 110 0.01971 duration 0.9134 193.7685 8.2919 0.00155 end of update on 9 mar 2011.
delete benchmarks
the third batch of queries ran delete statements
delete heap/clust group between 5678910 , 5679410 delete heap/clust group between 6234567 , 6234967 delete heap/clust group between 6455429 , 6455729 delete heap/clust group between 6655429 , 6655729 delete heap/clust group between 6955429 , 6955729 delete heap/clust group between 7195542 , 7155729 the result of benchmark heap:
rows reads cpu elapsed ----- ----- ----- -------- 1503 10630 62ms 179ms 401 2838 0ms 26ms 2700 19077 47ms 87ms 0 4 0ms 0ms 2953 20865 62ms 196ms 0 4 0ms 9ms update on 9 mar 2011:
cmd.commandtext = "delete heap group between @id , @id+1000";
- 724 rows have > 0 cpu , affect more 0 rows
counter minimum maximum average weighted --------- ------- ---------- ------- --------- rowcounts 192 69788 4781 - cpu 15 499 45 0.01247 reads 841 307958 20987 4.37880 writes 2 1819 127 0.02648 duration 0.3775 1534.3383 17.2412 0.00349 end of update on 9 mar 2011.
the result of benchmark clust:
rows reads cpu elapsed ----- ----- ----- -------- 1503 9228 16ms 55ms 401 3681 0ms 50ms 2700 24644 46ms 79ms 0 3 0ms 0ms 2953 26955 47ms 92ms 0 3 0ms 0ms update on 9 mar 2011:
cmd.commandtext = "delete clust group between @id , @id+1000";
- 751 rows have > 0 cpu , affect more 0 rows
counter minimum maximum average weighted --------- ------- ---------- ------- --------- rowcounts 144 69788 4648 - cpu 15 764 56 0.01538 reads 989 458467 30207 6.48490 writes 2 1830 127 0.02694 duration 0.2938 2512.1968 24.3714 0.00555 end of update on 9 mar 2011.
insert benchmarks
the last part of benchmark execution of insert statements.
insert heap/clust (...) values (...), (...), (...), (...), (...), (...)
the result of benchmark heap:
rows reads cpu elapsed ----- ----- ----- -------- 6 38 0ms 31ms update on 9 mar 2011:
string str = @"insert heap (group, currency, year, period, domain_id, mtdamount, mtdamount, ytdamount, amount, ytd_restated, restated, auditdate, audituser) values"; (int x = 0; x < 999; x++) { str += string.format(@"(@id + {0}, 'eur', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test'), ", x); } str += string.format(@"(@id, 'cad', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test') ", 1000); cmd.commandtext = str;
- 912 statements have > 0 cpu
counter minimum maximum average weighted --------- ------- ---------- ------- --------- rowcounts 1000 1000 1000 - cpu 15 2138 25 0.02500 reads 5212 7069 6328 6.32837 writes 16 34 22 0.02222 duration 1.6336 293.2132 4.4009 0.00440 end of update on 9 mar 2011.
the result of benchmark clust:
rows reads cpu elapsed ----- ----- ----- -------- 6 50 0ms 18ms update on 9 mar 2011:
string str = @"insert clust (group, currency, year, period, domain_id, mtdamount, mtdamount, ytdamount, amount, ytd_restated, restated, auditdate, audituser) values"; (int x = 0; x < 999; x++) { str += string.format(@"(@id + {0}, 'eur', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test'), ", x); } str += string.format(@"(@id, 'cad', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test') ", 1000); cmd.commandtext = str;
- 946 statements have > 0 cpu
counter minimum maximum average weighted --------- ------- ---------- ------- --------- rowcounts 1000 1000 1000 - cpu 15 2403 21 0.02157 reads 6810 8997 8412 8.41223 writes 16 25 19 0.01942 duration 1.5375 268.2571 6.1463 0.00614 end of update on 9 mar 2011.
conclusions
although there more logical reads going on when accessing table clustered & nonclustered index (while using nonclustered index) performance results are:
- select statements comparable
- update statements faster clustered index in place
- delete statements faster clustered index in place
- insert statements faster clustered index in place
of course benchmark limited on specific kind of table , limited set of queries, think based on information can start saying virtually better create clustered index on table.
update on 9 mar 2011:
as can see added results, conclusions on limited tests not correct in every case.
the results indicate statements benefit clustered index update statements. other statements 30% slower on table clustered index.
some additional charts plotted weighted duration per query heap vs clust. 
as can see performance profile insert statements quite interesting. spikes caused few data points take lot longer complete. 
end of update on 9 mar 2011.
Comments
Post a Comment