Sending list(of more than 50000 records) to the stored procedure as a parameter

Question

Post reply

Sending list(of more than 50000 records) to the stored procedure as a parameter

gsd1

Default port

Points: 1442
More actions
September 27, 2012 at 7:19 am

#267388

Is there a way that I can send the list as it is to the stored procedure as a parameter? (Instead of processing each record of the list to insert into the database from the front end)
Thank you...

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 1

You could use a table-valued parameter perhaps?

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

gsd1 Default port Points: 1442 More actions · Answer 2

laurie-789651,

Thank you for the suggestion. I will try this.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

laurie-789651 (9/27/2012)
You could use a table-valued parameter perhaps?
http://msdn.microsoft.com/en-us/library/bb510489.aspx

+1

This is where I would start as well.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

You can also try XML. Parsing XML can be a somewhat memory intensive process, but moving that much data is already going to be memory intensive, so it might be worth the price. It might work better than a table variable or it might not. Test both.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

Sure, in the spirit of objectivity XML might help you in some specific cases. If you're in need of every ms of performance test XML. You could also reasonably include delimited and fixed-width strings in that as well and maybe some others too like loading up a temp table before calling a proc.

Erland Sommarskog did some really nice work on this topic and was kind enough to publish his results. His test cases included one with a set of 50K inputs. The results are not conclusive (are results ever really conclusive for all cases? :-D) but definitely lean towards using a TVP. As usual it will depend but if you're not looking to do a deep dive using a TVP is a relatively safe bet and has a very nice synergy with ADO.NET DataTables and the Command object's Parameters Collection.

http://www.sommarskog.se/arrays-in-sql-perftest-2009.html#TVP

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

I've been through this type of thing many times before. Some process in some front end code takes 3 or 4 parameters and it then generates a shedload of data to pass to the database. I've seen them try to pass it as XML and the overhead of the extra bytes necessary for the tags killed "the pipe". They tried passing it as a CSV and splitting the data became a performance nightmare and it was still pretty tough on "the pipe". In desparation, they even tried sending the server INSERT/SELECT commands which just about melted "the pipe" down.

If your process is similar in that a small handful of parameters is given to some front end process and that process uses those parameters to generate the data, then consider just passing those small number of parameters to a process on the database and generating the data there. That's what I did for those other companies and something that would sometimes take hours (one company was generating a quarter million rows of data and trying to pass those over "the pipe") to something that took less than a second.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gsd1 Default port Points: 1442 More actions · Answer 7

Jeff Moden,

Thank you for the response.

I am receiving a list of 50000 records from a service.

I am planning to write a stored procedure with table-valued parameter and then pass the list to this sp.

If you are suggesting something different, can you explain little bit more with an example?

Regards,

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

gsd1 (9/28/2012)
Jeff Moden,
Thank you for the response.
I am receiving a list of 50000 records from a service.
I am planning to write a stored procedure with table-valued parameter and then pass the list to this sp.
If you are suggesting something different, can you explain little bit more with an example?
Regards,

What are these 50,000 rows received as/into? A SQL Server table? A disk file?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

gsd1 Default port Points: 1442 More actions · Answer 9

Service is defined with XSD, so,I receive data as a list.

Thanks & Regards,

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 10

gsd1 (9/28/2012)
Jeff Moden,
Thank you for the response.
I am receiving a list of 50000 records from a service.
I am planning to write a stored procedure with table-valued parameter and then pass the list to this sp.
If you are suggesting something different, can you explain little bit more with an example?
Regards,

Is the service something that you good folks built or is it a 3rd party service that you can't modify? Alsso, how is the service producing the rows? Is it creating them through some sort of algorithm or is it passing data through from some device?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nadrek SSC-Insane Points: 20039 More actions · Answer 11

If those rows are actual unique, collected data (as opposed to generated data, which Jeff Moden is addressing), then you might also consider something like BULK INSERT instead of parameter passing.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 12

Before going for BULK INSERT which implies there will be a directory where a file can be written that both the app and the SQL Server can reach I would ask what the app language is written in. If it's .NET then using the SqlBulkCopy class might be a better choice. The service call is made and the data is already in memory so it would bypass any need to write this data to a file and also limit the amount of permissions the app needs.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato