none
这条sql可以怎么优化呢? RRS feed

  • 问题

  • 这部分sql是写在sp中的,通过profiler发现该部分执行较慢.

    sql如下:

    insert xxxx

       

    SELECT DISTINCT TOP 100 WITH TIES  
      A.Dropshipid,  
      A.Companycode,  
      A.Referencesonumber AS Sonumber,  
      A.Itemnumber,  
      A.[description],  
      A.Quantity,  
      A.Transactionnumber,  
      A.Warehousenumber,  
      F.SellerID as Vendornumber,--B.Vendornumber,  
      A.Downloaddate,  
      F.ShippingCompanyName,  
      F.ShippingPhone,  
      F.shipviacode AS ShipVia,  
      F.shippingAddress1 AS address1 ,  
      F.shippingAddress2 AS address2,  
      CASE RTRIM(ISNULL(F.shippingcountry,''))  
       WHEN 'CAN' THEN 'CA'  
       WHEN 'USA' THEN 'US'  
       WHEN 'USB' THEN 'US'  
       WHEN '' THEN 'US'  
       ELSE F.shippingcountry END AS country,  
      F.shippingCity AS city,  
      F.shippingState AS state,  
      F.shippingZipCode AS zipcode,  
      F.shippingcontactwith AS [name],  
      F.Email,  
      F.shippingcontactwith AS Contact,  
      --ADD BY MKTPLS  
      F.SOType,   
      F.LanguageID AS LanguageCode,   
      F.CountryCode,    
      F.SellerID,    
      F.SellerName,  
      A.Unitprice,  
      A.CurrencyCode,  
      A.CurrencyExchangeRate,  
      F.SODate ,  
      F.CurrencyShippingCharge,  
      F.CurrencySOAmount,  
      --ADD BY SBN[20101118]  
      F.RMANumber  
     FROM  Dropship.Dbo.Dropshiptransaction A WITH (NOLOCK)  
     INNER JOIN Dropship.Dbo.Dropshipmaster F WITH (NOLOCK)  
      ON a.dropshipid = f.dropshipid  
     INNER JOIN DBO.UV_EDI_LoadBalancing_ServerServiceVendor B WITH (NOLOCK)
        ON B.VendorNumber=F.SellerID And B.ServerName='Edafa' And B.ServiceName='VF'
    WHERE  F.SellerID IS NOT NULL  
      AND ((F.SOTYPE='99999' 
               AND A.Downloaddate IS NULL 
               AND (A.Status IS NULL OR A.Status <> 'V')  
               AND EXISTS (SELECT TOP 1 1  
                      FROM   Dropship.Dbo.V_Newtransaction D WITH (NOLOCK)  
                      WHERE  D.Dropshipid = A.Dropshipid  
                      AND D.Warehousenumber = A.Warehousenumber)
               AND NOT EXISTS (SELECT TOP 1 1  
                      FROM   NSLS.dbo.SOMaster G WITH (NOLOCK)  
                      WHERE  G.sonumber= A.Referencesonumber  
                      AND G.companycode=F.companycode  
                      AND G.status = 'V')
               AND NOT EXISTS (SELECT TOP 1 1  
                      FROM   dbo.ediordermaster E WITH (NOLOCK)  
                      WHERE  E.SellerID =  F.SellerID  
                      AND E.Ordernumber = CONVERT(VARCHAR(15),A.Referencesonumber)  
                      AND E.warehouseNumber = a.warehouseNumber) ) 
           OR (F.SOTYPE in ('99998','99997')
               AND NOT EXISTS (SELECT TOP 1 1  
                      FROM   mktpls.dbo.EDI_Seller_SBN_OrderMaster E WITH (NOLOCK)  
                      WHERE  E.SellerID =  F.SellerID  
                      AND E.Ordernumber = CONVERT(VARCHAR(15),A.Referencesonumber)))) 
      --AND D.ServiceName = @ServiceName  
      AND A.Itemnumber LIKE '9SI%'
    ORDER BY A.Dropshipid DESC  

     --GET item info  

    这个需要怎么优化呢?


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.


    • 已编辑 Wison-Ho 2012年5月7日 6:03
    2012年5月7日 5:54

全部回复