查询语句反应慢的有关问题

2013-07-23

查询语句反应慢的问题以下这段代码是查询最近五个月有交易的和新建两个月之类的供应商，但是查询速度很慢需

查询语句反应慢的问题
以下这段代码是查询最近五个月有交易的和新建两个月之类的供应商，但是查询速度很慢需要30s左右，请问各位大虾有没有什么好的建议？？？
CompanyCode in之前和之后的语句分开查询速度都很快！！
SELECT  distinct CompanyCode AS Code, '' AS Parent, CompanyName AS NAME, CompanyName AS Description, 0 AS Layer
FROM eCompany
WHERE (1 = 1)  AND (DeleteMark = '') and CompanyCode in(select distinct parent
from (SELECT VendorCode AS Code, CompanyCode AS Parent, VendorCode AS NAME,  '客户编码：' + VendorCode + '
类别：' + (CASE BCFlag WHEN 'A' THEN N'国内区外' WHEN 'T' THEN N'区内' ELSE N'其他' END)  + 'Incoterm:' + Incoterm + '
PaymentTerm:' + PaymentTerm + N' 组织机构代码：' + OrganizationCode AS Description, 1 AS Layer
FROM eVendor  except
SELECT VendorCode AS Code, CompanyCode AS Parent, VendorCode AS NAME,  '客户编码：' + VendorCode + '
类别：' + (CASE BCFlag WHEN 'A' THEN N'国内区外' WHEN 'T' THEN N'区内' ELSE N'其他' END)  + 'Incoterm:' + Incoterm + '
PaymentTerm:' + PaymentTerm + N' 组织机构代码：' + OrganizationCode AS Description, 1 AS Layer
FROM eVendor
WHERE (ISNULL(CompanyCode, '') <> '')  AND (DeleteMark = '') and vendorcode not in
(SELECT distinct vendorcode FROM eOneDayHead WHERE (DATEDIFF(dd, UpdateTime, GETDATE()) < 150)
union
select distinct vendorcode from tqsmcpackinghead  where (datediff(dd,createdate,getdate())<150)
    union  select distinct  vendorcode from evendor where datediff(dd,updatetime,getdate())<60)) as D) 查询速度
[解决办法]

SELECT DISTINCT
CompanyCode AS Code ,

        '' AS Parent ,
        CompanyName AS NAME ,
        CompanyName AS Description ,
        0 AS Layer
FROM    eCompany
WHERE   ( 1 = 1 )
        AND ( DeleteMark = '' )
        AND CompanyCode IN (
        SELECT DISTINCT
                parent
        FROM    ( SELECT    VendorCode AS Code ,
                            CompanyCode AS Parent ,
                            VendorCode AS NAME ,
                            '客户编码：' + VendorCode + '
类别：' + ( CASE BCFlag
           WHEN 'A' THEN N'国内区外'
           WHEN 'T' THEN N'区内'
           ELSE N'其他'
         END ) + 'Incoterm:' + Incoterm + '
PaymentTerm:' + PaymentTerm + N' 组织机构代码：' + OrganizationCode AS Description ,
                            1 AS Layer
                  FROM      eVendor
                  EXCEPT
                  SELECT    VendorCode AS Code ,
                            CompanyCode AS Parent ,

                            VendorCode AS NAME ,
                            '客户编码：' + VendorCode + '
类别：' + ( CASE BCFlag
           WHEN 'A' THEN N'国内区外'
           WHEN 'T' THEN N'区内'
           ELSE N'其他'
         END ) + 'Incoterm:' + Incoterm + '
PaymentTerm:' + PaymentTerm + N' 组织机构代码：' + OrganizationCode AS Description ,
                            1 AS Layer
                  FROM      eVendor
                  WHERE     ( ISNULL(CompanyCode, '') <> '' )
                            AND ( DeleteMark = '' )
                            AND vendorcode NOT IN (
                            SELECT DISTINCT
                                    vendorcode
                            FROM    eOneDayHead
                            WHERE   ( DATEDIFF(dd, UpdateTime, GETDATE()) < 150 )
                            UNION

                            SELECT DISTINCT
                                    vendorcode
                            FROM    tqsmcpackinghead
                            WHERE   ( DATEDIFF(dd, createdate, GETDATE()) < 150 )
                            UNION
                            SELECT DISTINCT
                                    vendorcode
                            FROM    evendor
                            WHERE   DATEDIFF(dd, updatetime, GETDATE()) < 60 )
                ) AS D )

in 和 not in 对性能影响很大，建议用join的形式替换in的形式。

热点排行

SQL Server

查询语句反应慢的有关问题