0

A little set up: I have a SQL Server, and that SQL Server has a linked server connection to an Oracle database. All the queries presented here are being run in SSMS against the SQL Server (and from the SQL Server computer).

I have two queries, with what to me appear to be a very minor difference, yet their performance (execution time) is vastly different. One executes in 5 seconds, the other takes 8 minutes.

The sql here is scrubbed of identifying information.

-- QUERY 1 - takes ~5 seconds
insert into [SQLSERVER].[TableA]
select r.*
from [LINKEDSERVER]..[ERP].[TableB] r
where r.[RAISED_DATE] >= CONVERT(VARCHAR(10), DATEADD(day,-7,GETDATE()), 112)
    and r.ID NOT IN
        (
        select ID
        from [SQLSERVER].[dbo].[TableA]
        )
-- QUERY 2 - takes ~8 minutes
insert into [SQLSERVER].[TableA]
select r.*
from [LINKEDSERVER]..[ERP].[TableB] r
where r.[RAISED_DATE] >= '20220901'
    and r.ID NOT IN
        (
        select ID
        from [SQLSERVER].[dbo].[TableA]
        )

To be clear, I am just looking to understand why there's such a performance difference between these.

Thank you!

3
  • 1
    Please run your queries with their actual execution plans enabled and upload the plan XML to Paste The Plan and then add the link to those plans in your post. Wouldn't doubt you're getting a Remote Query operation with one query, and Remote Scan with the other, but your execution plans will answer that question.
    – J.D.
    Nov 9 at 2:30
  • 1
    Difference between DATEADD(day,-7,GETDATE()) and '20220901' is about two months. Depending on data in data [LINKEDSERVER]..[ERP].[TableB] that could explain the performance difference. Nov 9 at 7:32
  • 1
    What is the datatype of RAISED_DATE? Nov 9 at 11:32

1 Answer 1

1

Take a look at the actual execution plans in SSMS. Dollars to donuts that you'll see a warning on the plan with the explicit conversion explaining that a conversion is likely to impact performance. Why? You're limiting the indices available to your query by performing this conversion.

Search sargability for a lot more in depth info.

3
  • 1
    While that's normally a good guess, it looks like the query OP's doing explicit conversion on, is the fast one. So I would bet against that being the issue in this particular case.
    – J.D.
    Nov 9 at 3:31
  • If that indeed is the case (and I'm not sold on it: an index on a varchar column that's really dates does not sound performant) a implicit conversion in the opposite direction could well have the same.impact, on the other query. Nov 9 at 4:04
  • Well considering OP is explicitly converting a date value on the right side of the equals operator to a VARCHAR in the first query, I'd assume that means his field RAISED_DATE is actually stored as a VARCHAR as well (otherwise that would be an arbitrary explicit conversion they chose). If that's true, that means his second query shouldn't be doing any kind of conversion, since both sides of the equals operator are of the VARCHAR type. But yes, anything's possible, and the execution plans I asked OP for should affirm what their issue is.
    – J.D.
    Nov 9 at 4:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.