c# concate to string from large listarray

Question

I have a listArray with the following structure:

listArray[0] insert_idlist = listArray (value, value, value, ect)

This array contains about 2000 values in each dimension.

var string_list = "'" + string.Join("', '", insert_idlist[0]) + "'";
//var string_list = "'0169624'";

For db query purposes I am trying to concate the first dimension into a string as above. However when deployed this causes the program to hang, and based on previous experience with the deployment enviroment I suspect the large number of values is the cause.

when the commented line is enabled instead the code runs as expected.

Can anyone suggest an alternative method of concating that will provide a performance improvement?

Don't guess. Profile and find out the actual bottleneck (you may be right about the reason, but you may very well be wrong). — Oded, Dec 12 '12 at 11:59
@Oded I've spent most of the morning working on this. It is definitely the cause. — tracer tong, Dec 12 '12 at 12:00
Have you done any time measurements? How long concatenation takes? Are you sure that concatenation (not the db query itself) is a bottleneck? — default locale, Dec 12 '12 at 12:02
@default locale I'm pretty sure that would not be possible given our access level to the deployment enviroment. — tracer tong, Dec 12 '12 at 12:13
What about local testing with as similar a setup as production? You need to check _where_ the time is spent. Is it the concatenation? The network IO? The server? — Oded, Dec 12 '12 at 12:28

Jakub Konecki · Answer 1 · 2012-12-12T12:23:49.587

0

I think you could use Table-Value Parameters to pass lists to DB.

This will solve your concatenation problem by not having it in the first place.

Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

Start by reading this article on MSDN.

edited Dec 12 '12 at 12:23

answered Dec 12 '12 at 12:00

Jakub Konecki

45,581
7
87
126

This assumes SQL Server 2005+ – Oded Dec 12 '12 at 12:02
Yes, it does. It also assumes that OP wants to pass a list of elements to db query. – Jakub Konecki Dec 12 '12 at 12:04
@all This is ok. The sql server is 2008 r2 – tracer tong Dec 12 '12 at 12:04
@Jakub Can you explain this? I'm not very familiar with table-value parameters – tracer tong Dec 12 '12 at 12:16

score 0 · Answer 2 · answered Dec 12 '12 at 12:08

You could try a 'StringBuilder' and allocate sufficient space beforehand using the 'capacity' constructor argument. That way the string doesn't have to be copied to an ever longer buffer again and again while building the target string. You then do the join with a foreach statement or similar.

This may or may not be faster, depending on how 'string.Join' is implemented internally, which will likely differ in different versions of the .NET framework.

score 0 · Answer 3 · edited May 23 '17 at 12:03

0

If you're looking for performance gain at the expense of readability, using StringBuilder in a loop should be faster than String.Join<T>(string, IEnumerable<T>)(even more if you avoid using foreach).

Take a look at:

Stringbuilder in foreach slower than in for and String.Join() SUCKS on collections?

String.Join performance issue in C#

edited May 23 '17 at 12:03

Community

1
1

answered Dec 12 '12 at 13:17

f.cipriani

3,357
2
26
22

score -2 · Answer 4 · answered Dec 12 '12 at 12:01

-2

Pass as a parameters.

using (SqlCommand command = new SqlCommand("SELECT * FROM Dogs1 WHERE Name LIKE @Name", connection))
        {
        //
        // Add new SqlParameter to the command.
        //
        command.Parameters.Add(new SqlParameter("Name", dogName));          
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            int weight = reader.GetInt32(0);
            string name = reader.GetString(1);
            string breed = reader.GetString(2);
            Console.WriteLine("Weight = {0}, Name = {1}, Breed = {2}", weight,name,breed);
        }
        }

answered Dec 12 '12 at 12:01

Elshan

7,339
4
71
106

How do you know these are not passed in as a parameter? And even if they were not, why would this make anything faster? – Oded Dec 12 '12 at 12:03
Sure, it protects against SQL Injection. What's that got to do with the question? – Oded Dec 12 '12 at 12:08

c# concate to string from large listarray

4 Answers4