SQL Transaction takes lot of time to complete job

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I developed a dotnet component it take data from Production Server ( 6 GB)
hence analyze the data after stores into Backup Database hence delete data
from Production server, in both connection opened with Read UnCommited. Iam
doing process for each day, open connection each day process once analyzed
stores data into respective servers then closes the connection.

When iam running this service for 1 month it completes with in 80 min
the same service running for 6 months then take to process for 1 month more
than 8 Hrs.


For intLoopCount = LBound(strArrInterchangeValues) To
UBound(strArrInterchangeValues)
If Not IsNothing(strArrInterchangeValues(intLoopCount)) Then
' Assign dta_outdoc_details Delete Query
strDeleteOutDocQuery = "delete from dta_outdoc_details
where nInDocKey in (select nInDocKey from dta_indoc_details where
nInterchangeKey in (" + strArrInterchangeValues(intLoopCount) + "))"
cmdProdCommand.CommandText = strDeleteOutDocQuery '
Assign dta_outdoc_details Delete Query string
cmdProdCommand.ExecuteNonQuery() ' Execute
dta_outdoc_details Delete Query

' Assign dta_indoc_details Delete Query
strDeleteInDocQuery = "delete from dta_indoc_details
where nInterchangeKey in (" + strArrInterchangeValues(intLoopCount) + ")"
cmdProdCommand.CommandText = strDeleteInDocQuery '
Assign dta_indoc_details Delete Query string
cmdProdCommand.ExecuteNonQuery() ' Execute
dta_indoc_details Delete Query

' Assign dta_interchange_details Delete Query
strDeleteInterchangeDataDetailsQuery = "delete from
dta_interchange_data where nInterchangeDataKey in (select nInterchangeDataKey
from dta_interchange_details where nInterchangeKey in (" +
strArrInterchangeValues(intLoopCount) + "))"
cmdProdCommand.CommandText =
strDeleteInterchangeDataDetailsQuery ' Assign dta_interchange_details Delete
Query string
cmdProdCommand.ExecuteNonQuery() ' Execute
dta_interchange_details Delete Query

' Assign dta_interchange_data Delete Query
strDeleteInterchangeDetailsQuery = "delete from
dta_interchange_details where nInterchangeKey in (" +
strArrInterchangeValues(intLoopCount) + ")"
cmdProdCommand.CommandText =
strDeleteInterchangeDetailsQuery ' Assign dta_interchange_data Delete Query
string
cmdProdCommand.ExecuteNonQuery() ' Execute
dta_interchange_data Delete Query
End If
Next
 
Hi Prasad,

Am I right that you are only deleting records in your loop?
Did you try running those delete stataments from within QueryAnalyzer to see
how much time does it take?
 
Hi Prasad,

That's why I asked you to check with QA.
Are you sure that there isn't a problem on the server?
You are just issuing a delete command afterall and .net has not much to do
with it.
I don't think this is a problem of .net rather it is server's problem.
So, if you restart the service it runs faster?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Prasad said:
Please go through problem fully.

Problem is not with SQl Server, problem when i am running service for
short
time it works fast if running for service long time it takes 8 times more
compartively serice running short time.

I developed a dotnet component it take data from Production Server ( 6 GB)
hence analyze the data after stores into Backup Database hence delete data
from Production server, in both connections are opened with Read
UnCommited
Sql Transaction type. Iam doing process for each day, open connection for
each day hence analyzed the data then stores data into respective servers
then closes the connection.

Each day opening connection and connection specific transaction once
completes close the connection. when new day starts again opens the
connection do the process again close connection.

When iam running this service for 1 month it completes with in 1 Hour 20
Min.
The same service running for 6 months 1 month completes like above time 1
Hour 20 Min, when goes to 2 nd 3 rd it takes for 1 Month 3 Hrs, if statrts
4
month then takes 5 Hrs, for 5 th Month takes 8 Hrs.

when i observed in SQL Enterprise Manager it opens some locks, but not
locked.

Please check the following code when iam deleteing some statements using
select statement.

Me.beginProductionTransaction() ' Begin Production Transaction
Read Uncomitted

cmdProdCommand = New SqlCommand() ' Create
Instance of cmdProdCommand
cmdProdCommand.Connection = cnnProdDbConnection ' Assign
Production DB Connection
cmdProdCommand.Transaction = traProdDbTransaction ' Assign
Production transaction
cmdProdCommand.CommandTimeout =
cnnProdDbConnection.ConnectionTimeout ' Assign Connection Timeout

For intLoopCount = LBound(strArrInterchangeValues) To
UBound(strArrInterchangeValues)
If Not IsNothing(strArrInterchangeValues(intLoopCount))
Then
' Assign dta_outdoc_details Delete Query
strDeleteOutDocQuery = "delete from dta_outdoc_details
where nInDocKey in (select nInDocKey from dta_indoc_details where
nInterchangeKey in (" + strArrInterchangeValues(intLoopCount) + "))"
cmdProdCommand.CommandText = strDeleteOutDocQuery '
Assign dta_outdoc_details Delete Query string
cmdProdCommand.ExecuteNonQuery() ' Execute
dta_outdoc_details Delete Query

' Assign dta_indoc_details Delete Query
strDeleteInDocQuery = "delete from dta_indoc_details
where nInterchangeKey in (" + strArrInterchangeValues(intLoopCount) + ")"
cmdProdCommand.CommandText = strDeleteInDocQuery '
Assign dta_indoc_details Delete Query string
cmdProdCommand.ExecuteNonQuery() ' Execute
dta_indoc_details Delete Query

' Assign dta_interchange_details Delete Query
strDeleteInterchangeDataDetailsQuery = "delete from
dta_interchange_data where nInterchangeDataKey in (select
nInterchangeDataKey
from dta_interchange_details where nInterchangeKey in (" +
strArrInterchangeValues(intLoopCount) + "))"
cmdProdCommand.CommandText =
strDeleteInterchangeDataDetailsQuery ' Assign dta_interchange_details
Delete
Query string
cmdProdCommand.ExecuteNonQuery() ' Execute
dta_interchange_details Delete Query

' Assign dta_interchange_data Delete Query
strDeleteInterchangeDetailsQuery = "delete from
dta_interchange_details where nInterchangeKey in (" +
strArrInterchangeValues(intLoopCount) + ")"
cmdProdCommand.CommandText =
strDeleteInterchangeDetailsQuery ' Assign dta_interchange_data Delete
Query
string
cmdProdCommand.ExecuteNonQuery() ' Execute
dta_interchange_data Delete Query
End If
Next







Manage Your Profile |Rules of Conduct
C2004 Microsoft Corporation. All rights reserved. Terms of Use |Trademarks
|Privacy Statement

Miha Markic said:
Hi Prasad,

Am I right that you are only deleting records in your loop?
Did you try running those delete stataments from within QueryAnalyzer to
see
how much time does it take?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Prasad said:
I developed a dotnet component it take data from Production Server ( 6
GB)
hence analyze the data after stores into Backup Database hence delete
data
from Production server, in both connection opened with Read UnCommited.
Iam
doing process for each day, open connection each day process once
analyzed
stores data into respective servers then closes the connection.

When iam running this service for 1 month it completes with in 80 min
the same service running for 6 months then take to process for 1 month
more
than 8 Hrs.


For intLoopCount = LBound(strArrInterchangeValues) To
UBound(strArrInterchangeValues)
If Not IsNothing(strArrInterchangeValues(intLoopCount))
Then
' Assign dta_outdoc_details Delete Query
strDeleteOutDocQuery = "delete from
dta_outdoc_details
where nInDocKey in (select nInDocKey from dta_indoc_details where
nInterchangeKey in (" + strArrInterchangeValues(intLoopCount) + "))"
cmdProdCommand.CommandText = strDeleteOutDocQuery '
Assign dta_outdoc_details Delete Query string
cmdProdCommand.ExecuteNonQuery() ' Execute
dta_outdoc_details Delete Query

' Assign dta_indoc_details Delete Query
strDeleteInDocQuery = "delete from dta_indoc_details
where nInterchangeKey in (" + strArrInterchangeValues(intLoopCount) +
")"
cmdProdCommand.CommandText = strDeleteInDocQuery '
Assign dta_indoc_details Delete Query string
cmdProdCommand.ExecuteNonQuery() ' Execute
dta_indoc_details Delete Query

' Assign dta_interchange_details Delete Query
strDeleteInterchangeDataDetailsQuery = "delete from
dta_interchange_data where nInterchangeDataKey in (select
nInterchangeDataKey
from dta_interchange_details where nInterchangeKey in (" +
strArrInterchangeValues(intLoopCount) + "))"
cmdProdCommand.CommandText =
strDeleteInterchangeDataDetailsQuery ' Assign dta_interchange_details
Delete
Query string
cmdProdCommand.ExecuteNonQuery() ' Execute
dta_interchange_details Delete Query

' Assign dta_interchange_data Delete Query
strDeleteInterchangeDetailsQuery = "delete from
dta_interchange_details where nInterchangeKey in (" +
strArrInterchangeValues(intLoopCount) + ")"
cmdProdCommand.CommandText =
strDeleteInterchangeDetailsQuery ' Assign dta_interchange_data Delete
Query
string
cmdProdCommand.ExecuteNonQuery() ' Execute
dta_interchange_data Delete Query
End If
Next
Click to expand...
Click to expand...
Click to expand...
 
Back
Top