我是DELPHI新手,现在要做个报表:
数据库中已经有 八十多万条 记录
有一个更新语句UPDATE Money_UserInfo
SET MUI_Prim = isnull(null,0)SQL查询分析器中 做完 126秒在DELPHI 中 执行不下去了?
我使用的是adoquery
说 超时已过期。。
另外 执行时
我在程序里
Application.ProcessMessages;
程序像死掉一样
等一会就弹出 超时已过期 了
数据库中已经有 八十多万条 记录
有一个更新语句UPDATE Money_UserInfo
SET MUI_Prim = isnull(null,0)SQL查询分析器中 做完 126秒在DELPHI 中 执行不下去了?
我使用的是adoquery
说 超时已过期。。
另外 执行时
我在程序里
Application.ProcessMessages;
程序像死掉一样
等一会就弹出 超时已过期 了
SET MUI_Prim =0 where MUI_Prim is null
试试这样
SET MUI_PrimaryTerminalStopped = isnull((select sum(MCS_PrimaryTerminalChanged)
from MONEY_ChangeStop
where MCS_UserNumber=MONEY_UserInfo.MUI_UserNumber
and MCS_EndDate is null
and MCS_State='报停'
group by MCS_State),0), MUI_AppendTerminalStopped = isnull((select sum(MCS_AppendTerminalChanged)
from MONEY_ChangeStop
where MCS_UserNumber=MONEY_UserInfo.MUI_UserNumber
and MCS_EndDate is null
and MCS_State='报停'
group by MCS_State),0),MUI_PrimaryTerminalForceStopped = isnull((select sum(MCS_PrimaryTerminalChanged)
from MONEY_ChangeStop
where MCS_UserNumber=MONEY_UserInfo.MUI_UserNumber
and MCS_EndDate is null
and MCS_State='罚停'
group by MCS_State),0),MUI_AppendTerminalForceStopped = isnull((select sum(MCS_AppendTerminalChanged)
from MONEY_ChangeStop
where MCS_UserNumber=MONEY_UserInfo.MUI_UserNumber
and MCS_EndDate is null
and MCS_State='罚停'
group by MCS_State),0)我简单的写成
UPDATE Money_UserInfo
SET MUI_Prim = isnull(null,0)
这样 是为了方便大家给我解答
虽然我加了
Application.ProcessMessages;
但程序还是像死掉一样
怎么办呢 ?
一个字段一个语句更新,LZ试试
UPDATE Money_UserInfo set MUI_PrimaryTerminalStopped= ...UPDATE Money_UserInfo set MUI_AppendTerminalStopped= ...UPDATE Money_UserInfo set MUI_PrimaryTerminalForceStopped=... ...
update Money_UserInfo set
MUI_PrimaryTerminalStopped=Primary1,
MUI_AppendTerminalStopped=Append1,
MCS_UserNumber=Primary2,
MCS_UserNumber=Append2
from Money_UserInfo a
inner join
(
select MCS_UserNumber,
sum(case when MCS_State='报停' then isnull(MCS_PrimaryTerminalChanged,0)) as Primary1,
sum(case when MCS_State='报停' then isnull(MCS_AppendTerminalChanged,0)) as Append1,
sum(case when MCS_State='罚停' then isnull(MCS_PrimaryTerminalChanged,0)) as Primary2,
sum(case when MCS_State='罚停' then isnull(MCS_AppendTerminalChanged,0)) as Append2
from MONEY_ChangeStop
where MCS_EndDate is null
Group by MCS_UserNumber
) b
on a.MCS_UserNumber=b.MCS_UserNumber
试试这样 我没测试 呵呵
MUI_PrimaryTerminalStopped=Primary1,
MUI_AppendTerminalStopped=Append1,
MCS_UserNumber=Primary2,
MCS_UserNumber=Append2
from Money_UserInfo a
inner join
(
select MCS_UserNumber,
sum(case when MCS_State='报停' then isnull(MCS_PrimaryTerminalChanged,0) else 0 end) as Primary1,
sum(case when MCS_State='报停' then isnull(MCS_AppendTerminalChanged,0) else 0 end) as Append1,
sum(case when MCS_State='罚停' then isnull(MCS_PrimaryTerminalChanged,0) else 0 end) as Primary2,
sum(case when MCS_State='罚停' then isnull(MCS_AppendTerminalChanged,0) else 0 end) as Append2
from MONEY_ChangeStop
where MCS_EndDate is null
Group by MCS_UserNumber
) b
on a.MCS_UserNumber=b.MCS_UserNumber
用的是SQL 2000吧?
升级下,用SQL2005就没有这个问题.
主要是优化代码,提高SQL性能
但是你遍历
Money_UserInfo的条数*MONEY_ChangeStop的条数*4次全表 不超时 天都看不过去