May 19, 2011 at 7:22 am
I have the following SQL CLR procedure....
It is called like the following:
exec sp_LoopCalcUnits1 'select top 5000 invoicekey from rb_ruleinvoice where rb_ruleid = 8251 ', 8251,3
As I run tests using the top 1000,2000, 3000 etc the performance is grea, but as I move into the 5000-10000 range it seems to be getting exponentially slower and I do not understand why.
Can anyone offer any advise?
Tony
[Microsoft.SqlServer.Server.SqlProcedure]
public static void sp_LoopCalcUnits1(String zSql, String zRuleId, int iCalcType)
{
SqlCommand cmd;
SqlConnection con = new SqlConnection("context connection=true");
cmd = new SqlCommand(zSql);
using (con)
{
try
{
// run the base query
cmd.Connection = con;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
List<String> myList = new List<string>();
long lCount;
while (rdr.Read())
{
myList.Add((String)rdr["invoicekey"]);
}
rdr.Close();
rdr.Dispose();
zSql = "insert into tftest values (1 ,CURRENT_TIMESTAMP)";
cmd.CommandText = zSql;
cmd.ExecuteNonQuery();
foreach (String zTemp in myList)
{
CalcUnits1(con, zTemp, zRuleId, iCalcType);
}
zSql = "insert into tftest values (100 ,CURRENT_TIMESTAMP)";
cmd.CommandText = zSql;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
}
public static void CalcUnits1(SqlConnection con,String zInvoiceKey, String zRuleId, int iCalcType)
{
// SqlConnection con = new SqlConnection("context connection=true");
SqlDataReader sdrResults;
SqlCommand cmd;
//SqlPipe pipe = SqlContext.Pipe;
String zSql;
decimal dSupplierID;
decimal dInvoiceTotal;
decimal dInvoiceSubtotal;
decimal dTermsDiscountAmount;
decimal dQueryUnits;
decimal dQueryGross;
decimal dQueryNet;
decimal dGrossTotal;
decimal dNetTotal;
decimal dUnitsTotal;
decimal dRebateUnitsTotal;
int iLoopCount;
dInvoiceTotal = 0;
dInvoiceSubtotal = 0;
dTermsDiscountAmount = 0;
dGrossTotal = 0;
dNetTotal = 0;
dUnitsTotal = 0;
dRebateUnitsTotal = 0;
// get the base invoice information
//using ( con )
//{
try
{
// run the base query
zSql = "";
dSupplierID = 0;
cmd = new SqlCommand("select TotalInvoiceAmount,InvoiceSubTotal,termsdiscountamount,supplierkey from invoicetotal a,invoice b where a.invoicekey = b.invoicekey and a.invoicekey = " + zInvoiceKey);
cmd.Connection = con;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
dSupplierID = (decimal)rdr["supplierkey"];
dInvoiceTotal = (decimal)rdr["TotalInvoiceAmount"];
dInvoiceSubtotal = (decimal)rdr["InvoiceSubTotal"];
dTermsDiscountAmount = (decimal)rdr["termsdiscountamount"];
}
rdr.Close();
rdr.Dispose();
cmd.Dispose();
if (dSupplierID != 0)
{
// if we found a supplier, make 3 passes at the invoice
// 1- lineitems
// 2- charges\allowances
// 3 - taxes
// for (iLoopCount = 1; iLoopCount <= 3; iLoopCount++)
for (iLoopCount = 1; iLoopCount <= 1; iLoopCount++)
{
switch (iLoopCount)
{
case 1:
zSql = "select sum(UnitsShipped),sum(GrossAmount),sum(NetAmount) from InvoiceLineItem a,Invoice b where ";
zSql = zSql + " a.invoicekey = " + zInvoiceKey;
zSql = zSql + " and a.invoicekey = b.invoicekey ";
zSql = zSql + " and a.ProductRebateCategory in (";
zSql = zSql + " select c.categorynumber from RB_RuleCategory c where c.rb_ruleid = " + zRuleId;
zSql = zSql + " and c.SupplierId = B.supplierkey";
zSql = zSql + ") ";
break;
case 2:
zSql = "select 1,a.TaxAmount,a.TaxAmount,a.ProductRebateCategory,'C' as AmountType from invoicetotaltax a,Invoice b where ";
zSql = zSql + " a.invoicekey = " + zInvoiceKey;
zSql = zSql + " and a.invoicekey = b.invoicekey ";
zSql = zSql + " and a.ProductRebateCategory in (";
zSql = zSql + " select c.categorynumber from RB_RuleCategory c where c.rb_ruleid = " + zRuleId;
zSql = zSql + " and c.SupplierId = B.supplierkey";
zSql = zSql + ") ";
break;
case 3:
zSql = "select 1,Amount,Amount,ProductRebateCategory,Type as AmountType from InvoiceTotalAdditionalCosts a,Invoice b where ";
zSql = zSql + " a.invoicekey = " + zInvoiceKey;
zSql = zSql + " and a.invoicekey = b.invoicekey ";
zSql = zSql + " and a.ProductRebateCategory in (";
zSql = zSql + " select c.categorynumber from RB_RuleCategory c where c.rb_ruleid = " + zRuleId;
zSql = zSql + " and c.SupplierId = B.supplierkey";
zSql = zSql + ") ";
break;
}
dQueryUnits = 0;
dQueryGross = 0;
dQueryNet = 0;
// get the units and update the total information
if (zSql != "")
{
cmd.CommandText = zSql;
sdrResults = cmd.ExecuteReader();
while (sdrResults.Read())
{
dQueryUnits = (decimal)sdrResults[0];
dQueryGross = (decimal)sdrResults[1];
dQueryNet = (decimal)sdrResults[2];
dGrossTotal = dGrossTotal + dQueryGross;
dNetTotal = dNetTotal + dQueryNet;
dUnitsTotal = dUnitsTotal + dQueryUnits;
}
sdrResults.Close();
sdrResults.Dispose();
cmd.Dispose();
switch (iCalcType)
{
case 1:
dRebateUnitsTotal = dRebateUnitsTotal + dQueryGross;
break;
case 2:
dRebateUnitsTotal = dRebateUnitsTotal + dQueryNet;
break;
case 3:
dRebateUnitsTotal = dRebateUnitsTotal + dQueryUnits;
break;
}
}
}
}
zSql = "insert into tftest values (" + zInvoiceKey + " ,CURRENT_TIMESTAMP)";
cmd.CommandText = zSql;
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
//}
}
May 19, 2011 at 9:21 am
hi there,
instead of concatenating your strings with str1 +=, use stringbuilder class.
in this way, you will shorten execution time.
here are examples of using stringbuilder class: http://www.google.com/#sclient=psy&hl=en&biw=1280&bih=899&source=hp&q=stringbuilder+.net+example+c%23&aq=f&aqi=&aql=&oq=&pbx=1&bav=on.2,or.r_gc.r_pw.&fp=f6843edb09e9953a
good luck,
tonci korsano
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply