ExamGuru IT/.Net Guru : In DotNet Component using Tranaction state ReadUncommited for long time SQl server process slow on microsoft.public.dotnet.framework.component

.NETGURU

In DotNet Component using Tranaction state ReadUncommited for long time SQl server process slow

Messages

Related Types

This message was discovered on microsoft.public.dotnet.framework.component_services.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.

Post a new message to this list...

Chunduri prasad via .Net Guru (VIP)

I developed a dotnet component the main goal of this component is take data from Production Server
(6 GB) and analyze hence store into Local Database then Remove from Production Server.
Iam taking data from production day by day, when running service for one day it works very fast hardly each day transaction completes with in 5 Min. When running service for 6 months in that time also iam making loop and processing day by day for 1st month it takes 5 min, then it starts slowly 10,15 20 and 30 Min.

Iam using Transaction ReadUncommited for both Production and Backup Database.
Please check folowing code takes more time for processing huge data.

Me.beginProductionTransaction() ' Begin Production Transaction

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

-----------------------
Posted by a user from .Net Guru (http://www.dot-net-guru.com/)

<Id>u6jCcoHON0KvwobMr6F31Q==</Id>

Reply to this message...

Prateek Baxi (VIP)

Hi,

Looking at the query I have following suggestion which could increase the
performance.

1) Execute all your queries outside of for..next loop. In your for...next
loop generate the string which results in a comman seperated string. Using
this string u can execute all your queries

2) In your query use joins instead of sub-query. e.g. following query can be
written using join
Your Query
-------------
delete from dta_outdoc_details where nInDocKey in (select nInDocKey from
dta_indoc_details where nInterchangeKey in (" +
strArrInterchangeValues(intLoopCount) + "))"

Suggestion
------------
delete from dta_outdoc_details a INNER JOIN dta_indoc_details b ON
a.nInDocKey = b.nInDocKey AND b.nInterchangeKey in (" + strInterchangeValues
+ ")"

In above suggestion 'strInterchangeValues' is a comma seperated values
generated in your for..next loop

3) I could not locate the commit of transaction anywhere in the code.

4) Try shifting this code in a component and make use of COM+ service and
transaction features of the COM+ including object pooling

I hope this helps.

"Chunduri prasad via .Net Guru" wrote:

[Original message clipped]

Reply to this message...

System.Data.SqlClient.SqlCommand

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification