I have a query that runs in under 1 second on SQL Server 2014. A similar query takes over 12 minutes when run against a table on a linked server running SQL Server 2008. I don't know if it's because it's a linked server or because it's 2008. I found a blog post about TVFs confusing the query optimizer on 2008, but I'm not sure how to apply that info to my situation.
Here's the query on the local table:
select 'E10', InvoiceNum, MiscCode, Description, DocMiscAmt, Type from Live.Erp.InvcMisc where InvoiceNum in ( select distinct ARInvoiceNum from dbo.UnifiedSalesJournal(2018, 5, default, default, default) where DB = 'E10' ) and MiscCode not like '%SHI' and MiscCode not like '%HAN'
Here's the query on the linked table. The difference in the plan is obvious:
select 'E9', InvoiceNum, MiscCode, Description, DocMiscAmt, Type from tmiedb.Epicor905.Dbo.InvcMisc where InvoiceNum in ( select distinct ARInvoiceNum from dbo.UnifiedSalesJournal(2018, 3, default, default, default) where DB = 'E9' ) and MiscCode not like 'SH%' and MiscCode not like 'HD%' and MiscCode <> 'THDL'
I got rid of the loop by rewriting it like this. This runs in about 1 second:
select 'E9', InvoiceNum, MiscCode, m.Description, DocMiscAmt, Type from dbo.UnifiedSalesJournal(2018, 3, default, default, default) i left join tmiedb.Epicor905.Dbo.InvcMisc m on i.ARInvoiceNum = m.InvoiceNum
But if I add a
where clause, this takes 29 minutes.
The data is small. The TVF returns a few thousand rows. The local table contains 10K rows, and the linked table contains 55K rows.
The linked server contains historical data that will never change, so I'm considering importing the whole database into SQL Server 2014. But I'm hoping there's a less drastic solution.
Possibly related: Linked Server behavior. I'm seeing the behavior described in Case 3a. It's calling
sp_execute thousands of times, once for each row returned by the TVF.