SQL改善20130201

 SELECT distinct b.PONO, 
 GenerationType=CASE a.GenerationType 
  WHEN 'ByEDI' THEN 'LH801' 
  ELSE 'NA802' 
  END   
 FROM IVInvoiceMainT(NOLOCK) a INNER JOIN  IVInvoiceDetailT(NOLOCK) b ON b.InvoiceNo = a.InvoiceNo  
 LEFT JOIN IVEdiPOLog(NOLOCK) c ON b.PONO=c.PONO AND a.GenerationType=c.GenerationType 
 WHERE a.GenerationType=@GenerationType  
 AND a.InvoiceDate>'2011-11-27'  
 AND a.InvoiceNo NOT LIKE '225%' 
 AND a.VendorCode <>'VVB015'  
 AND (a.IsUpdateBu IS NULL or a.IsUpdateBu = 0) 
 AND len(b.PONO)=10  
 OR ( c.GenerationType=@GenerationType AND c.Status='F' AND datediff(Hour,c.LogDate,getdate())>=24) 
 GROUP BY b.PONO,a.GenerationType ORDER BY b.PONO
 
 
 SELECT distinct PONO, GenerationType From  
        (SELECT distinct b.PONO,  
        GenerationType=CASE a.GenerationType  
                WHEN 'ByEDI' THEN 'LH801'  
                ELSE 'NA802'  
                END   
        FROM IVInvoiceMainT(NOLOCK) a  INNER JOIN  IVInvoiceDetailT(NOLOCK) b  ON b.InvoiceNo = a.InvoiceNo  
        --LEFT JOIN IVEdiPOLog(NOLOCK) c ON b.PONO=c.PONO AND a.GenerationType=c.GenerationType  
        --AND b.PONO=c.PONO  
        WHERE  
        a.GenerationType=@GenerationType  
        AND a.InvoiceDate>'2011-11-27'  
        AND a.InvoiceNo NOT LIKE '225%'  
        AND a.VendorCode <> 'VVB015'  
        AND (a.IsUpdateBu IS NULL or a.IsUpdateBu = 0)  
        AND len(b.PONO)=10 
        GROUP BY b.PONO,a.GenerationType  
        union all  
        SELECT distinct b.PONO,  
        GenerationType=CASE a.GenerationType  
                WHEN 'ByEDI' THEN 'LH801'  
                ELSE 'NA802'  
                END   
        FROM IVInvoiceMainT(NOLOCK) a  INNER JOIN   
        IVInvoiceDetailT(NOLOCK)  b  ON b.InvoiceNo = a.InvoiceNo 
        LEFT JOIN IVEdiPOLog(NOLOCK) c ON b.PONO=c.PONO AND a.GenerationType=c.GenerationType  
        AND b.PONO=c.PONO  
        WHERE  
        c.GenerationType=@GenerationType
        AND c.Status='F'  
        AND datediff(Hour,c.LogDate,getdate())>=24  
        GROUP BY b.PONO,a.GenerationType ) AS B  
        ORDER BY PONO 

 

效能從單次響應99秒優化到0.6秒

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24867586/viewspace-753756/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24867586/viewspace-753756/