Skip to main content
B
Bachi_Boy
Participant
May 6, 2009
Question

Long page load / <cfquery> issue

  • May 6, 2009
  • 2 replies
  • 830 views

Hi all,

I am developing a CFM page that is essentially a data dump of data that resides in our AS400.  This is really my first coldfusion project, so I am still learning a few ins and outs of the language.  I have a query, and it's taking over 15 minutes to return data.  Is there a tag I could use to speed this along, or is my syntax wrong, or any query optimization suggestions?  When I remove my <cfquery tag> everything loads fine. Any assistance would be appreciated.  Thanks in advance!!!  Here is my code:

<cfquery name="GetData" datasource="Web_Catalog" result="GetDataResult" >

          SELECT     DISTINCT WSFILE001.DEL1.DDEAL## AS DEAL## , WSFILE001.DEL1.DVER## AS VERSION##, WSFILE001.PRD1.PGROUP AS GROUP, WSFILE001.PRD1.PPROD## AS ITEM##, 
        WSFILE001.PRD1.PDESC AS PRODUCT_DESC, WSFILE001.PRD1.PSIZE AS SIZE, 
        (SELECT MAX(WSFILE001.COS1.CFOBP) FROM WSFILE001.COS1 WHERE WSFILE001.PRD1.PPROD## = WSFILE001.COS1.COSPRD AND WSFILE001.COS1.COSDAT < '1090505')   AS FOB, 
        (SELECT MAX(WSFILE001.COS1.CFRTP) FROM WSFILE001.COS1 WHERE WSFILE001.PRD1.PPROD## = WSFILE001.COS1.COSPRD AND WSFILE001.COS1.COSDAT < '1090505') AS FREIGHT, 
        WSFILE001.COS1.CPURCP AS PA,
        (SELECT MAX(WSFILE001.COS1.CDEIM) FROM WSFILE001.COS1 WHERE WSFILE001.PRD1.PPROD## = WSFILE001.COS1.COSPRD AND WSFILE001.COS1.COSDAT < '1090505') AS OCEAN_FREIGHT,
        WSFILE001.COS1.CDUTYP AS IMPORT_DUTY, WSFILE001.COS1.CPBANK AS BANK, WSFILE001.COS1.CMISC AS MISC, WSFILE001.COS1.COTAXP AS FEDERAL_TAXES, WSFILE001.COS1.CSTAXC AS STATE_TAXES, 
        (SELECT MAX(WSFILE001.PRC1.PSEL8P) FROM WSFILE001.PRC1 WHERE WSFILE001.PRD1.PPROD## = WSFILE001.PRC1.PRCPRD AND WSFILE001.COS1.COSDAT < '1090505') AS LANDED,          
        (SELECT MAX(WSFILE001.PRC1.PSEL1P) FROM WSFILE001.PRC1 WHERE WSFILE001.PRD1.PPROD## = WSFILE001.PRC1.PRCPRD AND WSFILE001.COS1.COSDAT < '1090505') AS LIST_PRICE, 
        WSFILE001.NDEP1.NPRC01 AS DA, 
        ((SELECT MAX(WSFILE001.PRC1.PSEL8P) FROM WSFILE001.PRC1 WHERE WSFILE001.PRD1.PPROD## = WSFILE001.PRC1.PRCPRD AND WSFILE001.COS1.COSDAT < '1090505') - WSFILE001.NDEP1.NPRC01) AS NETCOST, 
        WSFILE001.DEL1.DM1 AS QUANTITY

        FROM      WSFILE001.DEL1, WSFILE001.PDEL, WSFILE001.PRD1, WSFILE001.COS1, WSFILE001.PRC1, WSFILE001.NDEP1, WSFILE001.DIS1
        
        WHERE      
             WSFILE001.DEL1.DDEAL## > '71' AND
               WSFILE001.DEL1.DDEAL## < '1400' AND 
             WSFILE001.PRD1.PGROUP = WSFILE001.PDEL.PDGRUP AND 
            WSFILE001.PRD1.PPROD## = WSFILE001.COS1.COSPRD AND
            WSFILE001.PRD1.PPROD##  = WSFILE001.NDEP1.NETPRD AND     
            WSFILE001.PRD1.PPROD## = WSFILE001.PRC1.PRCPRD AND
            WSFILE001.PRD1.PSUPPL = WSFILE001.PDEL.PDCMP AND


        WSFILE001.DEL1.DDEAL## > 0 AND WSFILE001.DEL1.DDEAL## =
        COALESCE  (CASE WHEN WSFILE001.PDEL.PDX = 0 THEN NULL ELSE WSFILE001.PDEL.PDX END,
                              CASE WHEN WSFILE001.PDEL.PDW = 0 THEN NULL ELSE WSFILE001.PDEL.PDW END,
                              CASE WHEN WSFILE001.PDEL.PDV = 0 THEN NULL ELSE WSFILE001.PDEL.PDV END,
                              CASE WHEN WSFILE001.PDEL.PDU = 0 THEN NULL ELSE WSFILE001.PDEL.PDU END,
                              CASE WHEN WSFILE001.PDEL.PDT = 0 THEN NULL ELSE WSFILE001.PDEL.PDT END,
                              CASE WHEN WSFILE001.PDEL.PDS = 0 THEN NULL ELSE WSFILE001.PDEL.PDS END,
                              CASE WHEN WSFILE001.PDEL.PDR = 0 THEN NULL ELSE WSFILE001.PDEL.PDR END,
                              CASE WHEN WSFILE001.PDEL.PDQ = 0 THEN NULL ELSE WSFILE001.PDEL.PDQ END,
                              CASE WHEN WSFILE001.PDEL.PDP = 0 THEN NULL ELSE WSFILE001.PDEL.PDP END,
                              CASE WHEN WSFILE001.PDEL.PDO = 0 THEN NULL ELSE WSFILE001.PDEL.PDO END,
                              CASE WHEN WSFILE001.PDEL.PDN = 0 THEN NULL ELSE WSFILE001.PDEL.PDN END,
                              CASE WHEN WSFILE001.PDEL.PDM = 0 THEN NULL ELSE WSFILE001.PDEL.PDM END,
                              CASE WHEN WSFILE001.PDEL.PDL = 0 THEN NULL ELSE WSFILE001.PDEL.PDL END,
                              CASE WHEN WSFILE001.PDEL.PDK = 0 THEN NULL ELSE WSFILE001.PDEL.PDK END,
                              CASE WHEN WSFILE001.PDEL.PDJ = 0 THEN NULL ELSE WSFILE001.PDEL.PDJ END,
                              CASE WHEN WSFILE001.PDEL.PDI = 0 THEN NULL ELSE WSFILE001.PDEL.PDI END,
                              CASE WHEN WSFILE001.PDEL.PDH = 0 THEN NULL ELSE WSFILE001.PDEL.PDH END,
                              CASE WHEN WSFILE001.PDEL.PDG = 0 THEN NULL ELSE WSFILE001.PDEL.PDG END,
                              CASE WHEN WSFILE001.PDEL.PDF = 0 THEN NULL ELSE WSFILE001.PDEL.PDF END,
                              CASE WHEN WSFILE001.PDEL.PDE = 0 THEN NULL ELSE WSFILE001.PDEL.PDE END,
                              CASE WHEN WSFILE001.PDEL.PDD = 0 THEN NULL ELSE WSFILE001.PDEL.PDD END,
                              CASE WHEN WSFILE001.PDEL.PDC = 0 THEN NULL ELSE WSFILE001.PDEL.PDC END,
                              CASE WHEN WSFILE001.PDEL.PDB = 0 THEN NULL ELSE WSFILE001.PDEL.PDB END,
                              CASE WHEN WSFILE001.PDEL.PDA = 0 THEN NULL ELSE WSFILE001.PDEL.PDA END)  
    
        AND
    
        WSFILE001.DEL1.DVER##  > 0 AND WSFILE001.DEL1.DVER## = 
        COALESCE  (CASE WHEN WSFILE001.PDEL.PDVX = 0 THEN NULL ELSE WSFILE001.PDEL.PDVX END,
                              CASE WHEN WSFILE001.PDEL.PDVX = 0 THEN NULL ELSE WSFILE001.PDEL.PDVW END,
                              CASE WHEN WSFILE001.PDEL.PDVX = 0 THEN NULL ELSE WSFILE001.PDEL.PDVV END,
                              CASE WHEN WSFILE001.PDEL.PDVU = 0 THEN NULL ELSE WSFILE001.PDEL.PDVU END,
                              CASE WHEN WSFILE001.PDEL.PDVT = 0 THEN NULL ELSE WSFILE001.PDEL.PDVT END,
                              CASE WHEN WSFILE001.PDEL.PDVS = 0 THEN NULL ELSE WSFILE001.PDEL.PDVS END,
                              CASE WHEN WSFILE001.PDEL.PDVR = 0 THEN NULL ELSE WSFILE001.PDEL.PDVR END,
                              CASE WHEN WSFILE001.PDEL.PDVQ = 0 THEN NULL ELSE WSFILE001.PDEL.PDVQ END,
                              CASE WHEN WSFILE001.PDEL.PDVP = 0 THEN NULL ELSE WSFILE001.PDEL.PDVP END,
                              CASE WHEN WSFILE001.PDEL.PDVO = 0 THEN NULL ELSE WSFILE001.PDEL.PDVO END,
                              CASE WHEN WSFILE001.PDEL.PDVN = 0 THEN NULL ELSE WSFILE001.PDEL.PDVN END,
                              CASE WHEN WSFILE001.PDEL.PDVM = 0 THEN NULL ELSE WSFILE001.PDEL.PDVM END,
                              CASE WHEN WSFILE001.PDEL.PDVL = 0 THEN NULL ELSE WSFILE001.PDEL.PDVL END,
                              CASE WHEN WSFILE001.PDEL.PDVK = 0 THEN NULL ELSE WSFILE001.PDEL.PDVK END,
                              CASE WHEN WSFILE001.PDEL.PDVJ = 0 THEN NULL ELSE WSFILE001.PDEL.PDVJ END,
                              CASE WHEN WSFILE001.PDEL.PDVI = 0 THEN NULL ELSE WSFILE001.PDEL.PDVI END,
                              CASE WHEN WSFILE001.PDEL.PDVH = 0 THEN NULL ELSE WSFILE001.PDEL.PDVH END,
                              CASE WHEN WSFILE001.PDEL.PDVG = 0 THEN NULL ELSE WSFILE001.PDEL.PDVG END,
                              CASE WHEN WSFILE001.PDEL.PDVF = 0 THEN NULL ELSE WSFILE001.PDEL.PDVF END,
                              CASE WHEN WSFILE001.PDEL.PDVE = 0 THEN NULL ELSE WSFILE001.PDEL.PDVE END,
                              CASE WHEN WSFILE001.PDEL.PDVD = 0 THEN NULL ELSE WSFILE001.PDEL.PDVD END,
                              CASE WHEN WSFILE001.PDEL.PDVC = 0 THEN NULL ELSE WSFILE001.PDEL.PDVC END,
                              CASE WHEN WSFILE001.PDEL.PDVB = 0 THEN NULL ELSE WSFILE001.PDEL.PDVB END,
                              CASE WHEN WSFILE001.PDEL.PDVA = 0 THEN NULL ELSE WSFILE001.PDEL.PDVA END)      
    
        AND  
    
        WSFILE001.DEL1.DVER##  > 0 AND WSFILE001.DEL1.DM1 = 
        COALESCE  (CASE WHEN WSFILE001.DEL1.DVER## = 12 THEN WSFILE001.DEL1.DM12 ELSE NULL END,
                              CASE WHEN WSFILE001.DEL1.DVER## = 11 THEN WSFILE001.DEL1.DM11 ELSE NULL END,
                              CASE WHEN WSFILE001.DEL1.DVER## = 10 THEN WSFILE001.DEL1.DM10 ELSE NULL END,
                              CASE WHEN WSFILE001.DEL1.DVER## = 9 THEN WSFILE001.DEL1.DM9 ELSE NULL END,
                              CASE WHEN WSFILE001.DEL1.DVER## = 8 THEN WSFILE001.DEL1.DM8 ELSE NULL END,
                              CASE WHEN WSFILE001.DEL1.DVER## = 7 THEN WSFILE001.DEL1.DM7 ELSE NULL END,
                              CASE WHEN WSFILE001.DEL1.DVER## = 6 THEN WSFILE001.DEL1.DM6 ELSE NULL END,
                              CASE WHEN WSFILE001.DEL1.DVER## = 5 THEN WSFILE001.DEL1.DM5 ELSE NULL END,
                              CASE WHEN WSFILE001.DEL1.DVER## = 4 THEN WSFILE001.DEL1.DM4 ELSE NULL END,
                              CASE WHEN WSFILE001.DEL1.DVER## = 3 THEN WSFILE001.DEL1.DM3 ELSE NULL END,
                              CASE WHEN WSFILE001.DEL1.DVER## = 2 THEN WSFILE001.DEL1.DM2 ELSE NULL END,
                              CASE WHEN WSFILE001.DEL1.DVER## = 1 THEN WSFILE001.DEL1.DM1 ELSE NULL END)
    
        AND          
    
        WSFILE001.NDEP1.NPRC01 =  
        COALESCE     (CASE  WHEN  WSFILE001.NDEP1.NPRC01 > MIN(WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC06, WSFILE001.NDEP1.NPRC07, 
                                WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC09, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
             CASE WHEN  WSFILE001.NDEP1.NPRC02 > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC06, WSFILE001.NDEP1.NPRC07, 
                                WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC09, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
             CASE WHEN  WSFILE001.NDEP1.NPRC03 > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC06, WSFILE001.NDEP1.NPRC07, 
                                WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC09, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
             CASE WHEN  WSFILE001.NDEP1.NPRC04 > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC06, WSFILE001.NDEP1.NPRC07, 
                                WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC09, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
             CASE WHEN  WSFILE001.NDEP1.NPRC05 > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC06, WSFILE001.NDEP1.NPRC07, 
                                WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC09, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
             CASE WHEN  WSFILE001.NDEP1.NPRC06 > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC07, 
                                WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC09, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
             CASE WHEN  WSFILE001.NDEP1.NPRC07 > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC06, 
                                WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC09, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
             CASE WHEN  WSFILE001.NDEP1.NPRC08  > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC06,  
                                WSFILE001.NDEP1.NPRC07, WSFILE001.NDEP1.NPRC09, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
             CASE WHEN  WSFILE001.NDEP1.NPRC09  > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC06,  
                                WSFILE001.NDEP1.NPRC07, WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
             CASE WHEN  WSFILE001.NDEP1.NPRC10  > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC06, 
                                WSFILE001.NDEP1.NPRC07, WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC09) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END)
    
    ORDER BY DEAL##

</cfquery>
    This topic has been closed for replies.

    2 replies

    T
    TLC-IT
    Inspiring
    May 7, 2009

    I wonder if your AS/400 can sue you for damages, for giving it a query like that.

    Seriously, friend:  learn SQL.  You need to clearly understand how a query can be built and what the computer actually does in order to carry it out.  This query is grotesque.

    (1) When you list multiple tables in FROM without a JOIN clause, you risk a "cartesian product."  (Google it.)

    (2) You have no need for repeated sub-SELECTs when you could not only calculate all the aggregate values in one sub-SELECT, but could actually eliminate the use of sub-SELECTs altogether.

    (3) You need to clearly understand how your query translates into the 'execution plan' that the query engine actually uses.  You need to seriously learn how to be your computer's best friend... it's best "query optimizer."

    I have seen "hour long" queries be reduced to "a handful of seconds."

    B
    Bachi_BoyAuthor
    Participant
    May 6, 2009

    It might help if I remember to paste in my code

    <cfquery name="GetData" datasource="Web_Catalog" result="GetDataResult" >

            SELECT    DISTINCT WSFILE001.DEL1.DDEAL## AS DEAL## , WSFILE001.DEL1.DVER## AS VERSION##, WSFILE001.PRD1.PGROUP AS GROUP, WSFILE001.PRD1.PPROD## AS ITEM##,
            WSFILE001.PRD1.PDESC AS PRODUCT_DESC, WSFILE001.PRD1.PSIZE AS SIZE,
            (SELECT MAX(WSFILE001.COS1.CFOBP) FROM WSFILE001.COS1 WHERE WSFILE001.PRD1.PPROD## = WSFILE001.COS1.COSPRD AND WSFILE001.COS1.COSDAT < '1090505')   AS FOB,
            (SELECT MAX(WSFILE001.COS1.CFRTP) FROM WSFILE001.COS1 WHERE WSFILE001.PRD1.PPROD## = WSFILE001.COS1.COSPRD AND WSFILE001.COS1.COSDAT < '1090505') AS FREIGHT,
            WSFILE001.COS1.CPURCP AS PA,
            (SELECT MAX(WSFILE001.COS1.CDEIM) FROM WSFILE001.COS1 WHERE WSFILE001.PRD1.PPROD## = WSFILE001.COS1.COSPRD AND WSFILE001.COS1.COSDAT < '1090505') AS OCEAN_FREIGHT,
            WSFILE001.COS1.CDUTYP AS IMPORT_DUTY, WSFILE001.COS1.CPBANK AS BANK, WSFILE001.COS1.CMISC AS MISC, WSFILE001.COS1.COTAXP AS FEDERAL_TAXES, WSFILE001.COS1.CSTAXC AS STATE_TAXES,
            (SELECT MAX(WSFILE001.PRC1.PSEL8P) FROM WSFILE001.PRC1 WHERE WSFILE001.PRD1.PPROD## = WSFILE001.PRC1.PRCPRD AND WSFILE001.COS1.COSDAT < '1090505') AS LANDED,       
            (SELECT MAX(WSFILE001.PRC1.PSEL1P) FROM WSFILE001.PRC1 WHERE WSFILE001.PRD1.PPROD## = WSFILE001.PRC1.PRCPRD AND WSFILE001.COS1.COSDAT < '1090505') AS LIST_PRICE,
            WSFILE001.NDEP1.NPRC01 AS DA,
            ((SELECT MAX(WSFILE001.PRC1.PSEL8P) FROM WSFILE001.PRC1 WHERE WSFILE001.PRD1.PPROD## = WSFILE001.PRC1.PRCPRD AND WSFILE001.COS1.COSDAT < '1090505') - WSFILE001.NDEP1.NPRC01) AS NETCOST,
            WSFILE001.DEL1.DM1 AS QUANTITY

            FROM     WSFILE001.DEL1, WSFILE001.PDEL, WSFILE001.PRD1, WSFILE001.COS1, WSFILE001.PRC1, WSFILE001.NDEP1, WSFILE001.DIS1
           
            WHERE    
                WSFILE001.DEL1.DDEAL## > '71' AND
                WSFILE001.DEL1.DDEAL## < '1400' AND
                WSFILE001.PRD1.PGROUP = WSFILE001.PDEL.PDGRUP AND
                WSFILE001.PRD1.PPROD## = WSFILE001.COS1.COSPRD AND
                WSFILE001.PRD1.PPROD##  = WSFILE001.NDEP1.NETPRD AND   
                WSFILE001.PRD1.PPROD## = WSFILE001.PRC1.PRCPRD AND
                WSFILE001.PRD1.PSUPPL = WSFILE001.PDEL.PDCMP AND


            WSFILE001.DEL1.DDEAL## > 0 AND WSFILE001.DEL1.DDEAL## =
            COALESCE  (CASE WHEN WSFILE001.PDEL.PDX = 0 THEN NULL ELSE WSFILE001.PDEL.PDX END,
                                  CASE WHEN WSFILE001.PDEL.PDW = 0 THEN NULL ELSE WSFILE001.PDEL.PDW END,
                                  CASE WHEN WSFILE001.PDEL.PDV = 0 THEN NULL ELSE WSFILE001.PDEL.PDV END,
                                  CASE WHEN WSFILE001.PDEL.PDU = 0 THEN NULL ELSE WSFILE001.PDEL.PDU END,
                                  CASE WHEN WSFILE001.PDEL.PDT = 0 THEN NULL ELSE WSFILE001.PDEL.PDT END,
                                  CASE WHEN WSFILE001.PDEL.PDS = 0 THEN NULL ELSE WSFILE001.PDEL.PDS END,
                                  CASE WHEN WSFILE001.PDEL.PDR = 0 THEN NULL ELSE WSFILE001.PDEL.PDR END,
                                  CASE WHEN WSFILE001.PDEL.PDQ = 0 THEN NULL ELSE WSFILE001.PDEL.PDQ END,
                                  CASE WHEN WSFILE001.PDEL.PDP = 0 THEN NULL ELSE WSFILE001.PDEL.PDP END,
                                  CASE WHEN WSFILE001.PDEL.PDO = 0 THEN NULL ELSE WSFILE001.PDEL.PDO END,
                                  CASE WHEN WSFILE001.PDEL.PDN = 0 THEN NULL ELSE WSFILE001.PDEL.PDN END,
                                  CASE WHEN WSFILE001.PDEL.PDM = 0 THEN NULL ELSE WSFILE001.PDEL.PDM END,
                                  CASE WHEN WSFILE001.PDEL.PDL = 0 THEN NULL ELSE WSFILE001.PDEL.PDL END,
                                  CASE WHEN WSFILE001.PDEL.PDK = 0 THEN NULL ELSE WSFILE001.PDEL.PDK END,
                                  CASE WHEN WSFILE001.PDEL.PDJ = 0 THEN NULL ELSE WSFILE001.PDEL.PDJ END,
                                  CASE WHEN WSFILE001.PDEL.PDI = 0 THEN NULL ELSE WSFILE001.PDEL.PDI END,
                                  CASE WHEN WSFILE001.PDEL.PDH = 0 THEN NULL ELSE WSFILE001.PDEL.PDH END,
                                  CASE WHEN WSFILE001.PDEL.PDG = 0 THEN NULL ELSE WSFILE001.PDEL.PDG END,
                                  CASE WHEN WSFILE001.PDEL.PDF = 0 THEN NULL ELSE WSFILE001.PDEL.PDF END,
                                  CASE WHEN WSFILE001.PDEL.PDE = 0 THEN NULL ELSE WSFILE001.PDEL.PDE END,
                                  CASE WHEN WSFILE001.PDEL.PDD = 0 THEN NULL ELSE WSFILE001.PDEL.PDD END,
                                  CASE WHEN WSFILE001.PDEL.PDC = 0 THEN NULL ELSE WSFILE001.PDEL.PDC END,
                                  CASE WHEN WSFILE001.PDEL.PDB = 0 THEN NULL ELSE WSFILE001.PDEL.PDB END,
                                  CASE WHEN WSFILE001.PDEL.PDA = 0 THEN NULL ELSE WSFILE001.PDEL.PDA END) 
       
            AND
       
            WSFILE001.DEL1.DVER##  > 0 AND WSFILE001.DEL1.DVER## =
            COALESCE  (CASE WHEN WSFILE001.PDEL.PDVX = 0 THEN NULL ELSE WSFILE001.PDEL.PDVX END,
                                  CASE WHEN WSFILE001.PDEL.PDVX = 0 THEN NULL ELSE WSFILE001.PDEL.PDVW END,
                                  CASE WHEN WSFILE001.PDEL.PDVX = 0 THEN NULL ELSE WSFILE001.PDEL.PDVV END,
                                  CASE WHEN WSFILE001.PDEL.PDVU = 0 THEN NULL ELSE WSFILE001.PDEL.PDVU END,
                                  CASE WHEN WSFILE001.PDEL.PDVT = 0 THEN NULL ELSE WSFILE001.PDEL.PDVT END,
                                  CASE WHEN WSFILE001.PDEL.PDVS = 0 THEN NULL ELSE WSFILE001.PDEL.PDVS END,
                                  CASE WHEN WSFILE001.PDEL.PDVR = 0 THEN NULL ELSE WSFILE001.PDEL.PDVR END,
                                  CASE WHEN WSFILE001.PDEL.PDVQ = 0 THEN NULL ELSE WSFILE001.PDEL.PDVQ END,
                                  CASE WHEN WSFILE001.PDEL.PDVP = 0 THEN NULL ELSE WSFILE001.PDEL.PDVP END,
                                  CASE WHEN WSFILE001.PDEL.PDVO = 0 THEN NULL ELSE WSFILE001.PDEL.PDVO END,
                                  CASE WHEN WSFILE001.PDEL.PDVN = 0 THEN NULL ELSE WSFILE001.PDEL.PDVN END,
                                  CASE WHEN WSFILE001.PDEL.PDVM = 0 THEN NULL ELSE WSFILE001.PDEL.PDVM END,
                                  CASE WHEN WSFILE001.PDEL.PDVL = 0 THEN NULL ELSE WSFILE001.PDEL.PDVL END,
                                  CASE WHEN WSFILE001.PDEL.PDVK = 0 THEN NULL ELSE WSFILE001.PDEL.PDVK END,
                                  CASE WHEN WSFILE001.PDEL.PDVJ = 0 THEN NULL ELSE WSFILE001.PDEL.PDVJ END,
                                  CASE WHEN WSFILE001.PDEL.PDVI = 0 THEN NULL ELSE WSFILE001.PDEL.PDVI END,
                                  CASE WHEN WSFILE001.PDEL.PDVH = 0 THEN NULL ELSE WSFILE001.PDEL.PDVH END,
                                  CASE WHEN WSFILE001.PDEL.PDVG = 0 THEN NULL ELSE WSFILE001.PDEL.PDVG END,
                                  CASE WHEN WSFILE001.PDEL.PDVF = 0 THEN NULL ELSE WSFILE001.PDEL.PDVF END,
                                  CASE WHEN WSFILE001.PDEL.PDVE = 0 THEN NULL ELSE WSFILE001.PDEL.PDVE END,
                                  CASE WHEN WSFILE001.PDEL.PDVD = 0 THEN NULL ELSE WSFILE001.PDEL.PDVD END,
                                  CASE WHEN WSFILE001.PDEL.PDVC = 0 THEN NULL ELSE WSFILE001.PDEL.PDVC END,
                                  CASE WHEN WSFILE001.PDEL.PDVB = 0 THEN NULL ELSE WSFILE001.PDEL.PDVB END,
                                  CASE WHEN WSFILE001.PDEL.PDVA = 0 THEN NULL ELSE WSFILE001.PDEL.PDVA END)    
       
            AND 
       
            WSFILE001.DEL1.DVER##  > 0 AND WSFILE001.DEL1.DM1 =
            COALESCE  (CASE WHEN WSFILE001.DEL1.DVER## = 12 THEN WSFILE001.DEL1.DM12 ELSE NULL END,
                                  CASE WHEN WSFILE001.DEL1.DVER## = 11 THEN WSFILE001.DEL1.DM11 ELSE NULL END,
                                  CASE WHEN WSFILE001.DEL1.DVER## = 10 THEN WSFILE001.DEL1.DM10 ELSE NULL END,
                                  CASE WHEN WSFILE001.DEL1.DVER## = 9 THEN WSFILE001.DEL1.DM9 ELSE NULL END,
                                  CASE WHEN WSFILE001.DEL1.DVER## = 8 THEN WSFILE001.DEL1.DM8 ELSE NULL END,
                                  CASE WHEN WSFILE001.DEL1.DVER## = 7 THEN WSFILE001.DEL1.DM7 ELSE NULL END,
                                  CASE WHEN WSFILE001.DEL1.DVER## = 6 THEN WSFILE001.DEL1.DM6 ELSE NULL END,
                                  CASE WHEN WSFILE001.DEL1.DVER## = 5 THEN WSFILE001.DEL1.DM5 ELSE NULL END,
                                  CASE WHEN WSFILE001.DEL1.DVER## = 4 THEN WSFILE001.DEL1.DM4 ELSE NULL END,
                                  CASE WHEN WSFILE001.DEL1.DVER## = 3 THEN WSFILE001.DEL1.DM3 ELSE NULL END,
                                  CASE WHEN WSFILE001.DEL1.DVER## = 2 THEN WSFILE001.DEL1.DM2 ELSE NULL END,
                                  CASE WHEN WSFILE001.DEL1.DVER## = 1 THEN WSFILE001.DEL1.DM1 ELSE NULL END)
       
            AND       
       
            WSFILE001.NDEP1.NPRC01 = 
            COALESCE    (CASE  WHEN  WSFILE001.NDEP1.NPRC01 > MIN(WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC06, WSFILE001.NDEP1.NPRC07,
                                    WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC09, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
                 CASE WHEN  WSFILE001.NDEP1.NPRC02 > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC06, WSFILE001.NDEP1.NPRC07,
                                    WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC09, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
                 CASE WHEN  WSFILE001.NDEP1.NPRC03 > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC06, WSFILE001.NDEP1.NPRC07,
                                    WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC09, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
                 CASE WHEN  WSFILE001.NDEP1.NPRC04 > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC06, WSFILE001.NDEP1.NPRC07,
                                    WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC09, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
                 CASE WHEN  WSFILE001.NDEP1.NPRC05 > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC06, WSFILE001.NDEP1.NPRC07,
                                    WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC09, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
                 CASE WHEN  WSFILE001.NDEP1.NPRC06 > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC07,
                                    WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC09, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
                 CASE WHEN  WSFILE001.NDEP1.NPRC07 > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC06,
                                    WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC09, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
                 CASE WHEN  WSFILE001.NDEP1.NPRC08  > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC06, 
                                    WSFILE001.NDEP1.NPRC07, WSFILE001.NDEP1.NPRC09, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
                 CASE WHEN  WSFILE001.NDEP1.NPRC09  > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC06, 
                                    WSFILE001.NDEP1.NPRC07, WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC10) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END,
                 CASE WHEN  WSFILE001.NDEP1.NPRC10  > MIN(WSFILE001.NDEP1.NPRC01, WSFILE001.NDEP1.NPRC02, WSFILE001.NDEP1.NPRC03, WSFILE001.NDEP1.NPRC04, WSFILE001.NDEP1.NPRC05, WSFILE001.NDEP1.NPRC06,
                                    WSFILE001.NDEP1.NPRC07, WSFILE001.NDEP1.NPRC08, WSFILE001.NDEP1.NPRC09) THEN WSFILE001.NDEP1.NPRC01 ELSE NULL END)
       
        ORDER BY DEAL##

    </cfquery>

    ilssac
    ilssac
    Inspiring
    May 6, 2009

    That is quite a query... I suspect it could be optimized but nothing jumps out for me mostly because I don't have the time to analyze a query that long.

    If it is a long running query, it is a long running query and sometimes one just has to live with that.  I have occasionally written code that was expected to take hours to run.

    If you can test your SQL directly against your database, sometimes it preforms better that way then through the database driver from the web server, but that is somewhat unusal.  If so, then putting the SQL into the database and accessing it as a stored procedure or something can improve preformance.

    That is about all you can do outside of improving the SQL and or database design to more effeciently retrieve the required records, but to do that would take more knowledge of the overal system then I have from looking at one, loonnngggg sql statement.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices