If all of the self-joins are needed, some filtered indexes might also help.

As always .... TEST, TEST, TEST

CREATE NONCLUSTERED INDEX ix_Design_Parts_BOMitem
ON design (design, designAlternative, version, class, operation,...

I would try to push my predicates up as high as possible, then isolate my top-level values, before getting the rest of the data.

Something like this

;WITH cteVersion...

If you have multiple threads, the inserting of VALUES could result in duplicates.

I would modify this to use the WHERE NOT EXISTS

IF (@@RWOCOUNT = 0)
BEGIN
  INSERT...

Why not pass your values to a stored procedure.

Then in the stored procedure you can do a proper upsert pattern

-- Inherent in the WHERE clause is the...

Adding to the above, this line in your WHERE clause is basically converting your LEFT JOINS to INNER JOINS

and t3.description = 'faulty device'

Just a thought - SQL 2005 still came bundled with SSMS built in.

So maybe RDP onto the box, and see if SSMS was installed.

Question is a duplicate post

https://www.sqlservercentral.com/forums/topic/query-output-results#post-4453822

A CROSS TAB query will work here.  You just need to know how many sets there are

WITH cteGroup AS (
  SELECT *
    ...

duplicate post deleted

If you convert your UPDATE to a SELECT, you will see that you cannot filter the way that you are trying.  The LAG works within your filtered results.

            
August 13, 2024 at 7:04 am
            
#4444384

            
            
                                                
        

How to calculate the product of all of the denominators for each group?  Why...

This took a bit of head scratching.  I managed to solve it, assuming that there are a max of 3 parts per group (as per your example).

I then added a...

Create some sample data

DECLARE @SAMPLE_SIZE BIGINT = 1000;

DECLARE
      @MinInt       int = 10000
    ,...

This article from Brent Ozar might help

https://www.brentozar.com/archive/2018/09/sql-server-2019-faster-table-variables-and-new-parameter-sniffing-issues/

Duplicate post removed