Post reply

Query Efficiency

  • January 24, 2007 at 6:44 pm

    #122743

    Hi All,

    Just a question I have the following query and am wondering as to whether it is as efficient as it could be

    Select CLI_CODE,

    PROJ_CODE,

                 Coalesce((select avg(EXPECTEDTA)

                 from PROFJOB_CUIDUSER PJCU, PROFJOB_CUID_SCHEME_ANALYTE PJCSA, PROFJOB_CUID PJC, PROFJOB PJ

                 where PJC.PRO_JOB = PJ.PRO_JOB

                 and PJC.PRO_JOB = PJCSA.PRO_JOB

                 and PJC.CUID = PJCSA.CUID

                 and PJCU.PRO_JOB = PJC.PRO_JOB

                 and PJCU.CUID = PJC.CUID

                 and PJC.VALIDATED >= '20070101 00:00:00'

                 and PJC.VALIDATED <= '20070125 10:16:10'

                 and PJCSA.ANALYTESTATUS <> 'LNR'

                 and PJC.SAMPLETYPE = 'UNK'

                 and PJ.CLI_CODE = PROFJOB.CLI_CODE

                 and PJ.PROJ_CODE = PROFJOB.PROJ_CODE),0) AS 'AVERAGE TA',

                 (select COUNT(DISTINCT PJC.CUID)

                 from PROFJOB_CUIDUSER PJCU, PROFJOB_CUID_SCHEME_ANALYTE PJCSA, PROFJOB_CUID PJC, PROFJOB PJ

                 where PJC.PRO_JOB = PJ.PRO_JOB

                 and PJC.PRO_JOB = PJCSA.PRO_JOB

                 and PJC.CUID = PJCSA.CUID

                 and PJCU.PRO_JOB = PJC.PRO_JOB

                 and PJCU.CUID = PJC.CUID

                 and PJC.VALIDATED >= '20070101 00:00:00'

                 and PJC.VALIDATED <= '20070125 10:16:10'

                 and PJCSA.ANALYTESTATUS <> 'LNR'

                 and PJC.SAMPLETYPE = 'UNK'

                 and PJCU.RATIOTA < 0.9

                 and PJ.CLI_CODE = PROFJOB.CLI_CODE

                 and PJ.PROJ_CODE = PROFJOB.PROJ_CODE) AS '<0.9',

    from JOB

    group by CLI_CODE, PROJ_CODE

    order by CLI_CODE

    It basically has an outer query which selects a client and a project and then selects aggregate information in the subquery using the outer Project and Job Pair.

    Can anyone suggest if this is the most effcient way of performing this type of operation??

    Any comments will be greatly appreciated

    FYI will produce something like

    Client    Project            Avg TA    <0.9

    GEOLOGY    DRILL-CAPEL    15.0    91.1

     

  • January 24, 2007 at 11:26 pm

    #685424

    I know the way.

    Normalize your data and get rid of fat tables.

    _____________
    Code for TallyGenerator

  • January 25, 2007 at 4:22 am

    #685467

    Subselects in the select clause are extremely inefficient. Move them out into joins and it'll be a lot better.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 25, 2007 at 8:54 am

    #685593

    Thanks Guys,

    Gail,

    when you move them to outer joins is that just a matter of moving the subqueries to the From clause and then using the correct ansi joins from there

    Cheers

  • January 25, 2007 at 9:31 am

    #685611

    Pretty much, though you'll have to check and make sure you get the same results.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 25, 2007 at 6:19 pm

    #685730

    Cheers

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply