Home Forums SQL Server 2014 Development - SQL Server 2014 Update Query Performance RE: Update Query Performance

  • March 20, 2017 at 9:37 am

    #1934024

    Sergiy - Monday, March 20, 2017 4:23 AM

    I don't think that TOP 1 does what you think it does.

    It does exactly what I think it does.It makes sure that there is no way to get an error "subquery returned more than 1 value".It should not be happening anyway, because the record is filtered out by an exact match to a PK value.Only 1 record should be returned.Therefore ORDER BY would be only an unnecessary overhead.Unless the table definition is changed.Then the logic of the query (probably not only this one) would require revisiting.But while developers working on it the TOP 1 would prevent run-time errors in Production.

    My apologies. I had a brain cloud and had it in my head that there were multiple Inventory rows per Product - like a ledger system (carried over from another post I had answered recently). 🙂

    Curious to think that a conditional, select (with lock(s) taken as additional overhead) and top operation prior to the update would lead to overall improved performance. Not saying it isn't viable for certain workloads, just an interesting proposition.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service