SELECT
 _fkQSC,
 _fkQSCmaster,
 _Name,
 _xGlyph,
 _Indent,
 _Acceptable,
 IFNULL(Y._fkAssessments,FALSE,TRUE) AS _Existing,
 COALESCE(X._Bands,0) AS _MaxBand,
 COALESCE(Y._Band,0) AS _OriginalBand,
 COALESCE(Y._Band,0) AS _NewBand,
 COALESCE(Y._Band,0) / COALESCE(X._Bands,0)  AS _Percent,
 NOT EXISTS (SELECT  _fkQSCmaster FROM RolesQSC Z WHERE Z._fkQSCmaster = M._fkQSC) AS _Settable, <<<<<<<<<<<<<<<<<<<<
 _SpecialInstructions
FROM RolesQSC M
JOIN QSC X ON X._ID = _fkQSC
LEFT OUTER JOIN AssessmentDetails Y ON (Y._fkAssessments = :xAssessments) AND (Y._fkQSC = M._fkQSC) AND (Y._fkAssessor = :xAssessor)
WHERE _fkRoles = :xRole
ORDER BY _Sequence


The marked line boosts the time taken from .031 to .346 on my test data. Strangely enough I want to see if I can improve it.

I've tried using a JOIN instead

JOIN RolesQSC Z ON Z._fkQSCmaster = M._fkQSC

but that really screws things up - every row becomes the same. I can sort some of it out by prefixing the column names with the table.

I've tried NOJOINOPTIMIZE but no difference

Any thoughts anyone?

Roy Lambert

Forget it - I'd removed an index - not sure why or why I don't remember

Roy Lambert