Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 2 of 2 total |
Speeding up query |
Thu, Aug 4 2016 1:43 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Aug 4 2016 1:49 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Forget it - I'd removed an index - not sure why or why I don't remember
Roy Lambert |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |