COM+有问题了，原因是因为在COM+中执行的SQL语句时间过长而出错！怎么处理? (100分)

错误提示：超时已过期。。。

将事务写入过程。

com+执行环境可以设定事务的执行时间，如果，超过执行时间，就会自动rollback,默认为
60秒，但本人不建议你修改此执行时间，你可以sql语名写到存储过程里，再调用。

fonder,谢谢，我写的就是存储过程，是这样调用的：
clientDataset.commenttext:= 'exec xxxx @xxx= '''xxx''''

还有，发现了一个问题，就是在sqlserver查询分析器里直接调用存储过程，
第一次调用，花了2分36秒，而第二次调用，只花了34秒，这又是怎么回事呢？
SQLSERVER的问题？有办法解决吗？

因为第一次调用时，已经将存储过程，调到内存里，第二次，当然快啦！
你应该检查一下存储过程！

哦。。。原来这样啊！存储过程我真不懂还有更好的写法了，我贴出来你帮我看看！能有效率
更高的写法吗？谢谢了！



CREATE PROCEDURE GetFullTimeTeacherDistributionByOlogy
@sYear varchar(4),
@sType varchar(14), ----城市，县镇，农村三类
@sDistrict varchar(16), -- 地区
@sSchoolMain varchar(24), -- 办学主体 A8
@sSchoolType varchar(34) --学校类型 A2
AS
begin
SELECT Main.A13, C1.C1, C2.C2, C3.C3, C4.C4, C5.C5, C6.C6, C7.C7, C8.C8, C9.C9,C10.C10 FROM
(SELECT DISTINCT A13 FROM TeacherInfo WHERE A00 = @sYear AND A27 = '专任教师') Main
left join (SELECT A.A13, COUNT(*) C1 FROM TeacherInfo A, SchoolInfo B
WHERE A.A00 = @sYear AND A.SchoolNo = B.SchoolNo AND A.A27 = '专任教师' AND B.A11 LIKE @sType
AND A.SchoolNo LIKE @sDistrict AND B.A8 LIKE @sSchoolMain AND B.A2 LIKE @sSchoolType
AND A.A00 = B.A00
GROUP BY A.A13) C1 on Main.A13 = C1.A13
left join (SELECT A.A13, COUNT(*) C2 FROM TeacherInfo A, SchoolInfo B
WHERE A.A00 = @sYear AND A.SchoolNo = B.SchoolNo AND A.A27 = '专任教师' AND B.A11 LIKE @sType AND A.A4 = '女'
AND A.SchoolNo LIKE @sDistrict AND B.A8 LIKE @sSchoolMain AND B.A2 LIKE @sSchoolType
AND A.A00 = B.A00
GROUP BY A.A13) C2 on Main.A13 = C2.A13
left join (SELECT A.A13, COUNT(*) C3 FROM TeacherInfo A, SchoolInfo B
WHERE A.A00 = @sYear AND A.SchoolNo = B.SchoolNo AND A.A27 = '专任教师' AND B.A11 LIKE @sType AND A.A6 <> '汉'
AND A.SchoolNo LIKE @sDistrict AND B.A8 LIKE @sSchoolMain AND B.A2 LIKE @sSchoolType
AND A.A00 = B.A00
GROUP BY A.A13) C3 on Main.A13 = C3.A13
left join (SELECT A.A13, COUNT(*) C4 FROM TeacherInfo A, SchoolInfo B
WHERE A.A00 = @sYear AND A.SchoolNo = B.SchoolNo AND A.A27 = '专任教师' AND B.A11 LIKE @sType AND A.A8 = '研究生毕业'
AND A.SchoolNo LIKE @sDistrict AND B.A8 LIKE @sSchoolMain AND B.A2 LIKE @sSchoolType
AND A.A00 = B.A00
GROUP BY A.A13) C4 on Main.A13 = C4.A13
left join (SELECT A.A13, COUNT(*) C5 FROM TeacherInfo A, SchoolInfo B
WHERE A.A00 = @sYear AND A.SchoolNo = B.SchoolNo AND A.A27 = '专任教师' AND B.A11 LIKE @sType AND A.A8 = '研究生班毕业'
AND A.SchoolNo LIKE @sDistrict AND B.A8 LIKE @sSchoolMain AND B.A2 LIKE @sSchoolType
AND A.A00 = B.A00
GROUP BY A.A13) C5 on Main.A13 = C5.A13
left join (SELECT A.A13, COUNT(*) C6 FROM TeacherInfo A, SchoolInfo B
WHERE A.A00 = @sYear AND A.SchoolNo = B.SchoolNo AND A.A27 = '专任教师' AND B.A11 LIKE @sType AND A.A8 = '本科毕业'
AND A.SchoolNo LIKE @sDistrict AND B.A8 LIKE @sSchoolMain AND B.A2 LIKE @sSchoolType
AND A.A00 = B.A00
GROUP BY A.A13) C6 on Main.A13 = C6.A13
left join (SELECT A.A13, COUNT(*) C7 FROM TeacherInfo A, SchoolInfo B
WHERE A.A00 = @sYear AND A.SchoolNo = B.SchoolNo AND A.A27 = '专任教师' AND B.A11 LIKE @sType AND A.A8 = '专科毕业'
AND A.SchoolNo LIKE @sDistrict AND B.A8 LIKE @sSchoolMain AND B.A2 LIKE @sSchoolType
AND A.A00 = B.A00
GROUP BY A.A13) C7 on Main.A13 = C7.A13
left join (SELECT A.A13, COUNT(*) C8 FROM TeacherInfo A, SchoolInfo B
WHERE A.A00 = @sYear AND A.SchoolNo = B.SchoolNo AND A.A27 = '专任教师' AND B.A11 LIKE @sType AND A.A8 = '中专高中毕业'
AND A.SchoolNo LIKE @sDistrict AND B.A8 LIKE @sSchoolMain AND B.A2 LIKE @sSchoolType
AND A.A00 = B.A00
GROUP BY A.A13) C8 on Main.A13 = C8.A13
left join (SELECT A.A13, COUNT(*) C9 FROM TeacherInfo A, SchoolInfo B
WHERE A.A00 = @sYear AND A.SchoolNo = B.SchoolNo AND A.A27 = '专任教师' AND B.A11 LIKE @sType AND A.A8 = '高中阶段以下'
AND A.SchoolNo LIKE @sDistrict AND B.A8 LIKE @sSchoolMain AND B.A2 LIKE @sSchoolType
AND A.A00 = B.A00
GROUP BY A.A13) C9 on Main.A13 = C9.A13
left join (SELECT A.A13, COUNT(*) C10 FROM TeacherInfo A, SchoolInfo B
WHERE A.A00 = @sYear AND A.SchoolNo = B.SchoolNo AND A.A27 = '专任教师' AND B.A11 LIKE @sType AND A.A8 IS NULL
AND A.SchoolNo LIKE @sDistrict AND B.A8 LIKE @sSchoolMain AND B.A2 LIKE @sSchoolType
AND A.A00 = B.A00
GROUP BY A.A13) C10 on Main.A13 = C10.A13
, (select 1 OrderID, '政治' OrderName
union all
select 2 OrderID, '语文' OrderName
union all
select 3 OrderID, '数学' OrderName
union all
select 4 OrderID, '小学自然' OrderName
union all
select 5 OrderID, '物理' OrderName
union all
select 6 OrderID, '化学' OrderName
union all
select 7 OrderID, '生物' OrderName
union all
select 8 OrderID, '地理自然' OrderName
union all
select 9 OrderID, '历史' OrderName
union all
select 10 OrderID, '外语' OrderName
union all
select 11 OrderID, '信息技术' OrderName
union all
select 12 OrderID, '体育' OrderName
union all
select 13 OrderID, '音乐' OrderName
union all
select 14 OrderID, '美术' OrderName
union all
select 15 OrderID, '劳动技术' OrderName
union all
select 16 OrderID, '农业资源环境类' OrderName
union all
select 17 OrderID, '能源类' OrderName
union all
select 18 OrderID, '土木水利工程类' OrderName
union all
select 19 OrderID, '加工制造类' OrderName
union all
select 20 OrderID, '交通运输类' OrderName
union all
select 21 OrderID, '信息技术类' OrderName
union all
select 22 OrderID, '医院卫生类' OrderName
union all
select 23 OrderID, '商贸旅游类' OrderName
union all
select 24 OrderID, '财经类' OrderName
union all
select 25 OrderID, '文艺体育类' OrderName
union all
select 26 OrderID, '公共事业类' OrderName
union all
select 27 OrderID, '实验教学' OrderName
union all
select 28 OrderID, '实习指导' OrderName
union all
select 29 OrderID, '幼儿教育' OrderName
union all
select 30 OrderID, '当年不任课' OrderName
union all
select 31 OrderID, '其他' OrderName
) as OrderTable
WHERE Main.A13 = OrderTable.OrderName
ORDER BY OrderTable.OrderID

END
GO

晕。。。贴出来的怎么这么乱？

不知道改成带索引的视图会怎么样.....

52free,改成视图应该也是一样的效果吧？视图也是要用SQL语句还查询的！

能不能分开一点写啊
太长了

你这程序这样写死了参数，以后维护起来会死人！
这种程序写的非常的糟糕！

如果你用的是ＡＤＯ的话，
可以设置CommandTimeOut属性为0，同时在组件服务中将事务超时时间设成3600，
就可以了。

在ADOquery力有一个Commandtimeout，预设为30，修改一下。估计是这个原因。（如果你用的是ado的话）

楼上的说的对,我也碰到这种情况,改一下时间就行了

谢谢各位，已经解决了！
TO：delphilai
象这样的情况，不知道有什么更好的办法呢？如何更好的写参数？谢谢！

我提个建议：就是问题解决了将解决的方法贴出来，这样大家以后在碰到这样的问题的时候就会解决了！

解决办法就是设置ADOConnection和ADODataSet的commendtimeout属性！

多人接受答案了。