0

This question was from 2012 for SQL Server 2008, and I wonder if with SQL Server 2016 and up it still is relevant: Should you make multiple insert calls or pass XML?

The argument in the above post is that the preparedocument and OPENXML are expensive calls and confusing to work with.

This post Save Array Or List Of Records As XML Data To SQL Server in one call seems to disagree with the previous post, but also mentions nothing about performance.

I have an entity that has a child properties of EntityTranslations which is a List<Translations>.

My issue with using a table type is that I find it to be clumsy on the .net side of the application.

Setting up the dataTable, columns, looping to insert each record into the table, then having a hard-coded reference to the new database table type something like 

DataTable translationDataTable = new DataTable("Translation");
translationDataTable.Columns.Add("Name", typeof(string));
translationDataTable.Columns.Add("Description", typeof(string));
translationDataTable.Columns.Add("Language", typeof(string));

foreach (Translation t in entityTranslations)
{
    DataRow newRow = translationDataTable.NewRow();
    newRow["Name"] = entityTranslations.Name;
    newRow["Description"] = entityTranslations.Description;
    newRow["Language"] = entityTranslations.Language;

    translationDataTable.Rows.Add(newRow);
}

List<IDbDataParameter> parameters = new List<IDbDataParameter>
{
    new SqlParameter("@Translations", SqlDbType.Structured)
    {
        TypeName = "dbo.TranslationTableType",
        Value = translationDataTable
    },
    new SqlParameter("CountryCode", countryCode),
};

It's the TypeName = "dbo.TranslationTableType", I take issue with VS

XmlSerializer xml = new XmlSerializer(typeof (List<Translation>));

using (MemoryStream memoryStream = new MemoryStream())
{
    xml.Serialize(memoryStream, entityTranslations);                    

    List<IDbDataParameter> parameters = new List<IDbDataParameter>();
    parameters.Add(new SqlParameter("Translations", Encoding.UTF8.GetString(memoryStream.ToArray())));                    
    parameters.Add(new SqlParameter("CountryCode", countryCode));
}

The question: is there a performance hit with SQL Server 2016 when inserting multiple rows via XML vs structure table vs many small inserts (and being part of a transaction on the .net side of code)?

Mike
  • 5,918
  • 9
  • 57
  • 94
  • I wouldn't hand-code a DataTable and Columns like that. Too easy to make mistakes and miss code changes to related classes. Far easier to build one from POCO objects with Reflection. You can decorate the POCO class and properties with Attributes if you need to specify schema/table name, string lengths, decimal scale/precision, etc., and they're defined in one place - directly on the class that needs them. Also... "Translations" and `Encoding.ASCII`? Surely you mean `Encoding.UTF8`? – AlwaysLearning Mar 26 '20 at 12:19
  • Changed to `UTF8` – Mike Mar 26 '20 at 12:23
  • 1
    Why not [just test it](https://ericlippert.com/2012/12/17/performance-rant/)? Only you can tell if there's any measurable performance overhead in *your* situation, and if there is, if you think that's worth the trade-off. Note that passing JSON and using `OPENJSON` has become another alternative with 2016, which is also worth testing. (And if you're not actually processing the rows on the database end, using `SqlBulkCopy` or individual inserts wrapped in a transaction is another alternative.) – Jeroen Mostert Mar 26 '20 at 12:36
  • @Jeroen Mostert has a good point about testing. Just keep in mind that performance will vary by rowcount, data size, contention, server & network resources. It is good to know what will give you good performance, but the answer will vary significantly, based on your conditions. – tgolisch Mar 26 '20 at 13:07
  • Sure I can do that, I was just coming here first in case someone already did or there was a clear cut winner (standard) of doing this type of operation – Mike Mar 26 '20 at 13:30
  • 1
    There can't be, as not all approaches apply to all versions of SQL Server, plus performance characteristics vary on the actual workload, plus the issue of which approach is "easier"/"more maintainable" has an inherently subjective component which can't be settled. People could write thorough, well-reasoned benchmarks (code and context) and put them up somewhere, but then that would exceed the scope of an SO answer, while anything less has the potential to bias and mislead readers forever (because "this guy tested it 10 years ago with a toy problem and claimed this was fastest"). – Jeroen Mostert Mar 26 '20 at 14:25

0 Answers0