[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Table variable : sending data in small chunks

Posted on 2012-09-17
10
Medium Priority
?
438 Views
Last Modified: 2012-10-14
I am reading from a csv file and sending data as table valued parameter to a stored procedure.  From what i have tested so far , I am able to process 300k records in 3 mins 30 seconds . The file may contain up to millions of records as we go. I wanted to know if its a good idea to send all these records to the stored procedure in one go or should I send them in batches of say 500k
0
Comment
Question by:mkrch
  • 5
  • 4
10 Comments
 
LVL 18

Expert Comment

by:vasto
ID: 38407756
Check the operation with the SQL profiler and you will see that there are inserts for each record behind the scene. I guess it doesn't matter what is the size of the chunk, the data is transferred record by record anyway.
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38408466
Remember table valued parameter is like a table variable , so inserting whole data in this table variable will not be a good idea. Microsoft it self suggest to use table valued parameter for less then 1000 records, and my point of view this no of records will also depends upon the no of columns  defined in table variable.

Read below Microsoft  article for more details.
http://msdn.microsoft.com/en-us/library/bb510489.aspx

Also set your chunk size some where around 500 to 800 , and check performance with profiler for each chunk size.
0
 

Author Comment

by:mkrch
ID: 38409615
I have 7 columns . I am not using any id columns at this time.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38409669
Then you can test your code with chunk size 500-800 through profiler ,
and can choose better chunk value.
0
 

Author Comment

by:mkrch
ID: 38428839
I did , there was no significatnt difference. Basically the mgmt wants me to send the data in batches. I want to know how can I do that. I have a datatable and I am inserting the datatable into TVP that is passed to a stored procedure. Is there a way I can send the data in batches like 1-10 then 10-20 etc . thanks
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38428938
If you want to read your file at fornt end then this is the better way for chunks processing.

otherwise you can have a look on below link, to get some idea about bulk file upload.

http://www.codeproject.com/Articles/439843/Handling-BULK-Data-insert-from-CSV-to-SQL-Server
0
 

Author Comment

by:mkrch
ID: 38429229
Thank you . Basically I am trying to break my datatable in chunks . For instance
Datatable = [1,2,3,4,5,6,7,8]
datatable2=[1,2,3], [4,5,6 ], [7,8.]....or something like that
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38431415
can you please describe in detail what you are trying to achieve ?
0
 

Accepted Solution

by:
mkrch earned 0 total points
ID: 38479041
I created an extension method to break the list in chunks and it works thanks.
http://stackoverflow.com/questions/769373/whats-a-clean-way-to-break-up-a-datatable-into-chunks-of-a-fixed-size-with-linq
0
 

Author Closing Comment

by:mkrch
ID: 38494388
efficient method
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question