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
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.
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
- Laptop, full dataset, actual execution plan
- Server, full dataset, estimated execution plan
- Laptop, top 10, actual execution plan
- Server, top 10, actual execution plan
Full dataset actual execution plan for the server is not included, as I'd rather not spend 15 hours generating it