heap_table vs clust_table

Hi experts

i have two table
create table heap_table
(
      col1 integer identity,
      col2 integer ,
      col3 varchar(50)
)

create table clust_table
(
      col1 integer identity primary key clustered,
      col2 integer ,
      col3 varchar(50)
)

Some data
--Insert 100 rows to start with
declare @i integer =0;
while @i<101
begin
      set @i = @i + 1
      insert into heap_table values (@i, cast(@i%5 as varchar))
      insert into clust_table values (@i, cast(@i%5 as varchar))
end;

--Now 2 million rows
while (select count(*) from clust_table) < 2000000
begin
      insert into heap_table select col2, col3 from clust_table;
      insert into clust_table select col2, col3 from clust_table;
end;

I understand that these results? I understand that the query in the index table cluster should be faster or not?

--query 1
set statistics io on
set statistics time on

      select SUM (col2)
      from heap_table
      where col1 %3 = 1
      group by col3

set statistics io off
set statistics time off
--Table 'heap_table'. Scan count 3, logical reads 8994, physical reads 0, read-ahead reads 0.

-- SQL Server Execution Times:
--   CPU time = 2013 ms,  elapsed time = 1702 ms.

--query 2
set statistics io on
set statistics time on

      select SUM (col2)
      from  [dbo].[clust_table]
      where col1 %3 = 1
      group by col3

set statistics io off
set statistics time off
--Table 'clust_table'. Scan count 3, logical reads 9159, physical reads 0, read-ahead reads 0.

-- SQL Server Execution Times:
--   CPU time = 1810 ms,  elapsed time = 1283 ms.
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eugene ZCommented:
<I understand that these results? I understand that the query in the index table cluster should be faster or not?>
it depends:
as the main idea - yes

however there are situations when heap could be used:

-staging tables used for import/export/ETL processes.
- SELECT * INTO..
-very small  tables

more
SQL Server Best Practices Article
https://msdn.microsoft.com/en-us/library/cc917672.aspx#EFAA
0
David ToddSenior DBACommented:
Hi,

Now if your query had a covering index or included columns, and I'm wondering if the where clause is sargable or not, then you could see some quite different results.

Regards
  David
0
David ToddSenior DBACommented:
Hi,

In checking with your script, and adding another table for a covering index, I get all three queries have the same (or very similar) cost in the execution plan.

That is, according to the execution plans, there is no significant different at this level between the queries and table.

HTH
  David
0
David ToddSenior DBACommented:
Hi,

I changed the where clause to
       where
                  --col1 %3 = 1
                  col1 >= 20
                  and col1 < 49

with the following results
(5 row(s) affected)
Table 'heap_table'. Scan count 9, logical reads 9821, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 279 ms,  elapsed time = 130 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(5 row(s) affected)
Table 'clust_table'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 141 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(5 row(s) affected)
Table 'cover_table'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 67 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

and query plan cost of 8.9554/0.0146/0.0146

which illustrates the comment I made about the where clause not being sargable.

The key is the 3 logical reads for the indexed tables vs nearly 10000 reads for the heap.


HTH
  David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
enrique_aeo, do you still need help with this question?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.