CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Metaphor
Metaphor

Reputation: 6405

SELECT takes 1 second INSERT @TableVar SELECT takes 45 seconds

I have a select statement that executes sub-second on a table of 255 million rows. The result is approximately 50 rows.

When I try to do an INSERT @Tbl SELECT ..., the query takes 45 seconds.

Can someone explain this to me?

Here is the complete batch. Timer is 127 seconds. When insert line is commented out, Timer is 2 seconds.

DBCC DROPCLEANBUFFERS
GO

DBCC FREEPROCCACHE
GO


declare @fr datetime = '2013-01-01', @to datetime = '2013-09-01'
declare @TempTable table (Title varchar(50), PlayCount int, Wt float)

declare @t1 datetime = getdate()

    insert @TempTable
    select Title, PlayCount, MaxCount * 1.0 / PlayCount as Weight
    from (
        select l.SkinDescription as Title, count(*) as PlayCount, max(count(*)) OVER() AS MaxCount
        from LegalConfiguration l 
        join Play p on p.LegalConfigNumber = l.SequenceNumber
        where p.CurrentDate between @fr and @to
        group by l.SkinDescription
    ) sub

declare @t2 datetime = getdate()

select * from @TempTable
select datediff(ss,@t1,@t2) as timer

Upvotes: 0

Views: 220

Answers (2)

mrtig
mrtig

Reputation: 2267

Table variables like @Tbl limit the query execution to a single thread. Change your code to use temp tables. Like so:

select EPSName, count(*) as PlayCount, max(count(*)) OVER() AS MaxCount
into #Tbl
from LegalConfiguration l 
join Play p on p.LegalConfigNumber = l.SequenceNumber
where p.CurrentDate between @StartDate and @EndDate
group by EPSname

or 

CREATE TABLE #Tbl (EPSName VARCHAR(100),PlayCount INT, MaxCount INT)

INSERT #Tbl
select EPSName, count(*) as PlayCount, max(count(*)) OVER() AS MaxCount
from LegalConfiguration l 
join Play p on p.LegalConfigNumber = l.SequenceNumber
where p.CurrentDate between @StartDate and @EndDate
group by EPSname

You may also try adding an OPTION(RECOMPILE) to your original insert query.

The reason why parallelism does not work for table variables is explained here. This is an important excerpt from the article:

When the queries are compiled neither the table variable nor temporary table are populated and the query optimizer assumes “minimum” number of rows and that is always 1 in SQL Server. This estimation is used when SQL Server generates the “Estimated” query plan. While the “Estimated” and “Actual” query plans are same in most cases, there are some exception. If a table (temporary or permanent) is created in the same batch with the query, SQL Serve has to recompile the query since the query definition is unknown when the batch is compiled the first time. This is not the case for table variables.

Upvotes: -1

Woot4Moo
Woot4Moo

Reputation: 24316

My guess is your sub-second select is a cached result. Whereas the Insert @Tbl Select forces a cache flush and therefore 45 seconds is closer to reality on youre time that is needed. Also, the subsecond select implies that either all 255 million rows fit into memory or that it is partially cached and you are only returning the top N results in your copy of sql developer therefore allowing the application to page.

Upvotes: 2

Related Questions