SQL 2000转到2005时出错

dynasty0628

数据库由2000升级到2005，兼容级别设置为2000。别的问题还没发现，但是有一个流程，流转时会报下面的错误：

在操作文件：Org:的时候出现异常，操作代码：34，异常信息：error occur during invoke getRecords method, 查询处理器用尽了内部资源，无法生成查询计划。这种情况很少出现，只有在查询极其复杂或引用了大量表或分区时才会出现。请简化查询。如果您认为该消息的出现纯属错误，请与客户支持服务部门联系，了解详细信息。


环节限定者表达式：
OrgChildren(OrgChildren(OrgParent(GetProcUnitExecutorDept('', True), '', False, False), OrgCondition('*.ptm', ISPLAYNAME = '部门经理', ''), False, False),OrgCondition('*.psm','',''), False, False) or OrgChildren(OrgChildren(OrgParent(GetProcUnitExecutorDept('', True), '', False, False), OrgCondition('*.ptm', ISPLAYNAME = '部门副经理', ''), False, False),OrgCondition('*.psm','',''), False, False)

去掉这个表达式就可以正常流转了。切换到2000下，流转完全没问题

zfang

抓 一下这个 的执行sql。
方法1：sqlserver的监控
方法2：平台的sysmoniter

dynasty0628

抓到的SQL如下，在2005中查询，会报一楼错误，在2000中执行，1秒出结果：

