BS 架构，Oracle数据库 扫入条码速度很慢，如何追踪哪里慢？如何解决，谢谢大家！

Question

oracle

Answer 1

// 接上面代码
     /// <summary>
        /// 清除DataGrid的資料
        /// </summary>
        private void ClearDataGrid()
        {
            WaferList = new List<WMSLotInfo>();
            SelectWaferList = new List<WMSLotInfo>();
            gvQuery.Initialize();
        }        /// <summary>
        /// 查詢工單的資料
        /// </summary>
        private void QueryData()
        {
            try
            {
                string sWO = ttbWO.Text.Trim().ToUpper();
                string waferID = ttbWafer.Text.Trim();
                ClearTextBox();
                ReSetButton();
                ClearDataGrid();                ttbWO.Text = sWO;                if (sWO.IsNullOrTrimEmpty())
                {
                    AjaxFocus(ttbWO);
                    ProgramInformationBlock1.ShowException(Msg.Error.PleaseInput(lblWO.Text));
                    return;
                }                TargetWorkOrderData = null;
                #region 取得製令資訊，及畫面上欄位：QUANTITY RELEASEQTY ROUTE(生產流程) PROCESSCODE DEVICE(品號)
                WorkOrderData = WorkOrderInfoEx.GetWorkOrderByWorkOrder(sWO);
                if (WorkOrderData == null)
                {
                    AjaxFocus(ttbWO);
                    lblStandardProdFlag.Text = string.Empty;
                    throw new CimesException(Msg.Error.TheWorkOrderDoesNotExist());
                }                string flag = WorkOrderData.Flag.ToUpper();
                if (flag != "RELEASE" && flag != "CREATED")
                {
                    throw new CimesException(Msg.Error.WorkOrderStatusNotSomethingCanNotUse(sWO, "Release、Created", ProgramInformationBlock1.Caption));
                }                //未發放數量 = 工單數量 - 已發放數量
                var unReleaseQty = WorkOrderData.Quantity - WorkOrderData.ReleaseQuantity;
                if (unReleaseQty <= 0)
                {
                    throw new CimesException(Msg.Error.WorkOrderQtyWereReleaseFinished());
                }
                #endregion                TargetWorkOrderData = InfoCenter.GetBySQL<WorkOrderInfo>("SELECT * FROM MES_WPC_WO WHERE ERPWO = #[STRING] AND MAINFLAG = 'Y'", WorkOrderData["ERPWO"].ToString());
                if (TargetWorkOrderData == null)
                {
                    throw new CimesException("工单: " + WorkOrderData.WorkOrder + "所属的目标成品工单不存在！");
                }
                var activeVersion = DBCenter.GetSingleResult<decimal>(
                    "SELECT ACTIVEVERSION FROM MES_CHIP_OPER_RECIPE WHERE DEVICE = #[STRING] AND ROUTE = #[STRING] AND ROUTEVER = #[DECIMAL]"
                    , TargetWorkOrderData.DeviceName, WorkOrderData.RouteName, WorkOrderData.RouteVersion);
                if (activeVersion <= 0)
                {
                    throw new CimesException("目标工单: " + WorkOrderData.WorkOrder + "没有加工明细设置！");
                }
                ttbRecipeVersion.Text = activeVersion.Format();
                #region 帶出批號數量：於品號設定批號容量屬性，帶出預設值
                List<WpcItemInfo> lotSizes = WpcItemInfo.GetClassItems("ChipDefaultLotSize");
                if (lotSizes.Count == 0)
                {
                    throw new CimesException(Msg.Error.ItemMasterClassDataNotFound("ChipDefaultLotSize"));
                }
                decimal tryDecimal;
                if (lotSizes[0].ItemName.IsNullOrTrimEmpty())
                {
                    throw new CimesException(Msg.Error.ItemMasterClassDataNotFound("ChipDefaultLotSize"));
                }
                if (!decimal.TryParse(lotSizes[0].ItemName, out tryDecimal))
                {
                    throw new CimesException(Msg.Error.InputValueHasInvalidFormatPleaseInputInteger("ChipDefaultLotSize"));
                }
                if (tryDecimal <= 0)
                {
                    throw new CimesException(Msg.Error.ContentMustBeLargeThanZero("ChipDefaultLotSize"));
                }
                LotSize = tryDecimal;
                #endregion
                ttbWOQty.Text = WorkOrderData.Quantity.ToString();
                ttbRoute.Text = WorkOrderData.RouteName;
                ttbWOComment.Text = WorkOrderData.Description;
                ttbDeviceName.Text = WorkOrderData.DeviceName;
                ttbUnReleaseQty.Text = unReleaseQty.ToString();                //var sourceSite = ddlSourceSite.GetText();
                //var waferID = ttbWafer.Text.Trim();                StringBuilder sb = new StringBuilder();
                List<string> args = new List<string>();
                sb.Append("SELECT * FROM MES_WMS_LOT WHERE ASSIGNWO = #[STRING] AND ISSUEWO = ASSIGNWO AND STATUS = 'Booking' ");
                args.Add(sWO);
                //if (!sourceSite.IsNullOrTrimEmpty())
                //{
                //    sb.Append(" AND SOURCESITE = #[STRING] ");
                //    args.Add(sourceSite);
                //}                if (!waferID.IsNullOrTrimEmpty())
                {
                    sb.Append(" AND (WAFERID LIKE #[STRING] OR EPILASERMARK LIKE #[STRING] ) ");
                    args.Add("%" + waferID + "%");
                    args.Add("%" + waferID + "%");
                    ttbWafer.Text = waferID;
                }                sb.Append(" ORDER BY WAFERID ");                //WaferList = InfoCenter.GetList<WMSLotInfo>(sb.ToString(), args.ToArray()).FindAll(p => p.WorkOrder.ToCimesString().IsIn("", "Empty"));
                WaferList = (from p in InfoCenter.GetList<WMSLotInfo>(sb.ToString(), args.ToArray())
                             where p.WorkOrder.IsNullOrTrimEmpty() || p.WorkOrder == "Empty"
                             select p).ToList();
                gvQuery.SetDataSource(SelectWaferList);
                gvQuery.DataBind();                ttbWafer.ReadOnly = false;
                AjaxFocus(ttbWafer);
            }
            catch (Exception ex)
            {
                ttbWafer.ReadOnly = false;
                CimesException.Rethrow(ex);
            }
        }        /// <summary>
        /// 查詢工單的資料
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnQuery_Click(object sender, System.EventArgs e)
        {
            try
            {
                QueryData();
            }
            catch (Exception ex)
            {
                HandleError(ex);
            }
        }        /// <summary>
        /// 取消此動作，回到上一個查詢畫面
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnCancel_Click(object sender, System.EventArgs e)
        {
            try
            {
                ReturnToPortal();
            }
            catch (Exception ex)
            {
                HandleError(ex);
            }
        }        /// <summary>
        /// 確定事件函式
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnOK_Click(object sender, EventArgs e)
        {
            try
            {
                if (SelectWaferList.Count == 0)
                {
                    throw new CimesException(Msg.Error.DataIsNotExist(lblWaferList.Text));
                }                #region 檢查挑選片數不可大於未發放數量。
                if (Convert.ToDouble(ttbSelectedQty.Text) > Convert.ToDouble(ttbUnReleaseQty.Text))
                {
                    throw new CimesException(Msg.Error.CanNotBeGreaterThan(lblSelectQty.Text, lblUnReleaseQty.Text));
                }
                #endregion                if (ttbRecipeVersion.Text.IsNullOrTrimEmpty())
                {
                    throw new CimesException("参数: " + lblRecipeVersion + "遗失！");
                }                
                List<string> lots = new List<string>();    

Answer 2

// 接上面代码
            #region Transaction宣告
                var txnStamp = new TransactionStamp(this.User.Identity.Name, ProgramRight, ProgramRight, this.ApplicationName);
                using (var cts = CimesTransactionScope.Create())
                {
                    #region 覆寫工單主檔已下線量欄位與狀態
                    var updated = false;
                    int retry = 0;
                    WorkOrderInfoEx txnWorkOrderData = null;
                    do
                    {
                        txnWorkOrderData = WorkOrderInfo.GetWorkOrderByWorkOrder(WorkOrderData.WorkOrder).ChangeTo<WorkOrderInfoEx>(false); //(WorkOrderInfoEx)WorkOrderData.DeepCopy();
                        txnWorkOrderData.ReleaseQuantity += ttbSelectedQty.Text.ToDecimal();// +WorkOrderData.ReleaseQuantity;
                        if (txnWorkOrderData.Flag != "Created") txnWorkOrderData.Flag = "Created";
                        if (txnWorkOrderData.ReleaseQuantity > txnWorkOrderData.Quantity)
                        {
                            throw new CimesException("批号创建数量不可大于工单原始数量！");
                        }
                        try
                        {
                            if (txnWorkOrderData.UpdateToDB(txnStamp.UserID, txnStamp.RecordTime) != 1)
                                throw new CimesException(Msg.Error.AtomicityViolation(lblWO.Text));
                        }
                        catch (Exception ex)
                        {
                            ++retry;
                            Thread.Sleep(100);
                        }
                        updated = true;
                    } while (!updated && retry < 5);
                    if (!updated)
                    {
                        throw new CimesException("已连续 5次尝试更新工单数据失败, 请稍后再次操作！");
                    }
                    #endregion
                    int catchCount = 0;
                    int size = 25;
                    IEnumerable<WMSLotInfo> catchList = null;
                    while ((catchList = SelectWaferList.Skip(size * catchCount).Take(size)).Count() != 0)
                    {
                        ++catchCount;                        #region 取得新Lot
                        var nextIDData = Naming.GenerateRunIDForCreate(WorkOrderData.LotType, User.Identity.Name);
                        string sLot = nextIDData.First;
                        if (nextIDData.Second != null)
                            nextIDData.Second.ForEach(p =>
                            {
                                if (DBCenter.ExecuteSQL(p) != 1)
                                    throw new CimesException(Msg.Error.AtomicityViolation("Naming Sequence"));
                            });
                        lots.Add(sLot);
                        LotCreateInfo createNewLot = GetCreateLotData(sLot, 0, txnWorkOrderData, TargetWorkOrderData, ttbRecipeVersion.Text.ToDecimal());
                        #endregion                        List<ComponentInfo> lstCreate = new List<ComponentInfo>();
                        catchList.LoopDo((item, i) =>
                        {
                            #region 产生新的Component
                            var comp = ComponentInfo.CreateLotNormalComponent(item.WAFERID, item.Quantity).ChangeTo<ComponentInfoEx>();
                            comp.Status = "Normal";
                            comp.WorkOrder = createNewLot.WorkOrder;
                            comp.Unit = createNewLot.Unit;
                            comp.SecondUnit = "PCS";
                            comp.SecondQuantity = comp.ComponentQuantity;
                            comp.LotSequence = createNewLot.Lot + "-" + (i + 1).ToString().PadLeft(2, '0');
                            comp.ErpDevice = WorkOrderData.ErpDevice;
                            comp.TopGrade = "1100";
                            comp.InternalDevice = WorkOrderData.ErpDevice;
                            comp.DeviceName = WorkOrderData.DeviceName;
                            comp.ProdType = "Wafer";
                            comp.LaserMark = item.EPILASERMARK;
                            comp["WAFER_TYPE"] = item.WAFER_TYPE;
                            comp["LIFE_GRADE"] = item.LIFE_GRADE;
                            lstCreate.Add(comp);
                            #endregion                            #region WMS 处理
                            item.WorkOrder = txnWorkOrderData.WorkOrder;
                            item.Status = WMSLotStatus.Created;
                            var history = item.InsertToHistory(txnStamp);
                            history.Lot = sLot;
                            history.UpdateToDB();                            var nonactive = item.Fill<WMSLotNonactiveInfo>(true);
                            nonactive.InsertImmediately(txnStamp.UserID, txnStamp.RecordTime);
                            if (item.DeleteFromDB() != 1) throw new CimesException(Msg.Error.AtomicityViolation("DELETE MES_WMS_LOT"));
                            #endregion
                        });                        createNewLot["QUANTITY"] = lstCreate.Sum(p => p.ComponentQuantity);
                        createNewLot["STARTQUANTITY"] = createNewLot["QUANTITY"].ToDecimal();
                        createNewLot["SQUANTITY"] = lstCreate.Count;
                        createNewLot["STARTCOMPQTY"] = lstCreate.Count;
                        WIPTransaction.CreateLots(new List<LotCreateInfo> { createNewLot }, txnStamp);
                        WIPTransaction.SetLotComponent(createNewLot, lstCreate, txnStamp);
                        WIPTransaction.StartLot(createNewLot, "Wait", txnStamp);                        #region 处理MES_WIP_COMP_NONACTIVE
                        var nonactiveData = DBCenter.GetDataTable("SELECT * FROM MES_WIP_COMP_NONACTIVE WHERE COMPONENTID IN (#[INSTRING])", new object[] { catchList.Select(p => p.WAFERID).ToArray() });
                        nonactiveData.AsEnumerable().LoopDo((row, i) =>
                        {
                            var di = DBInsert.Mix("MES_WIP_COMP_LOG");
                            foreach (DataColumn column in nonactiveData.Columns)
                            {
                                di[column.ColumnName] = row[column.ColumnName];
                            }
                            di.InsertToDB();
                            if (DBCenter.ExecuteParse("DELETE FROM MES_WIP_COMP_NONACTIVE WHERE WIP_COMP_SID = #[STRING]", row["WIP_COMP_SID"].ToString()) != 1)
                            {
                                throw new CimesException("处理MES_WIP_COMP_NONACTIVE资讯发生错误！");// 理论上不会发生
                            }
                            DBInsert<object> dbi = DBInsert.Mix("MES_CHIP_COMP_UNDO");
                            dbi["CHIP_COMP_UNDO_SID"] = DBCenter.GetSystemID();
                            dbi["COMPONENTID"] = row["COMPONENTID"].ToString();
                            dbi["LOT"] = createNewLot.Lot;
                            dbi["LINKSID"] = txnStamp.LinkSID;
                            dbi["USERID"] = txnStamp.UserID;
                            dbi["UPDATETIME"] = txnStamp.RecordTime;
                            dbi.InsertToDB();
                        });
                        #endregion
                    }                    cts.Complete();
                }
                #endregion
                Js.OpenNewPage(UpdatePanel1, "RunCardLot.aspx?Type=Proc&Lots=" + lots.Concat(p => p, "^", null).ToString());                ReSetButton();
                ttbSelectedQty.Text = "0";
                QueryData();                ProgramInformationBlock1.ShowMessage(Msg.Hint.ExecuteSuccessfully(ProgramInformationBlock1.Caption));
            }
            catch (Exception ex)
            {
                HandleError(ex);
            }
        }        LotCreateInfo GetCreateLotData(string sLot, decimal quantity, WorkOrderInfoEx WoData, WorkOrderInfo targerWO, decimal recipeVersion)
        {
            MES_WIP_LOT_CREATE.Singleton.SyncColumnsFromDB();
            MES_WIP_LOT.Singleton.SyncColumnsFromDB();
            var lotCreateData = LotCreateInfo.CreateLotInfo(WoData, sLot, quantity, 0);
            lotCreateData["CUSTNO"] = WoData.CUSTNO;
            lotCreateData["SITE"] = WoData.Factory;
            lotCreateData["SECTION"] = WoData.Section;
            lotCreateData["DESCR"] = WoData.Description;
            lotCreateData["ERPDEVICE"] = WoData.ErpDevice;
            lotCreateData["LOTCANCELFLAG"] = "Y";
            lotCreateData["TARGETWO"] = targerWO.WorkOrder;
            lotCreateData["RECIPEVERSION"] = recipeVersion;
            lotCreateData["DEVICE"] = targerWO.DeviceName;
            return lotCreateData;
        }  

