“超时已过期”救命啊！！！

根据客户的需求定制了张报表，其中Left Join同一个表很多次：
SELECT .......
FROM ((((Policy p LEFT JOIN
      Carrier AS c ON p.CarrierID = c.CarrierID) LEFT JOIN
      Customer AS u ON p.CustomerID = u.CustID) LEFT JOIN
      ProductType AS r ON p.ProdTypeID = r.ProdTypeID) LEFT JOIN
      Sales AS s ON p.SalesID = s.SalesID) LEFT JOIN
      Branch B ON B.BranchId = P.Branchid LEFT JOIN
      SourceType ST ON ST.SourceTypeid = p.SourceTypeid LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 1) v1 ON p.PolicyID = v1.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 2) v2 ON p.PolicyID = v2.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 3) v3 ON p.PolicyID = v3.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 4) v4 ON p.PolicyID = v4.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 5) v5 ON p.PolicyID = v5.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 6) v6 ON p.PolicyID = v6.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 7) v7 ON p.PolicyID = v7.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 9) v8 ON p.PolicyID = v8.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 10) v9 ON p.PolicyID = v9.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 11) v10 ON p.PolicyID = v10.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 13) v11 ON p.PolicyID = v11.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 14) v12 ON p.PolicyID = v12.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 19) v13 ON p.PolicyID = v13.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 26) v14 ON p.PolicyID = v14.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 27) v15 ON p.PolicyID = v15.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 28) v16 ON p.PolicyID = v16.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 29) v17 ON p.PolicyID = v17.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 30) v18 ON p.PolicyID = v18.PolicyID LEFT JOIN
          (SELECT *
         FROM TargetData
         WHERE PropertyID = 31) v19 ON p.PolicyID = v19.PolicyID
WHERE .......

其中V1到V19都是同一个表的别名，但是总是容易出现“超时已过期”这样的错误提示，我已经想办法将ADODB.Connection对象的TimeOut设置比较大了还是容易出现这样的提示。
我也知道同时Left Join这么多的表实在是不明智，但是没有办法啊，客户需求，所以我想请教各位大侠、前辈们有没有什么比较好的方法来对这个语句进行优化。。。。

6 个解决方案

#1

把这条语句优化一下，重复访问表TargetData多次，在效率上肯定慢。

#2

WangZWang(阿来)：我也想进行优化，但是我实在是没有这方面的太多经验，请您指教！！

#3

不知道你的结构，和设计的思想，所以无从help you!

#4

WangZWang(阿来)：我从V1到V19的这个Left Join是这样的，就是假设Policy 表中的一条记录在TargetData表中对应19条记录，我希望将这19条记录中的某个字段提取出来然后和Policy中的其他记录一起排列成客户需求的报表。。。。

#5

可以试试,只left join 一次,在字段列表中取数据时,用case来完成,

没有整体的要求,不好帮到你

#6

可以用case 来完成.

#1