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(Custome r cust in customers)
{

string query ="insert ignore into dbseThxCustomer s.tblCustomer"+
" (strCustomerNam e,strCustomerAc countReference, strCustomerShor tName"+
",fltCustomerAc countBalance,"+
" fltCustomerCred itLimit, strCustomerAddr ess1, strCustomerAddr ess2,"+
"strCustomerAdd ress3, strCustomerAddr ess4"+
" ,strCustomerCon tact, strCustomerTele phoneNumber,+
"strCustomerFax Number,strCusto merEmailAddress )"+
" VALUES ("+
"'" + myObjDb.addSlas hes(cust.Name) +"'" +
",'" + myObjDb.addSlas hes(cust.Refere nce)+"'" +
",'" + myObjDb.addSlas hes(cust.ShortN ame)+"'" +
",'" + myObjDb.addSlas hes(cust.Balanc e.ToString())+" '" +
",'" +
myObjDb.addSlas hes(cust.Custom erAccount.CoreC reditLimit.ToSt ring())+"'"
+
",'" + myObjDb.addSlas hes(cust.Addres sLine1)+"'" +
",'" + myObjDb.addSlas hes(cust.Addres sLine2)+"'" +
",'" + myObjDb.addSlas hes(cust.Addres sLine3)+"'" +
",'" + myObjDb.addSlas hes(cust.Addres sLine4)+"'" +
",'" + myObjDb.addSlas hes(cust.Contac tName)+"'" +
",'" + myObjDb.addSlas hes(cust.Teleph oneNumber)+"'" +
",'" + myObjDb.addSlas hes(cust.FaxNum ber)+"'" +
",'" + myObjDb.addSlas hes(cust.TeMail Address) + "')";

}

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]]

RE: newbee memory question

> I am trying to loop through a collection of objects, create a sql[color=blue]
> string[/color]
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.Str ingBuilder instead of string. StringBuilder is
optimized for this type of work (building up a string from parts).

StringBuilder s = new StringBuilder() ;
s.Append("inser t ignore into dbseThxCustomer s.tblCustomer") ;
s.Append(...);

Mark

[Jeffrey Palermo, MCAD.Net]

Re: newbee memory question

Mark,
Using stringbuilder in this case will have no affect on performance or
memory consumption. See my blog post:

http://dotnetjunkies.com/WebLog/jpalermo/archive/2005/01/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]

Re: newbee memory question

> Tony, can you post the code you are using to actually send the sql[color=blue]
> to
> the database? The problem is likely to be there. Also, please post
> the code of the addShashes method so I can verify that[/color]

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.Op en();
}

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

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

public void close()
{
MyConnection.Cl ose();
}
}


and the code that does the insert is simply
try
{
command.Command Text = query;
command.Execute NonQuery();
}
catch (OdbcException MyOdbcException )
{
Console.WriteLi ne("in exception");
for (int i=0; i < MyOdbcException .Errors.Count; i++)
{
MessageBox.Show ("Message: " +
MyOdbcException .Errors[i].Message);
}
}

regards
tony

[tony]

Re: newbee memory question

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

the line:
myObjDb.addSlas hes(cust.Custom erAccount.CoreC reditLimit.ToSt ring())+"'"

should actually be
", '" + myObjDb.addSlas hes(cust.Credit Limit.ToString( ))+"'" +


My appologies for the bogus question...

Regards
Tony

[MarkT [developmentor]]

Re: newbee memory question

> Using stringbuilder in this case will have no affect on performance or[color=blue]
> memory consumption. See my blog post:[/color]

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]

Re: newbee memory question

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