Re: SQL Transaction takes lot of time to complete job

From: Miha Markic [MVP C#] (miha)
Date: 08/25/04 

Date: Wed, 25 Aug 2004 15:06:16 +0200

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" <Prasad@discussions.microsoft.com> wrote in message 
news:D1258FD9-3884-47D3-86AD-CA63894C89D5@microsoft.com...
> 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 [MVP C#]" wrote:
>
>> 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" <Prasad@discussions.microsoft.com> wrote in message
>> news:60F63D23-3237-4663-A6D3-06ADA3A8573C@microsoft.com...
>> >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
>> >
>>
>>
>>

Relevant Pages
Loading