1

I'm dealing with a performance issue that seems to be server dependent. The server in question is version 15.0.4316.3 (X64). Here's the code below.

SELECT b1.ID
    ,ParentID = (
        SELECT TOP 1 b2.ID
        FROM stgBOMv3 b2
        WHERE b2.id <= b1.id
            AND b2.BOMLevel <= 0
        ORDER BY ID DESC
        )
FROM stgBOMv3 b1
WHERE b1.BOMLevel = 1

The query runs fine on my laptop on the exact same dataset, version 15.0.2000.5 (X64). Specifically, it takes about 1 minute to run on my laptop and almost 15 hours on the server. The table has over 6 million rows and appropriate indexes on ID and BOMLevel.

Are there any obvious things that I'm missing that could be causing my query to take 900 times longer on this server?


Edited to add some extra details as requested. As to the purpose of this query, this query has been fit for purpose for years. It's only after migrating to a new server that the query performance tanked.

Table creation:

CREATE TABLE dbo.stgBOMv3 (
    id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
    ,ParentID INT NULL FOREIGN KEY REFERENCES stgBOMv3(id)
    ,TopID INT NULL FOREIGN KEY REFERENCES stgBOMv3(id)
    ,BOMLevel INT NULL
    ,ItemNumber NVARCHAR(255) NULL --and other columns not included
    )
GO

CREATE NONCLUSTERED INDEX [IX_stgBOMv3_BOMLevel]
ON [dbo].[stgBOMv3] ([BOMLevel])
GO

Execution plans:

Full dataset actual execution plan for the server is not included, as I'd rather not spend 15 hours generating it

6
  • We would need to see the execution plan of the query from SSMS in order to determine if that query is optimized and if the indexing is appropriate.
    – rvsc48
    Jul 20, 2023 at 13:53
  • Can you provide the CREATE TABLE for stgBOMv3 - including indexes? And both execution plans Jul 20, 2023 at 13:58
  • My spidey senses feel that your query has potential to be logically incorrect too. Are you trying to build a BOM list? This would probably be better handled with window functions or a recursive CTE, both for logical correctness and performance. But as rvsc48 mentioned, we can't really advise on performance issues without the execution plan. You can upload the actual execution plan to Paste The Plan and a link to it in your Post.
    – J.D.
    Jul 20, 2023 at 14:02
  • 1
    Thanks for the responses. I've added the extra details you've requested. Jul 20, 2023 at 14:32
  • So the decision to do a scan on the inside of the nested loops rather than a seek looks "interesting" - probably a row goal issue. Would you consider a more radical query rewrite? I'd be minded to use window functions here too Jul 20, 2023 at 14:48

1 Answer 1

0

The bad plan is doing a backward ordered scan on the inside of the nested loops and will just evaluate the b2.id < b1.id AND b2.BOMLevel <= 0 predicate against every row until it finds the first one matching the predicate.

enter image description here

This plan is catastrophically bad for small b1.id as it needs to scan the whole table before finding the first row matching the predicate.

I assume that it is undercosting the scan because of the TOP 1. See How (and why) does TOP impact an execution plan?

The good plan does seek the b2.id < b1.id part and then does a backward direction scan from that point in the index until it finds the first row matching the b2.BOMLevel <= 0 predicate.

The seek looks objectively better in this case so you could try just forcing that. I also changed the <= to < because we know ID is unique and that a row can't meet both of BOMLevel <= 0 and BOMLevel = 1.

SELECT b1.ID
    ,ParentID = (
        SELECT TOP 1 b2.ID
        FROM stgBOMv3 b2 WITH (FORCESEEK)
        WHERE b2.id < b1.id
            AND b2.BOMLevel <= 0
        ORDER BY ID DESC
        )
FROM stgBOMv3 b1
WHERE b1.BOMLevel = 1

But it looks like the query is returning all rows where BOMLevel = 1 and the ID value from the latest preceding row where BOMLevel <= 0 aliased as ParentID.

TBH this does seem quite confusing as you do have a separate column in the table also called ParentID but you state that it has been "fit for purpose for years".

So I'd be hopeful that the below out performs the current "good" plan

WITH b1 AS
(
SELECT ID, 
       BOMLevel,
       ParentID  = MAX(CASE WHEN BOMLevel <= 0 THEN ID END) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING)
FROM stgBOMv3
)
SELECT ID, 
       ParentID  
FROM b1
WHERE b1.BOMLevel = 1
1
  • 1
    So far this is working incredibly well. At the very least I'm getting the same results as my standard query at a much quicker pace, especially on the server. I'll poke around with this but I think this a massive step in the right direction. Thanks. Jul 20, 2023 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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