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

[Lucas Tam]

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/

[Lucas Tam]

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/

[Imran Koradia]

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/

[Cor Ligthert]

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

[Herfried K. Wagner [MVP]]

* 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/>