none
Table Valued Parameter question RRS feed

  • Question

  • I always write my SPs with Table valued parameters because I thought they will be good for performance (since people can send data in one shot... rather than call an SP in a loop)

    My friend pointed me to this article which says that Table Valued Parameters are good only for less than 1000 rows.

    http://msdn.microsoft.com/en-us/library/bb510489.aspx

    Is this this really true? I don't understand why TVPs would not scale beyond 1000 rows? (which looks miniscule by today's data standard)

    Also the articles says that > 1000 rows is OK for remote client process. but it doesn't say what is meant by that.

    Does it say that the .net code should not be running on the same machine as SQL? in my case this is always true because we have a dedicated sql server box.



    MSDNStudent Knows not much!

    Thursday, February 9, 2012 12:50 PM

Answers

  • At the bottom of the samples in the Link Alan provided to us is written, be aware this is just a sample situation.

    I assume that trying to load 1000 rows with 1Gb in the tables stored images (blobs) has another impact on the server and the total performance than 1000 rows with only one integer.


    Success
    Cor

    Monday, February 13, 2012 8:28 AM

All replies

  • I don't think there is a limitation, please read the below article for more info

    http://msdn.microsoft.com/en-us/library/bb675163.aspx [check the Limitations of Table-Valued Parameters section]
    http://aboutsqlserver.com/2010/08/01/

    Also, the link you have provided shows the comparision Table-Valued Parameters vs. BULK INSERT Operations and also in the "Restrictions" section they didn't mentioned this anyway.

    Thursday, February 9, 2012 4:33 PM
  • The article provided to me (hyperlinked in my mail) states


    Formatted data file on the server

    Complex

    < 1000

    Table-valued parameters

    Formatted data file on the server

    Complex

    > 1000

    BULK INSERT

    Remote client process

    Direct insert

    < 1000

    Table-valued parameters

    I don't know how you are interpretting this... but from what I read is don't use TVP for more than 1000 rows.

    Can you please provide correct interpretation?


    MSDNStudent Knows not much!

    Thursday, February 9, 2012 4:40 PM
  • Hi,

    Compared to bulk operations that have a greater startup cost than table-valued parameters, table-valued parameters perform well for inserting less than 1000 rows. It doesn't mean TVPs would not scale beyond 1000 rows. I'd like share some information about Bulk Insert in SQL from C#.

    But I found something strange in the link too:

    ------

    Remote client process

    Complex

    < 1000

    Table-valued parameters

    Remote client process

    Complex

    > 1000

    Table-valued parameters

    ------

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Monday, February 13, 2012 6:37 AM
    Moderator
  • > table-valued parameters perform well for inserting less than 1000 rows. It doesn't mean TVPs would not scale beyond 1000 rows

    Isn't this statement dubious??? I mean if something is good for inserting less than 1000 rows then it does not scale! 


    MSDNStudent Knows not much!

    Monday, February 13, 2012 7:15 AM
  • At the bottom of the samples in the Link Alan provided to us is written, be aware this is just a sample situation.

    I assume that trying to load 1000 rows with 1Gb in the tables stored images (blobs) has another impact on the server and the total performance than 1000 rows with only one integer.


    Success
    Cor

    Monday, February 13, 2012 8:28 AM
  • I assume that trying to load 1000 rows with 1Gb in the tables stored images (blobs) has another impact on the server and the total performance than 1000 rows with only one integer.

    I don't understand what does this statement mean? what are you assuming? what is integer??


    MSDNStudent Knows not much!

    Monday, February 13, 2012 1:46 PM
  • Hi,

    It depands how big is one record, I think you can try to use Performance tool to compare Bulk insert with TVP.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Friday, February 17, 2012 9:02 AM
    Moderator
  • Integer is the smallest value type which can be used on a Database Server in rows.

    A Blob or image (inside the tablerows and not the reference ones) whatever you call it the biggest.

    http://msdn.microsoft.com/en-us/library/ms187752.aspx

    For the rest see the reply from Alan,


    Success
    Cor

    Saturday, February 18, 2012 9:53 AM
  • I have try the TVP for the more than 1200 record. I am doing all the validation before putting the data into DataTable then I am passing that DataTable as a TVP.

    I am using below line of code:-

    SqlParameter tableValuedParameter = new SqlParameter();
                    tableValuedParameter.ParameterName = "@TVPData";
                    tableValuedParameter.SqlDbType = System.Data.SqlDbType.Structured;
                    tableValuedParameter.Value = ObjDataTable;

    I did not get any type of issue. But I am bit scared about my code just because of all above post.

    Kindly let me know the exact cases that cause the issue if we will use the more than 1000 records.


    • Edited by Quddoos Monday, January 20, 2014 2:19 PM more detail
    Monday, January 20, 2014 2:14 PM
  • I have try the TVP for the more than 1200 record. I am doing all the validation before putting the data into DataTable then I am passing that DataTable as a TVP.

    I am using below line of code:-

    SqlParameter tableValuedParameter = new SqlParameter();
                    tableValuedParameter.ParameterName = "@TVPData";
                    tableValuedParameter.SqlDbType = System.Data.SqlDbType.Structured;
                    tableValuedParameter.Value = ObjDataTable;

    I did not get any type of issue. But I am bit scared about my code just because of all above post.

    Kindly let me know the exact cases that cause the issue if we will use the more than 1000 records.


    quodos create your own Question and don't hack one of somebody else. You make now this answer less valuable for others searching for an answer.

    Success
    Cor

    Monday, January 20, 2014 4:46 PM