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

String concatenation is slow... Faster method for Bulk SQL string?

P: n/a
Hi all,

I'm concatenating a large SQL string for updating a table. There are >
80,000 commands (rows) in the SQL string. VB.NET seems to be *VERY* slow at
string concatenation when the string gets large...

Is it possible to execute a SQL command from a text file? .NET can
streamwrite extremely fast so I was thinking of writing all the commands to
a text file then executing the text file.

Is this possible? Or do I have to read the File back into a string and
submit it to ADO.NET?

Thanks.

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/
Nov 21 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Lucas Tam <RE********@rogers.com> wrote in
news:Xn***************************@140.99.99.130:
I'm concatenating a large SQL string for updating a table. There are >
80,000 commands (rows) in the SQL string. VB.NET seems to be *VERY*
slow at string concatenation when the string gets large...


Just gave StringBuilder a try... and it's MUCH faster.

I read somewhere that you shouldn't reuse the Stringbuilder object since
Strings are immutable. What is the best way to destory the string object
for reuse in a loop?

Thanks.

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/
Nov 21 '05 #2

P: n/a
Per documentation, String Objects are immutable while StringBuilder Objects
are mutable. So as long as you keep appending the sql statements to the
stringbuilder object, you should be fine - you shouldn't need to destroy any
objects then. At the very end when you're done building the entire sql, you
can just do ToString and execute it.

hope that helps..
Imran.

"Lucas Tam" <RE********@rogers.com> wrote in message
news:Xn***************************@140.99.99.130.. .
Lucas Tam <RE********@rogers.com> wrote in
news:Xn***************************@140.99.99.130:
I'm concatenating a large SQL string for updating a table. There are >
80,000 commands (rows) in the SQL string. VB.NET seems to be *VERY*
slow at string concatenation when the string gets large...


Just gave StringBuilder a try... and it's MUCH faster.

I read somewhere that you shouldn't reuse the Stringbuilder object since
Strings are immutable. What is the best way to destory the string object
for reuse in a loop?

Thanks.

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/

Nov 21 '05 #3

P: n/a
Hi Lucas,

Try to avoid the concatination of strings in anyway even with stringbuilder,
beside that it is not the right way, it is as well not good for security.

Try to use the command.parameters for that.
http://msdn.microsoft.com/library/de...classtopic.asp

Browse from here how to make the SQL string for that.

I hope this helps?

Cor
Nov 21 '05 #4

P: n/a
* Lucas Tam <RE********@rogers.com> scripsit:
I'm concatenating a large SQL string for updating a table. There are >
80,000 commands (rows) in the SQL string. VB.NET seems to be *VERY*
slow at string concatenation when the string gets large...


Just gave StringBuilder a try... and it's MUCH faster.

I read somewhere that you shouldn't reuse the Stringbuilder object since
Strings are immutable.


What do you mean by reusing the stringbuilder? Sure, you can reuse an
instance of stringbuilder as it's not immutable.

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>
Nov 21 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.