0
I have large C# application doing heavy I/O on our SQL 2008 R2 server. It's been running fine, but I've had to make some changes in the application, which has made the I/O load even heavier.
The application does a bunch of inserts to the DB as fast at the client system will allow, and the first 2-3 minutes it runs fast (1600 batch requests/sec), but slows down gradually and ends up around dragging along (>150 batch requests/sec). Wait time on logging is in the 70ms/sec range, spiking to <900 ms/sec when .Net SQLClient process suspends with a Wait Type : WRITELOG.
Any ideas?
Performance degrade 15 minutes
Following is the problematic code. Can't really refactor the code, and i know it's by far a good way to accomplish what i'm trying to do. This code has been running fine for 3+ years, but due to a new data source (not the one i'm inserting into), it's trying to catch up on 3+ years of data. As it's financial data, the issue here arises from inserting 3 years of daily interest transactions per case.
Again, it stars out fine by writing 50+ transactions per second, but quickly ends up with 1-2 per second over 15-20 minutes of degradation.
Data comes from another table in the same database, gets "sorted" in the below code, and exported as CSV from the target database when all transactions has finalized.
SqlCommand GTTrcmd = new SqlCommand(GTTr, BottasDBGTTr);
SqlDataReader GTTrReader = GTTrcmd.ExecuteReader();
while (GTTrReader.Read())
{
string FS = GTTrReader.GetString(0);
string FR = GTTrReader.GetString(1);
string CB = GTTrReader.GetString(2);
string CO = GTTrReader.GetString(3);
if ((FS != "" && FS != "0.000"))
{
trID = "4" + Sag;
InsertLine("TR", "ETR_AccountID", AccountID);
UpdateField("TR", "ETR_DebtorPNo", Sag);
UpdateField("TRD", "ETR_DebtorID", DebtorID);
UpdateField("TR", "ETR_Date", Date);
UpdateField("TR", "ETR_TransactionDate", DateN);
UpdateField("TR", "ETR_Source", "SVE");
UpdateField("TR", "ETR_TransactinId", trID);
UpdateField("TR", "ETR_SSN", GetSSN());
UpdateField("TR", "ETR_ReferencNo", HSag);
UpdateField("TRD", "ETR_TransactionAmount", FS.Insert(0, "-"));
UpdateField("TR", "ETR_FeesSalaer", FS.Insert(0, "-"));
UpdateField("TR", "ETR_TransactionType", "2");
UpdateField("TR", "ETR_AdjustmentType", "5");
if (UniREST.StartsWith("-"))
{
UpdateField("TR", "ETR_CurrentBalance", UniREST);
if (Double.Parse(FS.Replace(".", ",")) > Double.Parse(UniREST.Replace(".", ",").Remove(0, 1)))
{
UpdateField("TR", "ETR_Overpayment", UniREST.Remove(UniREST.Length - 2, 2));
}
}
else
{
UpdateField("TR", "ETR_CurrentBalance", UniREST);
}
UpdateField("TR", "ETR_CurrentBalanceCostsCourt", "0.000");
UpdateField("TR", "ETR_CurrentBalanceIntrestOnFees", "0.000");
UpdateField("TR", "ETR_CurrentBalancePrincipal", UniHBEL);
UpdateField("TR", "ETR_CurrentBalanceIntrestOnPrincipal", UniRBEL);
UpdateField("TR", "ETR_CurrentBalanceFeesSalaer", UniSBEL);
UpdateField("TR", "ETR_CurrentBalanceFeesReminder", UniXMBEL);
UpdateField("TR", "ETR_CurrentBalanceCostsBailiff", UniTBEL);
UpdateField("TR", "ETR_CurrentBalanceCostsCourt", UniUBEL);
//-------------NEW LINE -------------
trID = "5" + Sag;
InsertLine("TR", "ETR_AccountID", AccountID);
UpdateField("TR", "ETR_DebtorPNo", Sag);
UpdateField("TRD", "ETR_DebtorID", DebtorID);
UpdateField("TR", "ETR_Date", Date);
UpdateField("TR", "ETR_TransactionDate", DateN);
UpdateField("TR", "ETR_Source", "SVE");
UpdateField("TR", "ETR_TransactinId", trID);
UpdateField("TR", "ETR_SSN", GetSSN());
UpdateField("TR", "ETR_ReferencNo", HSag);
UpdateField("TRD", "ETR_TransactionAmount", FS);
UpdateField("TR", "ETR_CostsCourt", FS);
UpdateField("TR", "ETR_TransactionType", "2");
UpdateField("TR", "ETR_AdjustmentType", "4");
if (UniREST.StartsWith("-"))
{
UpdateField("TR", "ETR_CurrentBalance", UniREST);
if (Double.Parse(FS.Replace(".", ",")) > Double.Parse(UniREST.Replace(".", ",").Remove(0, 1)))
{
UpdateField("TR", "ETR_Overpayment", UniREST.Remove(UniREST.Length - 2, 2));
}
}
else
{
UpdateField("TR", "ETR_CurrentBalance", UniREST);
}
UpdateField("TR", "ETR_CurrentBalanceCostsCourt", "0.000");
UpdateField("TR", "ETR_CurrentBalanceIntrestOnFees", "0.000");
UpdateField("TR", "ETR_CurrentBalancePrincipal", UniHBEL);
UpdateField("TR", "ETR_CurrentBalanceIntrestOnPrincipal", UniRBEL);
UpdateField("TR", "ETR_CurrentBalanceFeesSalaer", UniSBEL);
UpdateField("TR", "ETR_CurrentBalanceFeesReminder", UniXMBEL);
UpdateField("TR", "ETR_CurrentBalanceCostsBailiff", UniTBEL);
UpdateField("TR", "ETR_CurrentBalanceCostsCourt", UniUBEL);
}
if (FR != "" && FR != "0.000")
{
trID = "6" + Sag;
InsertLine("TR", "ETR_AccountID", AccountID);
UpdateField("TR", "ETR_DebtorPNo", Sag);
UpdateField("TRD", "ETR_DebtorID", DebtorID);
UpdateField("TR", "ETR_Date", Date);
UpdateField("TR", "ETR_TransactionDate", DateN);
UpdateField("TR", "ETR_Source", "SVE");
UpdateField("TR", "ETR_TransactinId", trID);
UpdateField("TR", "ETR_SSN", GetSSN());
UpdateField("TR", "ETR_ReferencNo", HSag);
UpdateField("TRD", "ETR_TransactionAmount", FR.Insert(0, "-"));
UpdateField("TR", "ETR_FeesReminder", FR.Insert(0, "-"));
UpdateField("TR", "ETR_TransactionType", "2");
UpdateField("TR", "ETR_AdjustmentType", "6");
if (UniREST.StartsWith("-"))
{
UpdateField("TR", "ETR_CurrentBalance", UniREST);
if (Double.Parse(FR.Replace(".", ",")) > Double.Parse(UniREST.Replace(".", ",").Remove(0, 1)))
{
UpdateField("TR", "ETR_Overpayment", UniREST.Remove(UniREST.Length - 2, 2));
}
}
else
{
UpdateField("TR", "ETR_CurrentBalance", UniREST);
}
UpdateField("TR", "ETR_CurrentBalanceCostsCourt", "0.000");
UpdateField("TR", "ETR_CurrentBalanceIntrestOnFees", "0.000");
UpdateField("TR", "ETR_CurrentBalancePrincipal", UniHBEL);
UpdateField("TR", "ETR_CurrentBalanceIntrestOnPrincipal", UniRBEL);
UpdateField("TR", "ETR_CurrentBalanceFeesSalaer", UniSBEL);
UpdateField("TR", "ETR_CurrentBalanceFeesReminder", UniXMBEL);
UpdateField("TR", "ETR_CurrentBalanceCostsBailiff", UniTBEL);
UpdateField("TR", "ETR_CurrentBalanceCostsCourt", UniUBEL);
//-------------NEW LINE -------------
trID = "7" + Sag;
InsertLine("TR", "ETR_AccountID", AccountID);
UpdateField("TR", "ETR_DebtorPNo", Sag);
UpdateField("TRD", "ETR_DebtorID", DebtorID);
UpdateField("TR", "ETR_Date", Date);
UpdateField("TR", "ETR_TransactionDate", DateN);
UpdateField("TR", "ETR_Source", "SVE");
UpdateField("TR", "ETR_TransactinId", trID);
UpdateField("TR", "ETR_SSN", GetSSN());
UpdateField("TR", "ETR_ReferencNo", HSag);
UpdateField("TRD", "ETR_TransactionAmount", FR);
UpdateField("TR", "ETR_CostsCourt", FR);
UpdateField("TR", "ETR_TransactionType", "2");
UpdateField("TR", "ETR_AdjustmentType", "4");
if (UniREST.StartsWith("-"))
{
UpdateField("TR", "ETR_CurrentBalance", UniREST);
if (Double.Parse(FR.Replace(".", ",")) > Double.Parse(UniREST.Replace(".", ",").Remove(0, 1)))
{
UpdateField("TR", "ETR_Overpayment", UniREST.Remove(UniREST.Length - 2, 2));
}
}
else
{
UpdateField("TR", "ETR_CurrentBalance", UniREST);
}
UpdateField("TR", "ETR_CurrentBalanceCostsCourt", "0.000");
UpdateField("TR", "ETR_CurrentBalanceIntrestOnFees", "0.000");
UpdateField("TR", "ETR_CurrentBalancePrincipal", UniHBEL);
UpdateField("TR", "ETR_CurrentBalanceIntrestOnPrincipal", UniRBEL);
UpdateField("TR", "ETR_CurrentBalanceFeesSalaer", UniSBEL);
UpdateField("TR", "ETR_CurrentBalanceFeesReminder", UniXMBEL);
UpdateField("TR", "ETR_CurrentBalanceCostsBailiff", UniTBEL);
UpdateField("TR", "ETR_CurrentBalanceCostsCourt", UniUBEL);
}
if (CB != "" && CB != "0.000")
{
trID = "8" + Sag;
InsertLine("TR", "ETR_AccountID", AccountID);
UpdateField("TR", "ETR_DebtorPNo", Sag);
UpdateField("TRD", "ETR_DebtorID", DebtorID);
UpdateField("TR", "ETR_Date", Date);
UpdateField("TR", "ETR_TransactionDate", DateN);
UpdateField("TR", "ETR_Source", "SVE");
UpdateField("TR", "ETR_TransactinId", trID);
UpdateField("TR", "ETR_SSN", GetSSN());
UpdateField("TR", "ETR_ReferencNo", HSag);
UpdateField("TRD", "ETR_TransactionAmount", CB.Insert(0, "-"));
UpdateField("TR", "ETR_CostsBailiff", CB.Insert(0, "-"));
UpdateField("TR", "ETR_TransactionType", "2");
UpdateField("TR", "ETR_AdjustmentType", "3");
if (UniREST.StartsWith("-"))
{
UpdateField("TR", "ETR_CurrentBalance", UniREST);
if (Double.Parse(CB.Replace(".", ",")) > Double.Parse(UniREST.Replace(".", ",").Remove(0, 1)))
{
UpdateField("TR", "ETR_Overpayment", UniREST.Remove(UniREST.Length - 2, 2));
}
}
else
{
UpdateField("TR", "ETR_CurrentBalance", UniREST);
}
UpdateField("TR", "ETR_CurrentBalanceCostsCourt", "0.000");
UpdateField("TR", "ETR_CurrentBalanceIntrestOnFees", "0.000");
UpdateField("TR", "ETR_CurrentBalancePrincipal", UniHBEL);
UpdateField("TR", "ETR_CurrentBalanceIntrestOnPrincipal", UniRBEL);
UpdateField("TR", "ETR_CurrentBalanceFeesSalaer", UniSBEL);
UpdateField("TR", "ETR_CurrentBalanceFeesReminder", UniXMBEL);
UpdateField("TR", "ETR_CurrentBalanceCostsBailiff", UniTBEL);
UpdateField("TR", "ETR_CurrentBalanceCostsCourt", UniUBEL);
//-------------NEW LINE -------------
trID = "9" + Sag;
InsertLine("TR", "ETR_AccountID", AccountID);
UpdateField("TR", "ETR_DebtorPNo", Sag);
UpdateField("TRD", "ETR_DebtorID", DebtorID);
UpdateField("TR", "ETR_Date", Date);
UpdateField("TR", "ETR_TransactionDate", DateN);
UpdateField("TR", "ETR_Source", "SVE");
UpdateField("TR", "ETR_TransactinId", trID);
UpdateField("TR", "ETR_SSN", GetSSN());
UpdateField("TR", "ETR_ReferencNo", HSag);
UpdateField("TRD", "ETR_TransactionAmount", CB);
UpdateField("TR", "ETR_CostsCourt", CB);
UpdateField("TR", "ETR_TransactionType", "2");
UpdateField("TR", "ETR_AdjustmentType", "4");
if (UniREST.StartsWith("-"))
{
UpdateField("TR", "ETR_CurrentBalance", UniREST);
if (Double.Parse(CB.Replace(".", ",")) > Double.Parse(UniREST.Replace(".", ",").Remove(0, 1)))
{
UpdateField("TR", "ETR_Overpayment", UniREST.Remove(UniREST.Length - 2, 2));
}
}
else
{
UpdateField("TR", "ETR_CurrentBalance", UniREST);
}
UpdateField("TR", "ETR_CurrentBalanceCostsCourt", "0.000");
UpdateField("TR", "ETR_CurrentBalanceIntrestOnFees", "0.000");
UpdateField("TR", "ETR_CurrentBalancePrincipal", UniHBEL);
UpdateField("TR", "ETR_CurrentBalanceIntrestOnPrincipal", UniRBEL);
UpdateField("TR", "ETR_CurrentBalanceFeesSalaer", UniSBEL);
UpdateField("TR", "ETR_CurrentBalanceFeesReminder", UniXMBEL);
UpdateField("TR", "ETR_CurrentBalanceCostsBailiff", UniTBEL);
UpdateField("TR", "ETR_CurrentBalanceCostsCourt", UniUBEL);
}
if ((CO != "" && CO != "0.000"))
{
trID = "10" + Sag;
InsertLine("TR", "ETR_AccountID", AccountID);
UpdateField("TR", "ETR_DebtorPNo", Sag);
UpdateField("TRD", "ETR_DebtorID", DebtorID);
UpdateField("TR", "ETR_Date", Date);
UpdateField("TR", "ETR_TransactionDate", DateN);
UpdateField("TR", "ETR_Source", "SVE");
UpdateField("TR", "ETR_TransactinId", trID);
UpdateField("TR", "ETR_SSN", GetSSN());
UpdateField("TR", "ETR_ReferencNo", HSag);
UpdateField("TRD", "ETR_TransactionAmount", CO.Insert(0, "-"));
UpdateField("TR", "ETR_AdditionalVar1", CO.Insert(0, "-"));
UpdateField("TR", "ETR_TransactionType", "2");
UpdateField("TR", "ETR_AdjustmentType", "2");
if (UniREST.StartsWith("-"))
{
UpdateField("TR", "ETR_CurrentBalance", UniREST);
if (Double.Parse(CO.Replace(".", ",")) > Double.Parse(UniREST.Replace(".", ",").Remove(0, 1)))
{
UpdateField("TR", "ETR_Overpayment", UniREST.Remove(UniREST.Length - 2, 2));
}
}
else
{
UpdateField("TR", "ETR_CurrentBalance", UniREST);
}
UpdateField("TR", "ETR_CurrentBalanceCostsCourt", "0.000");
UpdateField("TR", "ETR_CurrentBalanceIntrestOnFees", "0.000");
UpdateField("TR", "ETR_CurrentBalancePrincipal", UniHBEL);
UpdateField("TR", "ETR_CurrentBalanceIntrestOnPrincipal", UniRBEL);
UpdateField("TR", "ETR_CurrentBalanceFeesSalaer", UniSBEL);
UpdateField("TR", "ETR_CurrentBalanceFeesReminder", UniXMBEL);
UpdateField("TR", "ETR_CurrentBalanceCostsBailiff", UniTBEL);
UpdateField("TR", "ETR_CurrentBalanceCostsCourt", UniUBEL);
//-------------NEW LINE -------------
trID = "11" + Sag;
InsertLine("TR", "ETR_AccountID", AccountID);
UpdateField("TR", "ETR_DebtorPNo", Sag);
UpdateField("TRD", "ETR_DebtorID", DebtorID);
UpdateField("TR", "ETR_Date", Date);
UpdateField("TR", "ETR_TransactionDate", DateN);
UpdateField("TR", "ETR_Source", "SVE");
UpdateField("TR", "ETR_TransactinId", trID);
UpdateField("TR", "ETR_SSN", GetSSN());
UpdateField("TR", "ETR_ReferencNo", HSag);
UpdateField("TRD", "ETR_TransactionAmount", CO);
UpdateField("TR", "ETR_CostsCourt", CO);
UpdateField("TR", "ETR_TransactionType", "2");
UpdateField("TR", "ETR_AdjustmentType", "4");
if (UniREST.StartsWith("-"))
{
UpdateField("TR", "ETR_CurrentBalance", UniREST);
if (Double.Parse(CO.Replace(".", ",")) > Double.Parse(UniREST.Replace(".", ",").Remove(0, 1)))
{
UpdateField("TR", "ETR_Overpayment", UniREST.Remove(UniREST.Length - 2, 2));
}
}
else
{
UpdateField("TR", "ETR_CurrentBalance", UniREST);
}
UpdateField("TR", "ETR_CurrentBalanceCostsCourt", "0.000");
UpdateField("TR", "ETR_CurrentBalanceIntrestOnFees", "0.000");
UpdateField("TR", "ETR_CurrentBalancePrincipal", UniHBEL);
UpdateField("TR", "ETR_CurrentBalanceIntrestOnPrincipal", UniRBEL);
UpdateField("TR", "ETR_CurrentBalanceFeesSalaer", UniSBEL);
UpdateField("TR", "ETR_CurrentBalanceFeesReminder", UniXMBEL);
UpdateField("TR", "ETR_CurrentBalanceCostsBailiff", UniTBEL);
UpdateField("TR", "ETR_CurrentBalanceCostsCourt", UniUBEL);
}
UpdateField("SN", "ESN_CurrentBalance_CostCourt", UniUBEL);
Console.WriteLine("Transaction [ACCOUNT IO] written for: " + Sag);
}
BottasDBGTTr.Close();
BottasDBGTTr.Open();
M.Whitefield
Posted 2019-05-28T22:04:29.387
Reputation: 1
Please post the "heavy I/O" code (both before and after your changes) so that we can look and see what the problem might be and what you might do about it. Your question, as written, doesn't give much to work with. – Darrin Cullop – 2019-05-28T22:07:12.400
Posted more info. Hope this clears up the question. – M.Whitefield – 2019-05-28T22:20:56.223