Click here to Skip to main content
15,880,503 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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			
/****** Object:  UserDefinedFunction [dbo].[ProcessRouting_2]    Script Date: 13/12/2012 8:21:45 AM ******/			
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

VB
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()
Posted
Comments
Kuthuparakkal 13-Dec-12 1:26am    
I would approach this using Stored Procedures instead functions and also move the SQL statement written in VB...

Also indefinte timeout(zero) is not good, try to set it as some 60 sec or something.
snamyna 13-Dec-12 19:51pm    
Thank you, for your information, others project we also use store procedure and
the result still same (slow during busy time and will be normal if not busy).

1 solution

Just create a single Stored Procedure which performs all the Insertions and Selections, and call it in your VB.NET code. It will definitely reduce response time.


VB
Dim command As SqlCommand
command = New SqlCommand()
command.Connection = sqlCon
command.CommandType = CommandType.StoredProcedure
command.CommandText = "DB_Insert_NewCustomer"
command.Parameters.Add("@Customer_Name", SqlDbType.VarChar).Value = parameters(0)
command.Parameters.Add("@Gender_Code", SqlDbType.Int).Value = Convert.ToInt32(parameters(1))
command.Connection.Open()
command.ExecuteNonQuery()
command.Connection.Close()


Hope this helps you with performance issues.
 
Share this answer
 
Comments
snamyna 13-Dec-12 19:51pm    
Thank you, for your information, others project we also use store procedure and
the result still same (slow during busy time and will be normal if not busy).
Instead of modify coding, any suggestion in the hardware (sql server or networking)?
[no name] 15-Dec-12 8:48am    
Check your DB Structure and most importantly Indexes on your tables. Use No Locks on select queries. These are just basics, There are much more tips available for database, server optimization.
snamyna 17-Dec-12 1:55am    
I already review my DB Structure and add the Indexes. Today performance already improve.I will wait the result by end of this month (closing period), where many user/process.
snamyna 1-Jan-13 19:02pm    
Performance by end of this month already improve. Thank you for the solution.
[no name] 2-Jan-13 6:14am    
You are Welcome snamyna :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900