爲什麽我調用過程插入數據速度很慢，請大神指導
//創建過程
CREATE OR REPLACE procedure SMTSFIS1.locksn (sr varchar2,lockno varchar2 ,lock_group varchar2,EMPNO VARCHAR2,LOCK_APPLY_EMP VARCHAR2,lockrn varchar2,flag number,res out number ) as
snn varchar2(32767);
sn varchar2(50);
c number default 0;
tempcount number default 0;
sp_count number default 0;
begin
snn:=sr;
while (instr(snn,',')>0) loop
sn:=substr(snn,1,instr(snn,',')-1);
SELECT COUNT(*) INTO tempcount FROM SFISM4.R_PART_MAP_T WHERE SELF_SN=SN OR VENDER_SN=SN AND USED_FLAG=1;
if tempcount=0 then
case flag
when 0 then
insert into SMTsfism4.r_lock_tracking_t (LOCK_NO,LOCK_TYPE,SERIAL_NUMBER,MODEL_NAME,GROUP_NAME,LOCK_EMP,LOCK_TIME,UNLOCK_EMP,UNLOCK_TIME,LOCK_APPLY_EMP,UNLOCK_APPLY_EMP)
SELECT LOCKNO,'ROUTE',SERIAL_NUMBER,MODEL_NAME,lock_group,EMPNO,SYSDATE,'N/A','',LOCK_APPLY_EMP,'N/A' FROM SMTSFISM4.R_WIP_TRACKING_T WHERE SERIAL_NUMBER=sn OR SHIPPING_SN=SN;
when 1 then
insert into SMTsfism4.r_lock_tracking_t (LOCK_NO,LOCK_TYPE,SERIAL_NUMBER,MODEL_NAME,GROUP_NAME,LOCK_EMP,LOCK_TIME,UNLOCK_EMP,UNLOCK_TIME,LOCK_APPLY_EMP,UNLOCK_APPLY_EMP)
SELECT LOCKNO,'ROUTE',SERIAL_NUMBER,MODEL_NAME,lock_group,EMPNO,SYSDATE,'N/A','',LOCK_APPLY_EMP,'N/A' FROM SMTSFISM4.R_WIP_TRACKING_T WHERE SERIAL_NUMBER=sn OR SHIPPING_SN=SN;
update SMTsfism4.r_wip_tracking_t set error_flag=error_flag+3 where serial_number=sn OR SHIPPING_SN=SN;
end case;
c:=c+1;
end if;
snn:=substr(snn,instr(snn,',')+1,LENGTH(snn));
end loop;
insert into SMTsfism4.r_lock_desc_t (LOCK_NO, LOCK_STATUS, LOCK_REASON, LOCK_EMP, LOCK_TIME, LOCK_APPLY_EMP, LOCK_TYPE)
VALUES(lockno,'1',lockrn,EMPNO,SYSDATE,LOCK_APPLY_EMP,'ROUTE');
res:=c;
END;
/
//C#屏蔽程式
private void lockboutton_Click(object sender, EventArgs e)
{
resultbox.Clear();
boxsnstr = snbox.Lines;
if ((snbox.Text.Trim() == "") && (wdealwitn.ToString() == ""))
{
MessageBox.Show("尚無SN可操作", "系統提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if ((SQRbox.Text.Trim() == "") | (LKRN.Text.Trim() == ""))
{ MessageBox.Show("屏蔽申請人&屏蔽原因不能為空"); }
else
{
String num = String.Format("Select count(*)FROM SFISM4.R_LOCK_desc_T WHERE LOCK_NO LIKE '{0}%'", "P" + DateTime.Today.ToString("yyMMdd"));
OleDbCommand getnum = new OleDbCommand(num, DBHelper.conn);
int count3 = Convert.ToInt32(getnum.ExecuteScalar()) + 1;
string lockno = "P" + DateTime.Now.ToString("yyMMdd") + "00" + count3;
try
{
OleDbCommand cmm = new OleDbCommand("locksn", DBHelper.conn);
cmm.CommandType = CommandType.StoredProcedure;
//判斷待處理對象來源：文檔還是文本框------------------------------------
if (snbox.Text.Trim() == "")
{ cmm.Parameters.Add("sr", OleDbType.VarChar).Value = wdealwitn.ToString().ToUpper().Trim(); }
else
{
wdealwitn.Clear();
string[] boxsn = snbox.Lines;
for (int s = 0; s < snbox.Lines.Length; s++)
{
{ wdealwitn.Append( boxsnstr[s].Trim().ToUpper() + ","); }
}
cmm.Parameters.Add("sr", OleDbType.VarChar).Value = wdealwitn.ToString().ToUpper().Trim();
}
resultbox.AppendText("sss" + wdealwitn.ToString() + "\n");
//-----------------------------------------------------------------------
cmm.Parameters.Add("lockno", OleDbType.VarChar).Value = lockno;
cmm.Parameters.Add("lock_group", OleDbType.VarChar).Value = GM.Text.Trim().ToString();
cmm.Parameters.Add("EMPNO", OleDbType.VarChar).Value = load.user.usern;
cmm.Parameters.Add("LOCK_APPLY_EMP", OleDbType.VarChar).Value = SQRbox.Text.ToString();
cmm.Parameters.Add("lockrn", OleDbType.VarChar).Value = LKRN.Text.ToString();
if (radioButton1.Checked)
{ cmm.Parameters.Add("flag", OleDbType.UnsignedInt).Value = 1; }
else
{ cmm.Parameters.Add("flag", OleDbType.UnsignedInt).Value = 0; }
//cmm.Parameters.Add("res1", OleDbType.VarChar,200);
//cmm.Parameters["res1"].Direction = ParameterDirection.Output;
cmm.Parameters.Add("res", OleDbType.UnsignedInt);
cmm.Parameters["res"].Direction = ParameterDirection.Output;
cmm.ExecuteNonQuery();
//if (cmm.Parameters["res1"].Value.ToString() != "")
// resultbox.AppendText(cmm.Parameters["res1"].Value.ToString());
resultbox.AppendText("屏蔽完成，屏蔽數量：" + cmm.Parameters["res"].Value + "pcs,屏蔽單號" + lockno);
}
catch (Exception xlock)
{ MessageBox.Show("無法屏蔽：" + xlock.Message); }
finally
{ RESULT.Clear();
boxsnstr = new string[0];
wdealwitn.Clear();
}
}
}
運行結果很不理想，和單PCS鏈接數據庫迭代操作時間差不多，請大神有時間幫忙指導指導！非常感謝！ --------------------编程问答-------------------- 你在过程里还不是一个一个的循环处理每个SNN，完全没优势的。
优化SQL吧，目标是一次处理多条SNN。 --------------------编程问答-------------------- 再说这条：
SELECT COUNT(*) INTO tempcount
结果你只想判断有无：
if tempcount=0 then
用COUNT(*)实在是太太太浪费了
补充：.NET技术 , C#
