r3plica
Well-known member
- Joined
- Mar 4, 2010
- Messages
- 86
- Programming Experience
- 3-5
I have seen this question asked before, but I am struggling.
I have created a stored procedure which is pretty complicated.
Here it is for reference:
This proc takes about 1 second to run in SQL Studio Manager, but when I called this from my code:
FillDataTable takes forever. Infact it times out.
Now I have stepped through my code and it is actually when I run the ExecuteReader command that it takes ages.
I can't understand why it would only take a second to run in T-SQL but over 2 minutes from my application.
Could someone please help me to fix this issue as it has happened on another report and is beginning to annoy me
Here is my BaseDataAcess.vb so you can see the offending code:
I have created a stored procedure which is pretty complicated.
Here it is for reference:
VB.NET:
ALTER PROC ChangeoverEffeciencyReport
@DateFrom DATETIME
, @DateTo DATETIME
AS
BEGIN
CREATE TABLE #Temp
(
DepartmentID INT NOT NULL
, Department VARCHAR(10) NOT NULL
, PlanEfficiency DECIMAL(12, 2) NOT NULL
, ChangeEfficiency DECIMAL(12, 2) NOT NULL
, CostStandard INT NOT NULL
, PlanStandard INT NOT NULL
, Actual INT NOT NULL
, HasBeenUpdated BIT NOT NULL
)
INSERT #Temp
SELECT
c.ID
, c.DepartmentCode
, 0
, 0
, 0
, 0
, 0
, 0
FROM WorkCentreLog AS a
INNER JOIN WorkCentre AS b
ON a.WorkCentreID = b.ID
INNER JOIN Department AS c
ON b.DepartmentID = c.ID
WHERE a.Date
BETWEEN @DateFrom
AND @DateTo
GROUP BY
c.ID
, c.DepartmentCode
DECLARE @ID VARCHAR(10)
SELECT TOP 1 @ID = DepartmentID
FROM #Temp
ORDER BY DepartmentID
WHILE @ID <> ''
BEGIN
-- First set to has been updated
Update #Temp
SET HasBeenUpdated = 1
WHERE Department =
(
SELECT DepartmentCode
COLLATE Latin1_General_CI_AS
FROM Department
WHERE ID = @ID
)
-- Actual
UPDATE #Temp
SET Actual =
(
SELECT ISNULL(SUM((DATEDIFF(Minute, StartTime, FinishTime))), 0)
FROM WorkCentreLog
WHERE Date
BETWEEN @DateFrom
AND @DateTo
AND EventID IN
(
SELECT ID
FROM Event
WHERE EventGroupID =
(
SELECT ID FROM EventGroup WHERE UPPER(Description) = 'CHANGEOVER'
)
)
AND WorkCentreID IN
(
SELECT ID
FROM WorkCentre
WHERE DepartmentID = @ID
)
)
WHERE Department =
(
SELECT DepartmentCode
COLLATE Latin1_General_CI_AS
FROM Department
WHERE ID = @ID
)
-- CostStandard
UPDATE #Temp
SET CostStandard =
(
SELECT SUM(t.CostStandard)
FROM
(
SELECT
(
SELECT CostStandard
FROM Process
WHERE ID = WorkCentreLog.ProcessID
) AS CostStandard
FROM WorkCentreLog
WHERE Date
BETWEEN @DateFrom
AND @DateTo
AND EventID IN
(
SELECT ID
FROM Event
WHERE EventGroupID =
(
SELECT ID FROM EventGroup WHERE UPPER(Description) = 'CHANGEOVER'
)
)
AND WorkCentreID IN
(
SELECT ID
FROM WorkCentre
WHERE DepartmentID = 7
)
) AS t
)
/*
-- PlanStandard
UPDATE #Temp
SET PlanStandard =
(
SELECT SUM(t.PlanStandard)
FROM
(
SELECT
(
SELECT PlanStandard
FROM Process
WHERE ID = WorkCentreLog.ProcessID
) AS CostStandard
FROM WorkCentreLog
WHERE Date
BETWEEN @DateFrom
AND @DateTo
AND EventID IN
(
SELECT ID
FROM Event
WHERE EventGroupID =
(
SELECT ID FROM EventGroup WHERE UPPER(Description) = 'CHANGEOVER'
)
)
AND WorkCentreID IN
(
SELECT ID
FROM WorkCentre
WHERE DepartmentID = 7
)
) AS t
)*/
SELECT TOP 1 @ID = DepartmentID
FROM #Temp
WHERE HasBeenUpdated = 0
ORDER BY DepartmentID
-- If nothing was returned
IF @@ROWCOUNT = 0
SET @ID = ''
END
SELECT
*
FROM #Temp
DROP TABLE #Temp
END
GOThis proc takes about 1 second to run in SQL Studio Manager, but when I called this from my code:
VB.NET:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports MachineTracker.Common
Public Class ChangeoverEfficiencyDataAccess
Inherits BaseDataAccess
Public Shared Function SelectEfficiencyByDate(ByVal DateFrom As DateTime, ByVal DateTo As DateTime) As EfficiencyCollection
Dim wEfficiency As New EfficiencyCollection
Dim dt As DataTable
Dim SQLCmd As New SqlCommand
SetConnectionString = ConfigurationManager.ConnectionStrings("myMachineTracker").ConnectionString
SQLCmd.CommandType = CommandType.StoredProcedure
SQLCmd.CommandText = "ChangeoverEffeciencyReport"
SQLCmd.CommandTimeout = 200
SQLCmd.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = DateFrom
SQLCmd.Parameters.Add("@DateTo", SqlDbType.DateTime).Value = DateTo
dt = FillDataTable(SQLCmd)
For Each dr As DataRow In dt.Rows
wEfficiency.Add(ParseEfficiency(dr))
Next
Return wEfficiency
End Function
Private Shared Function ParseEfficiency(ByVal dr As DataRow) As Efficiency
Dim coEff As New Efficiency
With coEff
.DepartmentID = CInt(dr("DepartmentID"))
.DepartmentCode = Trim(dr("Department"))
.ChangeEfficiency = CDbl(dr("ChangeEfficiency"))
.PlanEfficiency = CDbl(dr("PlanEfficiency"))
.CostStandard = CInt(dr("CostStandard"))
.PlanStandard = CInt(dr("PlanStandard"))
.Actual = CInt(dr("Actual"))
End With
Return coEff
End Function
End ClassFillDataTable takes forever. Infact it times out.
Now I have stepped through my code and it is actually when I run the ExecuteReader command that it takes ages.
I can't understand why it would only take a second to run in T-SQL but over 2 minutes from my application.
Could someone please help me to fix this issue as it has happened on another report and is beginning to annoy me
Here is my BaseDataAcess.vb so you can see the offending code:
VB.NET:
Imports System.Data
Imports System.Data.SqlClient
Public MustInherit Class BaseDataAccess
Private Shared mstrConnectionString As String
Private Shared mConn As SqlConnection
Public Shared WriteOnly Property SetConnectionString() As String
Set(ByVal value As String)
mstrConnectionString = value
End Set
End Property
Shared Function FillDataTable(ByVal pSQLCmd As SqlCommand) As DataTable
Dim dr As SqlDataReader
Dim dt As New DataTable
If mstrConnectionString <> "" Then
OpenConnection()
pSQLCmd.Connection = mConn ' Set our connection
dr = pSQLCmd.ExecuteReader
dt.Load(dr) ' Load the DataRead into our DataTable
dr.Close()
CloseConnection() ' Call our Sub
Else
Throw New ApplicationException("Connection String has not been set")
End If
Return dt
End Function
Shared Function FillDataSet(ByVal pSQLCmd As SqlCommand) As DataSet
Dim ds As New DataSet
Dim da As New SqlDataAdapter
If mstrConnectionString <> "" Then
OpenConnection()
pSQLCmd.Connection = mConn ' Set our connection
da.SelectCommand = pSQLCmd ' Set the SelectCommand
da.Fill(ds) ' Fill our DataSet
da.Dispose() ' Dispose our Data Adapter
CloseConnection() ' Call our Sub
Else
Throw New ApplicationException("Connection String has not been set")
End If
Return ds
End Function
Shared Sub ExecuteNonSelect(ByVal pSQLCmd As SqlCommand)
If mstrConnectionString <> "" Then
OpenConnection() ' Call our Sub
pSQLCmd.Connection = mConn ' Set our connection
pSQLCmd.ExecuteNonQuery() ' Execute
CloseConnection() ' Call our Sub
Else
Throw New ApplicationException("Connection String has not been set")
End If
End Sub
Private Shared Sub OpenConnection()
mConn = New SqlConnection ' Create our connection
mConn.ConnectionString = mstrConnectionString ' Set our ConnectionString
mConn.Open() ' Open the connection
End Sub
Private Shared Sub CloseConnection()
If mConn.State = ConnectionState.Open Then
mConn.Close() ' Close
mConn.Dispose() ' Dispose
End If
End Sub
End Class
