Azure SQL, Clustered Columnstore Index, "TOP" performance

1
votes

I have a question about using Top with tables with Clustered Clustered Index on SQL Azure.

Both of the tables have Clustered Columnstore Index, table HeaderTable has 300K rows, table ValuesTable has 6.5M rows. 

-- with no "Top"
--responce after 2 sec
declare @Date datetime  = getdate()
select  zp.idCol1, Value1, zp.idcol2 from [HeaderTable] zp 
    inner join [dbo].[ValuesTable] zpp 
        on zp.idcol2 = zpp.idcol2
            where zp.Date > @Date-30 and zp.Date < @Date-10 and zp.idCol1>0 and zpp.Value2 = 'SZT'
                    order by idcol2
go 

-- with  "Top 100"  
--responce after 27 sec
declare @Date datetime  = getdate()
select top 100 zp.idCol1, Value1, zp.idcol2 from [HeaderTable] zp
    inner join [dbo].[ValuesTable] zpp 
        on zp.idcol2 = zpp.idcol2
            where zp.Date > @Date-30 and zp.Date < @Date-10 and zp.idCol1>0 and zpp.Value2 = 'SZT'
                    order by idcol2

go 

-- Result into Temporary Table and Select top 100  from Temporaty Table 
-- responce after  2 sec

declare @Date datetime  = getdate()
select  zp.idCol1, Value1, zp.idcol2 into #d  from [HeaderTable] zp 
    inner join [dbo].[ValuesTable] zpp
        on zp.idcol2 = zpp.idcol2
            where zp.Date > @Date-30 and zp.Date < @Date-10 and zp.idCol1>0 and zpp.Value2 = 'SZT'

select top 100 * from #d order by #d.idcol2
drop table #d
go

As You see the top operation in the second query is extremely slow. Maybe someone has some hints with this problem?

2
sql-serverazurecolumnstore
You can't point to a particular element of a query plan and hold it responsible like that. You are pretty much claiming that TOP is always slow which can't be true. Post the actual execution plans as XML somewhere. - usr
Here's the plan: link - Robert G.

2 Answers

2
votes

This is optimized in the enhancements in the new (compatibility level 130, compatibility level 130 is currently supported as preview, and not yet as generally available) database on Azure.

ALTER DATABASE <dbname> SET COMPATIBILITY_LEVEL = 130

makes the difference.

1
votes

The 2nd execution plan is appalling. SQL Server is destroying all Columnstore benefits by buffering the Columnstore into a rowstore temp table... This is a quality problem with the query optimizer because this strategy never makes sense under any circumstances.

Try to convince SQL Server that the TOP does nothing:

DECLARE @top BIGINT = 100;
SELECT TOP (@top) ...
OPTION (OPTIMIZE FOR (@top = 100000000000000000000000000000000));