I have a query that I want to optimize
One table has 7 million records and a columnstore index
Optimizer suggests a nonclustered index, but because I use view, I have to FORCESEEK it
The runtime between two queries (one with columnstore index and one with FORCESEEK) is quite short
Which is more efficient? Should I simply stick to the columnstore index and ignore the proposed nonclustered index?
Query with a columnstore index
SQL Server analysis and compilation time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server execution times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server analysis and compilation time:
CPU time = 15 ms, elapsed time = 18 ms.
(4532726 rows affected)
Table & # 39; tableA & # 39 ;. Scan Count 4, logical reads 12422, physical reads 0, prefetches 0, logical reads 60268, physical pricks 0, prefetches 0.
Table & # 39; tableA & # 39 ;. Segment reads 136, segment skipped 0.
(1 line affected)
SQL Server execution times:
CPU time = 12000 ms, elapsed time = 171677 ms.
SQL Server analysis and compilation time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server execution times:
CPU time = 0 ms, elapsed time = 0 ms.
Query with ungrouped FORCESEEK index
SQL Server analysis and compilation time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server execution times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server analysis and compilation time:
CPU time = 26 ms, elapsed time = 26 ms.
(4532726 rows affected)
Table & # 39; tableA & # 39 ;. Scan Count 135214, logical reads 628852, physical reads 0, reads 0, logical reads 17732, physical reads 0, reads 5250.
Table & # 39; tableA & # 39 ;. Segment reads 68, segment skipped 0.
(1 line affected)
SQL Server execution times:
CPU time = 19609 ms, elapsed time = 164944 ms.
SQL Server analysis and compilation time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server execution times:
CPU time = 0 ms, elapsed time = 0 ms.