Answer 3

//  接上面代码
      protected void ttbWafer_TextChanged(object sender, EventArgs e)
        {
            try
            {
                ttbWafer.Text = ttbWafer.Text.Trim();
                string strWaferID = ttbWafer.Must(lblWaferID);
                //檢查是否已刷入
                var wms = SelectWaferList.FirstOrDefault(p => p.WAFERID == strWaferID || p.EPILASERMARK == strWaferID);
                if (wms != null)
                {
                    throw new CimesException(Msg.Error.DataIsAlreadyExist(strWaferID));
                }                var findData = WaferList.FirstOrDefault(p => p.WAFERID == strWaferID || p.EPILASERMARK == strWaferID);
                if (findData == null)
                {
                    throw new CimesException(Msg.Error.DataIsNotExist(strWaferID));
                }                //刷入的Wafer數量不可超過批號數量
                int iLotSize = ttbLotSize.Text.ToInt();
                if (iLotSize < SelectWaferList.Count + 1)
                {
                    //[12141] 每盒數量不可大於{0}！
                    throw new CimesException(Msg.Error.EachBoxQtyCanNotOverThen(ttbLotSize.Text));
                }
                SelectWaferList.Add((WMSLotInfo)findData.DeepCopy());
                gvQuery.SetDataSource(SelectWaferList, true);
            }
            catch (Exception ex)
            {
                HandleError(ex);
            }
            finally
            {
                ttbWafer.Text = "";
                AjaxFocus(ttbWafer);
            }
        }        protected void gvQuery_DataBound(object sender, EventArgs e)
        {
            try
            {
                ttbSelectedQty.Text = SelectWaferList.Count.ToString();                btnOK.Enabled = (SelectWaferList.Count > 0);
            }
            catch (Exception ex)
            {
                HandleError(ex);
            }
        }        protected void gvQuery_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            try
            {
                WMSLotInfo WaferInfo = SelectWaferList[e.RowIndex];                //從清單中刪除此Wafer
                SelectWaferList.Remove(WaferInfo);                gvQuery.SetDataSource(SelectWaferList);
                gvQuery.DataBind();
            }
            catch (Exception ex)
            {
                HandleError(ex);
            }
        }        protected void ddlSourceSite_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                QueryData();
            }
            catch (Exception ex)
            {
                HandleError(ex);
            }
        }
    }
}

