Had some deadlocks and massive performance hits with large datasets that involved varchar(MAX) fields..

Added a plan guide with OPTION (MERGE JOIN) and the performance went back to good..

Plan is to...

Something like...

Select

CT_STOCKNO_P,

CT_STOCKSUMMARY,

CT_QUANTITY,

TR_QUANTITY,

TR_UNITCOST,

TR_TYPECODE,

MIN(CASE WHEN TR_TYPECODE = 'PREC' THEN TR_DATE END) AS MINPRECDATE,

MAX(CASE WHEN TR_TYPECODE = 'PREC' THEN TR_DATE END) AS MAXPRECDATE,

MIN(CASE WHEN TR_TYPECODE = 'ISSU' THEN TR_DATE END) AS MINISSUDATE,

MAX(CASE WHEN...