Query runs in the same time despite having 300 times as many rows

Question

I have two (almost) identical queries:

;with cteMasterCreateExecAsXml as (
    SELECT guid, CAST(ExecutionOrder AS xml) as x
    FROM (SELECT guid, ExecutionOrder 
          FROM @StrategiesToCreate sc
          JOIN InFusion_Data.dbo.compare_FoxStrategy fs ON fs.Guid = sc.MasterGuid
          WHERE fs.LinkedToTemplate = 0) k
) -- selects ~45 rows

SELECT MasterBIV.StrategyTagname, MasterBIV.Tagname, MasterBIV.Name,
       b.blk.value('for $i in . return count(../*[. << $i]) + 1', 'int') as ExecOrder
       INTO #MasterCreateExecOrder
FROM cteMasterCreateExecAsXml
CROSS APPLY x.nodes('//ExecutionOrder/Block') as b(blk)
JOIN InFusion_Data.dbo.compare_BlockInfoView MasterBIV ON MasterBIV.BlockGuid = b.blk.value('@Id', 'uniqueidentifier')

And

;with cteMasterExecAsXml as (
    SELECT guid, CAST(ExecutionOrder AS xml) as x
    FROM (SELECT guid, ExecutionOrder FROM @StrategiesToCompare sc
    JOIN InFusion_Data.dbo.compare_FoxStrategy fs ON fs.Guid = sc.MasterGuid) k
) -- selects ~17000 rows

SELECT MasterBIV.StrategyTagname, MasterBIV.Tagname, MasterBIV.Name,
       b.blk.value('for $i in . return count(../*[. << $i]) + 1', 'int') as ExecOrder
       INTO #MasterExecOrder
FROM cteMasterExecAsXml
CROSS APPLY x.nodes('//ExecutionOrder/Block') as b(blk)
JOIN InFusion_Data.dbo.compare_BlockInfoView MasterBIV ON MasterBIV.BlockGuid = b.blk.value('@Id', 'uniqueidentifier')

According the SQL execution plan they both take the same amount of time. The first one deals with 45 rows and the second one deals with ~17000 rows. This makes me think that many rows are being selected and converted to xml in both queries.

Unfortunately because I need to do the query across servers I can't use an XML column in the schema.

Any idea what's going on here or how I can speed up my queries?

Answer 1

As I see, the diffence is at "WHERE fs.LinkedToTemplate = 0" condition.

According the SQL execution plan they both take the same amount of time. The first one deals with 45 rows and the second one deals with ~17000 rows. This makes me think that many rows are being selected and converted to xml in both queries.

It looks like you have no table index by LinkedToTemplate column, so for both queries full table scan is needed. So to improve performance you should create index by LinkedToTemplate column.