Answer 4

只是把所有代码贴出来，以供查看方便，不是让你们写代码的。只是分析哪里可能慢！代码无法调试！Oracle 不懂

Answer 5

没有高手吗？
就想知道从哪里下手，找出慢的SQL 语句，谢谢！

Answer 6

我认为也是这样，数据库的SQL 语句引起的。现在我就想找到这些有问题的语句，可是Oracle第一次接触，束手无策。代码也没有看出来有哪些语句。

Answer 7

请高手们出来，告诉我哪里可以追踪到速度慢？谢谢
代码不能运行，只能编译通过；Oracle数据库，我是第一次接触，谢谢！

Answer 8

我的意思是，
先编译一部分，运行，快，ok.
再增加一部分，运行，快，ok
...
再增加一部分，运行，慢，哦，问题再这部分。对出问题的代码再用上面的办法循环。也许很罗嗦，但总比没有办法的好。

Answer 9

这么长代码谁会用心看啊。：（
你用stopwatch，监控这个操作各个部分的用时，看看哪里执行的时间比较长，一般优先考虑的是代码出现问题。

Answer 10

我知道问题出在哪.
因为是从textchange事件里做的判断,而这是个后台方法,每改变一次值,页面就会回发一次,当然慢了
而扫条码是模拟的键盘,是一下子从键盘录入了10几个字符进去
那么页面需要刷新10几次,能不慢吗你需要做JS或JQ脚本,从前台验证是否输入完毕,然后再调后台方法存数据库
而不是每改变一个字符都走一遍后台方法

