By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,812 Members | 863 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,812 IT Pros & Developers. It's quick & easy.

newbee memory question

P: n/a
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
Nov 16 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
> 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
Nov 16 '05 #2

P: n/a
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

Nov 16 '05 #3

P: n/a
> 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
Nov 16 '05 #4

P: n/a
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
Nov 16 '05 #5

P: n/a
> 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
Nov 16 '05 #6

P: n/a
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

Nov 16 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.