0

Does anyone know how many INSERT commands per transaction SQL Server can handle at the same time? Basically, I'm building a long string with bunch of INSERT's (around 8000 INSERT commands, and each has around 600 characters) which then I will execute using a function SQLexcute(sql_text):

for(obj in objects) {
    sql_text += "INSERT(prop1, prop2, prop3, prop4, prop5) VALUES(" + obj.prop1 + "," + obj.prop2 + "," + obj.prop3 + "," + obj.prop4 + "," + obj.prop5+");"
}
SQLexcute(sql_text)
Bridge
  • 29,818
  • 9
  • 60
  • 82
Carlitos Overflow
  • 609
  • 3
  • 13
  • 41
  • I'm presuming you're using c# on the backend to generate the query text, if I'm correct, you should really be using a stringbuilder as opposed to concatenating the string like that, as every time you're adding something to a string, it's creating a new instance of the string (strings in c# are immutable). As far as the SQL query, inserting 8000 records at a shot really shouldn't be that big of a deal. I'm also presuming that you're cleaning the data for the object properties prior to generating the SQL query, as to disallow SQL injection? – user2366842 Jan 28 '15 at 15:11
  • 2
    The one thing to keep in mind is that SQL Server will **escalate** its locks to a **table-level exclusive lock** if you insert more than 5000 rows at once (in a single transaction) - so that table will be **exlusively locked** until that `INSERT` transaction commits - no other operations (not even `SELECT`) will be possible on that table in the mean time – marc_s Jan 28 '15 at 15:12
  • 1
    Please please *please* use parameters rather than munging together strings (use `SqlParameter`s on a `SqlCommand` if this is C#): http://www.sommarskog.se/dynamic_sql.html#SQL_injection P.S. You are missing the table name in your query text – Bridge Jan 28 '15 at 15:13
  • I'm not building on C#. i'm building the string on a different program. The programming language is Mosel – Carlitos Overflow Jan 28 '15 at 15:15
  • Regardless of the language the correct way to do this is with parameterized queries. In your case you might want to look at table valued parameters. It would perform much faster than 8k individual insert statements. – Sean Lange Jan 28 '15 at 16:17
  • @SeanLange, Do you have a simple example on how it can be parameterized the queries? – Carlitos Overflow Jan 28 '15 at 16:45
  • I don't know anything about mosel so I can't help you with the syntax but surely any site that has examples of this language will have some examples. If not that, the documentation should. I would probably move this to a stored procedure and receive a table valued parameter with the 8k rows you want inserted – Sean Lange Jan 28 '15 at 19:06
  • @SeanLange TVP probably won't work if the only interface is ODBC: https://www.msi-jp.com/xpress/learning/square/%28B%29Modeling%20and%20problem_Summary.pdf (start at page 101). – Solomon Rutzky Jan 29 '15 at 04:54
  • 1
    Ugh that stinks. Regardless it should be able to handle parameterized queries. Even passing in a massive string of values and splitting it would seem better performance wise than 8,000 individual inserts. – Sean Lange Jan 29 '15 at 14:20
  • @SeanLange, parameterized sql queries worked. since i'm using odbc driver, i had to increase the buffer size too. – Carlitos Overflow Jan 30 '15 at 20:28
  • Awesome. Not only is your code much safer it is probably faster too. :) – Sean Lange Jan 30 '15 at 20:29

1 Answers1

0

Sean is right.

Do not execute the code in mosel. You have to create your query in T-SQL and pass the parameters there.

Here you have the link for table valued param:

Tabled valued param

Daniel Calbimonte
  • 35
  • 4
  • TVP probably won't work if the only interface is ODBC: https://www.msi-jp.com/xpress/learning/square/%28B%29Modeling%20and%20problem_Summary.pdf (start at page 101). – Solomon Rutzky Jan 29 '15 at 04:54