newbee memory question

[tony]

Hello,

I am trying to loop through a collection of objects, create a sql
string
from data held within those objects and insert into a database.

Each time through the loop the program seems to consume more memory to
the
point it almost slows to a halt. The first 200 inserts take around 1
second,
but it takes 15 minutes to to 1200. I can watch memory usage increase
as it loops through the collection.
Some code:

Customers is a collection of customer objects provided by a 3rd party
application. myObjDb is a simple database abstraction class,
addslashes is a simple function that escapes special charaters
using Regex

foreach(Customer cust in customers)
{

string query ="insert ignore into dbseThxCustomers.tblCustomer"+
" (strCustomerName,strCustomerAccountReference,strCu stomerShortName"+
",fltCustomerAccountBalance,"+
" fltCustomerCreditLimit, strCustomerAddress1, strCustomerAddress2,"+
"strCustomerAddress3, strCustomerAddress4"+
" ,strCustomerContact, strCustomerTelephoneNumber,+
"strCustomerFaxNumber,strCustomerEmailAddress )"+
" VALUES ("+
"'" + myObjDb.addSlashes(cust.Name) +"'" +
",'" + myObjDb.addSlashes(cust.Reference)+"'" +
",'" + myObjDb.addSlashes(cust.ShortName)+"'" +
",'" + myObjDb.addSlashes(cust.Balance.ToString())+"'" +
",'" +
myObjDb.addSlashes(cust.CustomerAccount.CoreCredit Limit.ToString())+"'"
+
",'" + myObjDb.addSlashes(cust.AddressLine1)+"'" +
",'" + myObjDb.addSlashes(cust.AddressLine2)+"'" +
",'" + myObjDb.addSlashes(cust.AddressLine3)+"'" +
",'" + myObjDb.addSlashes(cust.AddressLine4)+"'" +
",'" + myObjDb.addSlashes(cust.ContactName)+"'" +
",'" + myObjDb.addSlashes(cust.TelephoneNumber)+"'" +
",'" + myObjDb.addSlashes(cust.FaxNumber)+"'" +
",'" + myObjDb.addSlashes(cust.TeMailAddress) + "')";

}

I have tries declaring string query outside the loop, setting it to
null at
the end of the loop and using stringBuilder but same result every
time.

Hope I have provided enough info.

TIA tony

[MarkT [developmentor]]

> I am trying to loop through a collection of objects, create a sql

string

The string type allocates a new object and copies the old string each time
which gets expensive as you have discovered.

Try using System.Text.StringBuilder instead of string. StringBuilder is
optimized for this type of work (building up a string from parts).

StringBuilder s = new StringBuilder();
s.Append("insert ignore into dbseThxCustomers.tblCustomer");
s.Append(...);

Mark

[Jeffrey Palermo, MCAD.Net]

Mark,
Using stringbuilder in this case will have no affect on performance or
memory consumption. See my blog post:
http://dotnetjunkies.com/WebLog/jpal.../31/49465.aspx

Tony, can you post the code you are using to actually send the sql to
the database? The problem is likely to be there. Also, please post
the code of the addShashes method so I can verify that logic isn't the
problem. The code you posted only does string concatenation. I fail
to see how this code causes this behavior.

Best regards,
Jeffrey Palermo
Blog: http://www.jeffreypalermo.com

[tony]

> Tony, can you post the code you are using to actually send the sql

to
the database? The problem is likely to be there. Also, please post
the code of the addShashes method so I can verify that

Thanks for the reply Jeffrey, heres the class I use for handling
connection to the database, it included the addslashes method

public class dbMysql
{
private OdbcConnection MyConnection;

public void connect(string MyConString)
{
MyConnection = new OdbcConnection(MyConString);
MyConnection.Open();
}

public string addSlashes(string query)
{
query = Regex.Replace(query,@"\\",@"\\");
query = Regex.Replace(query,"'","\\'");
query = Regex.Replace(query,"\"","\\\"");
return query;
}

public OdbcCommand getCommand()
{
OdbcCommand MyCommand = new OdbcCommand();
MyCommand.Connection = MyConnection;
return MyCommand;
}

public void close()
{
MyConnection.Close();
}
}
and the code that does the insert is simply
try
{
command.CommandText = query;
command.ExecuteNonQuery();
}
catch (OdbcException MyOdbcException)
{
Console.WriteLine("in exception");
for (int i=0; i < MyOdbcException.Errors.Count; i++)
{
MessageBox.Show("Message: " +
MyOdbcException.Errors[i].Message);
}
}

regards
tony

[tony]

I have just found that i was calling the wrong method on the customer
object

the line:
myObjDb.addSlashes(cust.CustomerAccount.CoreCredit Limit.ToString())+"'"

should actually be
", '" + myObjDb.addSlashes(cust.CreditLimit.ToString())+"' " +
My appologies for the bogus question...

Regards
Tony

[MarkT [developmentor]]

> Using stringbuilder in this case will have no affect on performance or

memory consumption. See my blog post:

It's late and I'm jet lagged, so I can't see how your test code in your blog
post applies here. This example did not concatenate literals like the tests
you did.

Thanks,

Mark

[Jeffrey Palermo, MCAD.Net]

Tony,
Not a problem. Do make sure that the "close()" method is getting
called after the transaction.

Sometimes talking through a problem leads to the answer.

Best regards,
Jeffrey Palermo
Blog: http://www.jeffreypalermo.com