Click here to Skip to main content
12,356,455 members (68,934 online)
Rate this:
 
Please Sign up or sign in to vote.
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

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 12-Dec-12 14:01pm
snamyna1.4K
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

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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.


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.
  Permalink  
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)?
Sheikh Muhammad Haris 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.
Sheikh Muhammad Haris 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160621.1 | Last Updated 13 Dec 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100