select FGUID from TORGSYSTEM where FGUID IN (SELECT b.FGUID FROM TORGSYSTEM a, TORGSYSTEM b WHERE a.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE FGUID IN (SELECT
b.FGUID FROM TORGSYSTEM a, TORGSYSTEM b WHERE a.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE FGUID IN (SELECT b.FGUID FROM TORGSYSTEM a, TORGSYSTEM b WHERE
a.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE FGUID IN (SELECT b.FGUID FROM TORGSYSTEM a, TORGSYSTEM b WHERE a.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE
FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE (FDEPT = 'COMPUTERADMIN') and (FPOSITION = '' or FPOSITION is null) and (FPERSON = '' or FPERSON is null)))  AND ((( (b.FPATH
LIKE a.FPATH + a.FID + a.FKIND + '\%') OR ((a.FKIND = '.PTN') AND ((b.FID = a.FID AND b.FKIND = '.PTM') OR (b.FPATH + b.FID + b.FKIND  LIKE '%\' + a.FID + '.PTM' + '\%'))) ) OR ( a.FPATH
+ a.FID + a.FKIND = b.FPATH + b.FID + b.FKIND )))))  AND  b.FGUID = a.FPARENTGUID ))  AND b.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE ( (FFILENAME LIKE '%.PTM') ) and
(FGUID in (select FGUID from TORGATTRIBUTE where UPPER(FID) = 'DISPLAYNAME' and UPPER(FVALUE) = '部门经理')))  AND  b.FPARENTGUID = a.FGUID ))  AND b.FGUID IN (SELECT FGUID
FROM TORGSYSTEM WHERE ( (FFILENAME LIKE '%.PSM') ))  AND  b.FPARENTGUID = a.FGUID ) AND (FGUID IN (SELECT b.FGUID FROM TORGSYSTEM a, TORGSYSTEM b WHERE a.FGUID IN
(SELECT FGUID FROM TORGSYSTEM WHERE FGUID IN (SELECT b.FGUID FROM TORGSYSTEM a, TORGSYSTEM b WHERE a.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE FGUID IN
(SELECT b.FGUID FROM TORGSYSTEM a, TORGSYSTEM b WHERE a.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE FGUID IN (SELECT b.FGUID FROM TORGSYSTEM a, TORGSYSTEM b
WHERE a.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE (FDEPT = 'COMPUTERADMIN') and (FPOSITION = '' or FPOSITION is null)
and (FPERSON = '' or FPERSON is null)))  AND ((( (b.FPATH LIKE a.FPATH + a.FID + a.FKIND + '\%') OR ((a.FKIND = '.PTN') AND ((b.FID = a.FID AND b.FKIND = '.PTM') OR (b.FPATH + b.FID +
b.FKIND  LIKE '%\' + a.FID + '.PTM' + '\%'))) ) OR ( a.FPATH + a.FID + a.FKIND = b.FPATH + b.FID + b.FKIND )))))  AND  b.FGUID = a.FPARENTGUID ))  AND b.FGUID IN (SELECT FGUID FROM
TORGSYSTEM WHERE ( (FFILENAME LIKE '%.PTM') ) and (FGUID in (select FGUID from TORGATTRIBUTE where UPPER(FID) = 'DISPLAYNAME' and UPPER(FVALUE) = '部门经理')))  AND  
b.FPARENTGUID = a.FGUID ))  AND b.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE ( (FFILENAME LIKE '%.PSM') ))  AND  b.FPARENTGUID = a.FGUID ) or FGUID IN (SELECT b.FGUID
FROM TORGSYSTEM a, TORGSYSTEM b WHERE a.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE FGUID IN (SELECT b.FGUID FROM TORGSYSTEM a, TORGSYSTEM b WHERE a.FGUID IN
(SELECT FGUID FROM TORGSYSTEM WHERE FGUID IN (SELECT b.FGUID FROM TORGSYSTEM a, TORGSYSTEM b WHERE a.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE FGUID IN
(SELECT b.FGUID FROM TORGSYSTEM a, TORGSYSTEM b WHERE a.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE (FDEPT =
'COMPUTERADMIN') and (FPOSITION = '' or FPOSITION is null) and (FPERSON = '' or FPERSON is null)))  AND ((( (b.FPATH LIKE a.FPATH + a.FID + a.FKIND + '\%') OR ((a.FKIND = '.PTN') AND
((b.FID = a.FID AND b.FKIND = '.PTM') OR (b.FPATH + b.FID + b.FKIND  LIKE '%\' + a.FID + '.PTM' + '\%'))) ) OR ( a.FPATH + a.FID + a.FKIND = b.FPATH + b.FID + b.FKIND )))))  AND  b.FGUID
= a.FPARENTGUID ))  AND b.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE ( (FFILENAME LIKE '%.PTM') ) and (FGUID in (select FGUID from TORGATTRIBUTE where UPPER(FID) =
'DISPLAYNAME' and UPPER(FVALUE) = '部门副经理')))  AND  b.FPARENTGUID = a.FGUID ))  AND b.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE ( (FFILENAME LIKE '%.PSM') ))  AND  
b.FPARENTGUID = a.FGUID )) AND FGUID IN (SELECT b.FGUID FROM TORGSYSTEM a, TORGSYSTEM b WHERE a.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE (FGUID in (select FGUID
from TORGINDEX where UPPER(FID) = 'ORGUNIT.FUNCREF' and UPPER(FVALUE) = '\OWFSPACE\SDEPTMNGFUNC.FUNC')))  AND b.FGUID IN (SELECT FGUID FROM TORGSYSTEM WHERE (
(FFILENAME LIKE '%.PSM') ))  AND ((( (b.FPATH LIKE a.FPATH + a.FID + a.FKIND + '\%') OR ((a.FKIND = '.PTN') AND ((b.FID = a.FID AND b.FKIND = '.PTM') OR (b.FPATH + b.FID + b.FKIND  
LIKE '%\' + a.FID + '.PTM' + '\%'))) ) OR ( a.FPATH + a.FID + a.FKIND = b.FPATH + b.FID + b.FKIND )))) and FDELETELEVEL = 0

dynasty0628

sql monitor

zfang

上面的sql在2005上确实不能直接执行，网上找资料看了下，好想要加上 option(force order)就可了，但是sql2000下又可以不用。

所以只能请楼主想办法简化一下 相关的表达式或者规则。

dynasty0628

随着数据量的增大，升级到2005可能是必须的工作。
我在msdn上查了，好像是跟in语句也有关系，所以烦请你们也查一下，能不能优化你们的实现逻辑。
http://msdn.microsoft.com/zh-cn/library/ms177682.aspx

dynasty0628

请问这个问题怎么解决啊？表达式已经想不出还能怎么简化了

dynasty0628

试了几个简化的，结果还是一样

zfang

不好意思，这个底层的逻辑不好改变。只能想办法 去简化这个表达式。或者 自己配合部分代码来实现。

dynasty0628

真晕！那么多流程怎么改啊
而且你们自己的表达式到头来自己都不支持了，我们哪还知道该怎么改？我们没有能力去规避你们平台的缺陷，也没有那么多的时间和精力反过来改这么多的流程。我们所有的流程都有部门经理这个环节，表达式也都是这个，让你重新都改了你干么
难道你们不升级系统么？就这样一直保持支持SQL server 2000的水平？