Excess Query Time - suggestions

1

I have a 2 query results that I load into 2 table variables A & B.
From using statements like these I have narrowed it down to where the delay occurs

SET @end_t = GETDATE()
SET @ms = DATEDIFF(ms, @start_time, @end_t)
PRINT ' Query for Table B data - milsecs ' + CAST(@ms as Varchar(15))

declare @B table ( LN int, Customer varchar(25), pF int);
insert into @B(LN,customer, pF)
Select .....

I found that the Query itself ( select ) when instrumented is taking 1+ second
But inserting it into the table variable is tripling the time. (My test case only returns 20 records)

Any ideas on why... or an alternate approach.

2

20 rows should be so fast it wouldn't matter anyway.

But for any amount of post-load processing other than simply scanning the table, a temp table normally performs much better than a table variable.

3

I am new to understanding an execution plan - normally havent looked. In this case I did since the results seemed so baffling ( unexpected ). It says 95% of the time is in a table scan of one table that has an inner join with another table. It doesnt make sense to me that sticking the results into a table variable ( especially so few ) would be a time hit. I can see what the diff would be with temp tables.

Here's my print out ... this is from a script, in a stored proc its faster but still poor

14 row(s) affected)
Query for Table B data - milsecs 1260

(14 row(s) affected)
Now we have table b filled of data - milsecs 5203

(14 row(s) affected)

4

Longshot: TempTable has different datatype for the columns and implicit CONVERT is involved. That's not going to take seconds on 20 rows though ...

I would look at the Query Plan (in text form!) for the Query on its own, and then for the INSERT version. Perhaps SQL is, for some reason, choosing different indexes? You don't have a PKey on the @TempTable, but if you did I can imagine that SQL might use a different strategy to get the data "in PKey order" so-to-speak, so maybe something like that is going on.

SET SHOWPLAN_TEXT ON
GO

-- ... put query here ...

SET SHOWPLAN_TEXT OFF
GO
5

Thanks - when I examined the execution plan it suggested added non clustered index on two fields conditional fields ( used in the where clause used on the inner join. I had not done this in a while and had to read about it to refresh my memory. ( https://msdn.microsoft.com/en-us/library/ms186342.aspx - used SQL Server Managment Studio option - so interactive with mouse )

now my query times for same are
(14 row(s) affected)
Query for Table B data - milsecs 303

(14 row(s) affected)
Now we have table b filled of data - milsecs 596

A 9 fold improvement Thanks !