Answer 11

我大概知道问题出在哪.
可能是sql查询语法有问题
这部分可能要找原厂

Answer 12

条码枪的原理是扫出二维码然后加一个回车
ttbWafer_TextChanged为啥要更改一次查询一次呢？

Answer 13

ttbWafer_TextChanged 跟資料有關的變數有兩個
WaferList ,  SelectedWaferList
這兩個是由在this["XXXX"]取出來的裡面 
依照邏輯 變數WaferList是由QueryData填入
而QueryData只有在btnQuery_Click的時候才執行
而SelectedWaferList應該依照刷入的資料判斷是否存在
如果存在則報錯，不存再則加進去, 看起來根本沒有去查詢資料庫的樣子以上兩個變數都不存在 程式會不斷的存取資料庫的問題 往別的方向查一下吧 老大

Answer 14

加log看看是不是一个字符查询一次

Answer 15

23樓有懂Text_Change的原理?
有懂條碼槍的原理?懂了再提意見吧 大哥

Answer 16

亲, 你知道神马是asp.net吗

Answer 17

亲, 你知道神马是asp.net吗

Answer 18

show出处理时间 慢慢缩小范围

Answer 19

谢谢大家的激烈讨论，如果有办法调试代码，我也能看出大概问题。
现在两个问题：
1） 扫入WaferID 很慢， 我看代码也没有发送什么SQL 语句，请大家继续讨论。
2） 代码报错，好像是多国语言。我将25行 <asp:Label ID="lblProgress" runat="server" Text="<%$ Resources:Auto|RuleFace, DataProcessing %>" 改成不用资源对象： <asp:Label ID="lblProgress" runat="server" Text="Test"但是，一按F5运行，那个用户控件就又被自动改回去，代码就还报那个错误，这个有办法解决没？再次感谢大家捧场！！！