增加硬件配置这个只是能解决一时的问题,我的ERP系统运行几年了,中间换过几台服务器的,就是新换一段时间就好些,但数据量增大之后,还是存在超时的问题.我贴上一存储过程,看看还有什么不合理的地方 USE [TKLY_GYdata] GO /****** 对象: StoredProcedure [dbo].[sp_clkc_clckCK] 脚本日期: 05/31/2009 17:45:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GOALTER PROCEDURE [dbo].[sp_clkc_clckCK] @dzid varchar(20) AS Declare @liid int, @ckid int, @goodsdm varchar(30), @batNo varchar(30), @newdzid varchar(20), @clll_liid int, @cgth_liid int, @qty decimal(18,4), @Amt decimal(18,4), @price decimal(18,4), @kcqty decimal(18,4), @kcamt decimal(18,4), @Amt0 decimal(18,4), @ErrorNumber int, @shpz bit, @khid int, @cwid int SET NOCOUNT ON --不返回影响行数信息,以减少网络流量 SET XACT_ABORT ON --打开自动回滚 SELECT @ckid=ckid ,@shpz=shpz FROM clck_dzbh WHERE dzid=@dzid BEGIN TRANSACTION --1.update DECLARE cRec CURSOR SCROLL FOR SELECT liid,goodsdm,Qty,Amt,batNo,clll_liid,cgth_liid,khid,cwid FROM clck_dzit WHERE dzid=@dzid OPEN cRec FETCH cRec INTO @liid,@goodsdm,@qty,@Amt,@batNo,@clll_liid,@cgth_liid,@khid,@cwid
WHILE @@FETCH_STATUS=0 BEGIN --记录不存在则新增,否则执行更新 --.clkc_cczs IF not Exists( select goodsdm From clkc_cczs where goodsdm=@goodsdm and ckid=@ckid and batNo=@batNo and khid=@khid and cwid=@cwid) BEGIN INSERT INTO clkc_cczs with (TABLOCK) (goodsdm,Qty,Amt,ckid,batNo,khid,cwid) VALUES(@goodsdm,-@Qty,-@Amt,@ckid,@batNo,@khid,@cwid) END ELSE BEGIN --更新库存记录 UPDATE clkc_cczs with (TABLOCK) SET Amt =Amt-@Amt,Qty =Qty-@Qty From clkc_cczs WHERE goodsdm=@goodsdm and ckid=@ckid and batNo=@batNo and khid=@khid and cwid=@cwid END FETCH cRec INTO @liid,@goodsdm,@qty,@Amt,@batNo,@clll_liid,@cgth_liid,@khid,@cwid END --End While CLOSE cRec DEALLOCATE cRecCOMMIT TRANSACTION SET XACT_ABORT OFF --打开自动回滚
增加硬件配置这个只是能解决一时的问题,我的ERP系统运行几年了,中间换过几台服务器的,就是新换一段时间就好些,但数据量增大之后,还是存在超时的问题.我贴上一存储过程,看看还有什么不合理的地方
USE [TKLY_GYdata]
GO
/****** 对象: StoredProcedure [dbo].[sp_clkc_clckCK] 脚本日期: 05/31/2009 17:45:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GOALTER PROCEDURE [dbo].[sp_clkc_clckCK]
@dzid varchar(20)
AS
Declare
@liid int,
@ckid int,
@goodsdm varchar(30),
@batNo varchar(30),
@newdzid varchar(20),
@clll_liid int,
@cgth_liid int,
@qty decimal(18,4),
@Amt decimal(18,4),
@price decimal(18,4),
@kcqty decimal(18,4),
@kcamt decimal(18,4),
@Amt0 decimal(18,4),
@ErrorNumber int,
@shpz bit,
@khid int,
@cwid int SET NOCOUNT ON --不返回影响行数信息,以减少网络流量
SET XACT_ABORT ON --打开自动回滚 SELECT @ckid=ckid ,@shpz=shpz
FROM clck_dzbh WHERE dzid=@dzid
BEGIN TRANSACTION --1.update
DECLARE cRec CURSOR SCROLL FOR
SELECT liid,goodsdm,Qty,Amt,batNo,clll_liid,cgth_liid,khid,cwid
FROM clck_dzit
WHERE dzid=@dzid OPEN cRec
FETCH cRec
INTO @liid,@goodsdm,@qty,@Amt,@batNo,@clll_liid,@cgth_liid,@khid,@cwid
WHILE @@FETCH_STATUS=0
BEGIN
--记录不存在则新增,否则执行更新
--.clkc_cczs
IF not Exists(
select goodsdm
From clkc_cczs
where goodsdm=@goodsdm and ckid=@ckid and batNo=@batNo and
khid=@khid and cwid=@cwid) BEGIN
INSERT INTO clkc_cczs with (TABLOCK) (goodsdm,Qty,Amt,ckid,batNo,khid,cwid)
VALUES(@goodsdm,-@Qty,-@Amt,@ckid,@batNo,@khid,@cwid) END ELSE
BEGIN --更新库存记录
UPDATE clkc_cczs with (TABLOCK)
SET Amt =Amt-@Amt,Qty =Qty-@Qty
From clkc_cczs
WHERE goodsdm=@goodsdm and ckid=@ckid and batNo=@batNo and
khid=@khid and cwid=@cwid
END FETCH cRec
INTO @liid,@goodsdm,@qty,@Amt,@batNo,@clll_liid,@cgth_liid,@khid,@cwid
END --End While
CLOSE cRec
DEALLOCATE cRecCOMMIT TRANSACTION
SET XACT_ABORT OFF --打开自动回滚