The optimiser estimates one row and hence generates a plan that's optimal for a very small number of rows. 125000 is not a small number of rows and hence the plan is very, very sub-optimal.

You have table scans everywhere! And as the inner sources for nested loop joins!!!!! No wonder this is slow. In fact, I'm surprised it only takes 40 min.

Quick suggestions (as in, I looked over it quickly, not as in they're quick for you to implement)

Index on tblRegularItems (IsArchived, Level1Type, Level2Type, Level3Type, EDPNo)

Index on tblProductSizeWidthColour (fstrLongSKU, fintEcometryID) (btw, what does this table store?)

Index on tblProduct (fstrShortSKU)

Index on ltblSize (fstrSizeValue)

Index on ltblWidth (fstrWidthValue)

Index on ltblColour (fstrColourName)

Once you've added all those, run the query again and post the revised exec plan.