Good Day
I would like to ask regarding SQL Performance when process many record.
Below is the no of record and processing time during not busy time(not many
user using system and network) and busy time.
STEP No Of Record Processing Time In Minutes (On Holiday , Not Busy Time) Processing Time In Minutes (Busy Time)
Process 1 646,84 < 4 15 ~ 20
Process 2 103,980 < 5 15 ~ 20
Process 3 418,96 < 4 15 ~ 20
Process 4 335,91 < 4 15 ~ 20
Process 5 654,20 < 3 15 ~ 20
Process 6 350,10 < 4 15 ~ 20
Process 7 167,718 < 10 15 ~ 20
Process 8 165,271 < 10 15~ 60 or Cannot Complete
Problem
1) Processing time slow and sometime cannot complete during busy time.
Before process we already shrink log file in sql server.
2) Development using vb.net 10 and sql server 2012.
3) Attach is a part of SQL Function and my coding.
SQL FUNCTION
USE [MP_Planning]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER FUNCTION [dbo].[ProcessRouting_2]
(
@PlanMonth integer, @MRPController varchar(10)
)
RETURNS @ResultData TABLE
(
[ID_N] integer,
[ITEMNO] integer,
[MRP_CONTROLLER_T] varchar(10),
[PLAN_MONTH]varchar(250),
[ITEMCODE] varchar(250),
[OPER_NO] integer,
[WORKCENTRE] varchar(250),
[PLANQTY] float,
[REJECTRATE] float,
[PLANDATE] datetime,
[NEW_PLANQTY] float,
[LEADTIME] integer,
[NEW_PLANDATE] datetime,
[SCRAP_QTY] float
)
AS
begin
with cteCalculation
(
[ID_N],
[ITEMNO],
[MRP_CONTROLLER_T],
[PLAN_MONTH],
[ITEMCODE],
[OPER_NO] ,
[WORKCENTRE],
[PLANQTY],
[REJECTRATE],
[PLANDATE],
[NEW_PLANQTY],
[LEADTIME],
[NEW_PLANDATE],
[SCRAP_QTY]
)
as
(
SELECT ID_N, ITEMNO, MRP_CONTROLLER_T, PLAN_MONTH,ITEMCODE, cast(OPER_NO as integer) as OPER_NO, WORKCENTRE, PLANQTY, REJECTRATE, PLANDATE,
cast(PLANQTY as float) AS NEW_PLANQTY, LEADTIME,
dbo.GetNewPlanDate(WORKCENTRE, PLANDATE) as NEW_PLANDATE, ROUND(REJECTRATE * PLANQTY,0) AS SCRAP_QTY
FROM dbo.DETAIL_SCHEDULE_TEMP_START
WHERE (OPER_NO = '0010') AND (PLAN_MONTH = @PlanMonth) AND (MRP_CONTROLLER_T = @MRPController)
union all
SELECT t.ID_N, t.ITEMNO,t.MRP_CONTROLLER_T, t.PLAN_MONTH, t.ITEMCODE, cast(t.OPER_NO as integer) as OPER_NO , t.WORKCENTRE, t.PLANQTY, t.REJECTRATE, t.PLANDATE,
c.NEW_PLANQTY - ROUND((c.NEW_PLANQTY * t.REJECTRATE),0) AS NEW_PLANQTY, t.LEADTIME,
dbo.GetNewPlanDate(t.WORKCENTRE,DATEADD(dd,t.LEADTIME,c.NEW_PLANDATE)) as NEW_PLANDATE, ROUND(c.NEW_PLANQTY * t.REJECTRATE,0) AS SCRAP_QTY
FROM dbo.DETAIL_SCHEDULE_TEMP_START t inner join cteCalculation c on cast(t.OPER_NO as integer) -10 = c.OPER_NO and
t.ITEMCODE = c.ITEMCODE and t.PLANDATE = c.PLANDATE AND t.PLAN_MONTH = @PlanMonth AND (T.MRP_CONTROLLER_T = @MRPController)
)
INSERT INTO @ResultData(
[ID_N],[ITEMNO],[MRP_CONTROLLER_T],[PLAN_MONTH],[ITEMCODE], [OPER_NO], [WORKCENTRE], [PLANQTY], [REJECTRATE] , [PLANDATE] ,[NEW_PLANQTY],[LEADTIME], [NEW_PLANDATE],[SCRAP_QTY])
Select * from (select * from cteCalculation)AS LST
#NAME?
RETURN
end
VB.net Coding
Dim normplandate As String = "INSERT INTO DETAIL_SCHEDULE_TEMP_1" & _
" SELECT MRP_CONTROLLER_T, ITEMCODE AS ITEM_CODE_T, cast(OPER_NO as integer) as OPER_NO_T, WORKCENTRE AS WORKCENTRE_T, REJECTRATE AS REJECTRATE_N," & _
" LEADTIME AS LEADTIME_N, '' AS STK_QTY_N, PLANQTY_N AS PLAN_QTY_N, SCRAP_QTY_N AS REJ_QTY_N, NEW_PLANQTY_N" & _
" AS NEW_PLAN_QTY_N, '' AS STK_PLAN_QTY_N, '' AS ACT_QTY_N, '' AS DIFF_QTY_N, STD_PLAN_DATE AS STD_PLAN_DATE_D," & _
" STD_PLAN_MONTH AS STD_PLAN_MONTH_N" & _
" FROM (SELECT ITEMNO, ID_N, ITEMCODE, MRP_CONTROLLER_T, OPER_NO, WORKCENTRE, REJECTRATE, LEADTIME, SUM(PLANQTY) AS PLANQTY_N, SUM(SCRAP_QTY)" & _
" AS SCRAP_QTY_N, SUM(NEW_PLANQTY) AS NEW_PLANQTY_N, STD_PLAN_MONTH, STD_PLAN_DATE" & _
" FROM (SELECT DB1.ITEMNO, DB1.STD_PLAN_MONTH, DB1.STD_PLAN_DATE, DB1.ID_N, DB1.ITEMCODE, DB1.MRP_CONTROLLER_T, DB1.OPER_NO," & _
" DB1.WORKCENTRE, DB1.REJECTRATE, DB1.LEADTIME, ProcessRouting_2_1.PLANQTY, ProcessRouting_2_1.SCRAP_QTY," & _
" ProcessRouting_2_1.NEW_PLANQTY" & _
" FROM (SELECT DBO.Q_PLSTDPLANDATE.ITEMNO, DBO.Q_PLSTDPLANDATE.STD_PLAN_MONTH, DBO.Q_PLSTDPLANDATE.STD_PLAN_DATE," & _
" DBO.Q_PLITEM_MASTER.ID_N, DBO.Q_PLITEM_MASTER.ITEMCODE, DBO.Q_PLITEM_MASTER.MRP_CONTROLLER_T," & _
" DBO.Q_PLITEM_MASTER.OPER_NO, DBO.Q_PLITEM_MASTER.WORKCENTRE, DBO.Q_PLITEM_MASTER.REJECTRATE," & _
" DBO.Q_PLITEM_MASTER.LEADTIME" & _
" FROM DBO.Q_PLSTDPLANDATE CROSS JOIN" & _
" DBO.Q_PLITEM_MASTER" & _
" WHERE (DBO.Q_PLSTDPLANDATE.STD_PLAN_MONTH = '" & intmonth & "') AND (DBO.Q_PLITEM_MASTER.MRP_CONTROLLER_T = '" & Me.cbMRP.Text & "'))" & _
" AS DB1 LEFT OUTER JOIN" & _
" (SELECT ID_N, ITEMNO, MRP_CONTROLLER_T, PLAN_MONTH, ITEMCODE, OPER_NO, WORKCENTRE, PLANQTY, REJECTRATE, PLANDATE," & _
" NEW_PLANQTY, LEADTIME, NEW_PLANDATE, SCRAP_QTY" & _
" FROM DBO.ProcessRouting_2('" & intmonth & "', '" & Me.cbMRP.Text & "') AS ProcessRouting_2_2) AS ProcessRouting_2_1 ON" & _
" DB1.STD_PLAN_MONTH = ProcessRouting_2_1.PLAN_MONTH AND DB1.ID_N = ProcessRouting_2_1.ID_N AND" & _
" DB1.STD_PLAN_DATE = ProcessRouting_2_1.NEW_PLANDATE AND DB1.ITEMCODE = ProcessRouting_2_1.ITEMCODE AND" & _
" DB1.OPER_NO = ProcessRouting_2_1.OPER_NO) AS DB3" & _
" GROUP BY ITEMNO, ID_N, ITEMCODE, MRP_CONTROLLER_T, OPER_NO, WORKCENTRE, REJECTRATE, LEADTIME, STD_PLAN_MONTH, STD_PLAN_DATE) AS DB4"
Dim cmd As SqlCommand = New SqlCommand(normplandate, thisConnection)
cmd.CommandTimeout = 0
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
Submit an article or tip