SQLServerCentral / Discuss Content Posted by Solomon Rutzky / Article Discussions / Article Discussions by Author / Streaming Data Into SQL Server 2008 From an Application / Latest Posts

RE: Streaming Data Into SQL Server 2008 From an Application

mark hutchinson — Tue, 29 May 2012 07:07:07 GMT

@SolomonMy transaction range was based on some testing I'd done a long time ago. I'm surprised that a batch Insert transaction would slow/hang at 100. I agree that the transaction size can be much larger, but the performance levels off after a point and the memory cost becomes a consideration.My statement about a 3M row transaction being ill-advised was also based on old testing. Both TVP and transactions cache the Insert data differently. Since TVPs are a more recent feature, I would hope that it is inherently efficient. My experience with huge transactions is that there is a real memory resource hit and that slows everything on the system. If you have a server with sufficient physical memory, you would not experience this hit. I would expect you to see memory use spike on a performance monitor.My suggestion about multiple destination staging tables was meant to address table locking for multiple simultaneous users' batch Insert activities.TVP is a beautiful thing. My original comment was inquiring whether any performance test had been done using transactions. If TVP is sufficiently faster or more efficient, it might cause some people to change their batch Insert code.Mark

RE: Streaming Data Into SQL Server 2008 From an Application

Solomon Rutzky — Mon, 28 May 2012 22:24:03 GMT

[quote][b]mark hutchinson (5/17/2012)[/b][hr]1. generally, you will find that 500-1000 rows/transaction will give the optimal performance. Beyond that, you won't see much performance improvement.[/quote]Hi Mark and thanks for the reply.I would advise against having, or promoting, a set range of rows per transaction as there are far too many variables that play into overall performance. I have seen queries that work great at 80 rows and hang at 100 as well as other queries that are fine at the 5000 - 10,000 range. It all comes down to testing to find what is optimal for a particular situation.[quote]2. doing all 3 million rows in the transaction adversely impacts server resources and performance.[/quote]Can you be more specific, please? My testing shows that using a streaming TVP for 3 million rows is actually not that impacting to CPU and Memory, so what resource(s) are you thinking of? Sure, the tran log might grow slightly faster/larger but that might be worth the trade-off. Also keep in mind that the 3 million rows is a contrived example to show the disparity between the different mechanisms used to get the data into the DB. If locking a table for 20 seconds is too long and increases blocking, then it can be batched, but that wasn't really the point of the article.[quote]3. If the upload table is going to be shared, you might also consider doing individual uploads to separate tables (one per user) and then executing an Insert statement from the upload table to the shared table -- cached upload.[/quote]I don't see any particular gain here as the INSERT into the destination table should take the same amount of time either way. But the beauty of the TVP approach, even if not streaming, is that the incoming data can first go into a temporary table (if any massaging of the data needs to occur or if something needs to be indexed) and then into the shared table (possibly in batches), thus eliminating any need for additional schema (i.e. the per-user tables). Otherwise, assuming a more typical case of thousands, tens of thousands, or even several hundred thousand rows to be imported, the operation would likely happen quickly enough to not even need those additional steps. But again, testing is key and it is easy enough to add the intermediary temp table.Take care,Solomon...

RE: Streaming Data Into SQL Server 2008 From an Application

mark hutchinson — Thu, 17 May 2012 06:31:45 GMT

1. generally, you will find that 500-1000 rows/transaction will give the optimal performance. Beyond that, you won't see much performance improvement.2. doing all 3 million rows in the transaction adversely impacts server resources and performance.3. If the upload table is going to be shared, you might also consider doing individual uploads to separate tables (one per user) and then executing an Insert statement from the upload table to the shared table -- cached upload.

RE: Streaming Data Into SQL Server 2008 From an Application

Solomon Rutzky — Sat, 12 Dec 2009 12:57:27 GMT

[quote][b]FZelle (6/10/2009)[/b][hr]Most people don't know that for every command send ADO.NET needs an open Transaction.If there is none, it will open one and Commit or Rollback depending on the result.So if you open a Transaction for those mass updates yourself, you save a lot of time.So instead of using 3.000.000 commands and therefor 3.000.000 transactions just use one command,and an explicit Transaction.This alone cuts down the time needed in this experiment to about 25%.[/quote]Hello FZelle. I am sorry it has taken so long to respond to this but shortly after this post we had our first child and many things got lost in the mix.Thank you for pointing out the explicit transaction. Yes, I had not thought of that and I suspect that you are correct in that most people do not try this method. It is great to see such a simple update gain so much efficiency.In contrasting that with the method I am showing in this article, I see a benefit in the new streaming method in that it only takes less than 30 seconds as opposed to the 8 or so minutes that it would take assuming 25% of the original time it took. In certain situations to do the explicit transaction will lock the table for longer than is acceptable. In my current company we have a table that is updated in this method (many calls to a single row insert / update) with millions of rows and while doing the explicit transaction would reduce that time greatly, it would also lock the table which is not acceptable given that several processes need to write to the table at about the same time and cannot wait for 8 minutes (assuming lock escalation occurs). So there are situations where the explicit transaction would indeed reduce time but still not be a workable solution. However, as I said, this is still great information to know and helps a lot for people still on SQL Server 2005 who cannot do the streaming method.Lastly, to Bruce: Yes, the intention was to point out a new solution. I hope that I did not convey that this is the only solution but certainly one that should be considered.Take care,Solomon...

RE: Streaming Data Into SQL Server 2008 From an Application

Solomon Rutzky — Wed, 01 Jul 2009 09:42:04 GMT

[quote][b]Florian Reischl (7/1/2009)[/b][hr]@Solomon RutzkyJust want let you know, your article is referenced in current German SQL PASS newsletter! ;-)[/quote]Sweet! Danke for sharing :-).Zai Gezundt.

RE: Streaming Data Into SQL Server 2008 From an Application

Florian Reischl — Wed, 01 Jul 2009 06:18:19 GMT

@Solomon RutzkyJust want let you know, your article is referenced in current German SQL PASS newsletter! ;-)

RE: Streaming Data Into SQL Server 2008 From an Application

Bruce W Cassidy — Thu, 11 Jun 2009 20:27:56 GMT

[font="Verdana"]Interesting start to a good topic.I'm not sure I agree with the comment in the article that the "only way" to do things in the past has been with stored procedures that only take one row. It's been possible to pass XML to a stored procedure for some time now. I see someone has put up some code in the discussion to do precisely that. I believe that typed XML has some performance gains, so that might be a worthwhile comparison.If your application must "stream" data to SQL Server, have you considered using a queue of some sort (either SQL Server Broker or MSMQ for example.) The overhead for using queues is not inconsiderable, but in some cases it may well be a better approach to having to stream the data.Anyway, the point being that this is a [b]new[/b] solution (and an excellent one) in SQL Server 2008, but by no means the [b]only[/b] solution to the issue of having to pass in multiple rows of typed data to SQL Server.[/font]

RE: Streaming Data Into SQL Server 2008 From an Application

FZelle — Wed, 10 Jun 2009 11:14:56 GMT

This is one main problem with mass data in ADO.NET.Most people don't know that for every command send ADO.NET needs an open Transaction.If there is none, it will open one and Commit or Rollback depending on the result.So if you open a Transaction for those mass updates yourself, you save a lot of time.So instead of using 3.000.000 commands and therefor 3.000.000 transactions just use one command,and an explicit Transaction.This alone cuts down the time needed in this experiment to about 25%.So change the OldSchool connection and command loop to[code] using (dbConnection = new SqlConnection("Server=.\\sqlexpress;Database=Test;Trusted_Connection=True;")) { dbConnection.Open(); SqlTransaction trans = dbConnection.BeginTransaction(); Console.WriteLine("{0} -- Calling the proc iteratively ...", DateTime.Now ); using( SqlCommand importProc = new SqlCommand("INSERT INTO dbo.ImportTest (SillyIDField, MeaninglessText) VALUES (@SillyIDField, @MeaninglessText)", dbConnection)) { SqlParameter sillyIDField = importProc.Parameters.AddWithValue("@SillyIDField", (int)tempTable.Rows[0][0]); SqlParameter meaninglessText = importProc.Parameters.AddWithValue("@MeaninglessText", (string)tempTable.Rows[0][1]); importProc.Transaction = trans; for (int rowNum = 0; rowNum < tempTable.Rows.Count; rowNum++) { sillyIDField.Value=(int)tempTable.Rows[rowNum][0]; meaninglessText.Value=(string)tempTable.Rows[rowNum][1]; importProc.ExecuteNonQuery(); } } trans.Commit(); Console.WriteLine("{0} -- Done importing the data old-school style ({1} Seconds)", DateTime.Now, elapsedSeconds); }[/code]And by the way DataRows.ItemArray is only needed if you want to get/set the Rows value as an Object[].

RE: Streaming Data Into SQL Server 2008 From an Application

Solomon Rutzky — Mon, 01 Jun 2009 08:18:54 GMT

[quote][b]john hill (5/29/2009)[/b][hr]I enjoyed reading your article. Thanks for sharing it and providing the material.I downloaded your code and tried adding an option to do an import via SQLXML parameter into a similar stored procedure.My testing showed it to be significantly slower using a small 30K line file. Here are my results(~ 250 ms vs 1250ms)[/quote]Hello John and thanks, I am glad that you liked the article.And thanks so much for doing that test and providing the results and code. I was curious as to the performance of the XML option. I am not surprised that it takes longer given that it has to parse the XML string. Also, did you pay attention to the memory usage on the system? I am sure that it took up a good amount given that it had to package up the entire XML string before sending it to the database. Again this is the beauty of the IEnumerable interface feature with TVPs starting in SQL Server 2008 in that the application server does not need to use large amounts of memory in order to send large amounts of data to the database.Take care,Solomon...

RE: Streaming Data Into SQL Server 2008 From an Application

john hill — Fri, 29 May 2009 10:37:33 GMT

I enjoyed reading your article. Thanks for sharing it and providing the material.I downloaded your code and tried adding an option to do an import via SQLXML parameter into a similar stored procedure.The xml(untyped) approach is pretty simple:CREATE PROCEDURE dbo.ImportDataXml ( @inputXml xml)ASSET NOCOUNT ONINSERT INTO dbo.ImportTest (SillyIDField, MeaninglessText)SELECT r.n.value('@id','int'), r.n.value('@value','varchar(50)')FROM @inputXml.nodes('/Root/Node') as r(n)GOMy testing showed it to be significantly slower using a small 30K line file. Here are my results(~ 250 ms vs 1250ms)[09:28 UTC-7] PS>./streamteste Full Test.txt5/29/2009 9:28:03 AM -- Truncating the import table, clearing the caches, etc...5/29/2009 9:28:03 AM -- Done setting up for the test5/29/2009 9:28:03 AM -- Connecting to the Database...5/29/2009 9:28:03 AM -- Calling proc to read and import the data...5/29/2009 9:28:03 AM -- Proc is done reading and importing the data via Streamed TVP (0.241 Seconds))5/29/2009 9:28:03 AM -- Done![09:28 UTC-7] PS>./streamteste xml Test.txt5/29/2009 9:28:08 AM -- Truncating the import table, clearing the caches, etc...5/29/2009 9:28:08 AM -- Done setting up for the test5/29/2009 9:28:08 AM -- Connecting to the Database...5/29/2009 9:28:08 AM -- Calling proc to Xml import the data...5/29/2009 9:28:09 AM -- Proc is done importing the data via single Xml (1.296 Seconds)5/29/2009 9:28:09 AM -- Done!I attached my VS project and simple (testing) code.Thanks,John

RE: Streaming Data Into SQL Server 2008 From an Application

Solomon Rutzky — Thu, 28 May 2009 23:05:36 GMT

LP:Correct: I had not thought of the XML solution and so that would be an incorrect statement that I made about single inserts being the “only” way to load data. I will see about updating the article to be more accurate in that regards. Thanks for pointing that out. Really.[hr]Brian:Regarding being able to do this in PHP. I have not had time to check out the PHP or Mono (for *NIX) documentation, but if it supports enough basic .Net functionality, then it is possible since this does not need to be done via a SQL Server specific library. I used the ADO.Net method but from what I see it can be via ODBC using “data-at-execution” (whatever that is) or via OLE DB using a custom IRowset implementation.[hr]Florian:Thanks. And yes, I will try the single instance of the variable. I could have sworn I started with that and got an error but maybe I am misremembering. I will try it again.[hr]Daniel and Oleg :Thanks for pointing out the misusage of the “using” block. I thought that I originally had it the way that both of you recommended but had someone tell me to change it to what is currently shown. It is possible that I misunderstood that person. In either case I did miss it on the SqlCommand so thanks also, Oleg, for pointing that out.[hr]Don:I am not sure that I understand. It seems that extracting, FTPing, BCPing, Transforming, and finally loading into the destination seems a bit more complex than simply reading the data from a local source, transforming each row as necessary, and sending it to the server. This is fewer steps, fewer processes, very little memory usage, and a single connection over the network to the DB as opposed to the two steps in your five step process (FTP and BCP). So in using FTP the disk on the SQL Server has to work one time to receive the data and write it locally, then twice more to read the local source and write it to a SQL Server datafile. Then the disk has to work a fourth time to move the data from the “staging” table into the final destination table. In the scenario I mentioned, assuming the same setup with two servers, the disk on the SQL Server would only work one time as it puts the data it receives from the application into the final destination table. This would be 25% the amount of disk I/O (in simplistic terms) as compared to the FTP / BCP / Transform method.[hr]Matt:1) Yes, you can transform the data before it goes into the DataTable when using SqlBulkCopy, but you still need to populate that DataTable in memory before you can send it to the destination. In doing so it is fully persisted in memory whereas in the solution that I am giving an example of, only a single row at a time is in memory.2) I am not advocating that this feature alone is a “compelling” reason to upgrade to SQL Server 2008. If you work somewhere that does not have it or produces software that needs to work on various versions of various databases then by all means do what will work in most / all situations. BUT, what I AM saying is that if you already have SQL Server 2008 in-house then why not use a feature that is available to be used? That would not incur any more cost.

RE: Streaming Data Into SQL Server 2008 From an Application

Oleg Netchaev — Thu, 28 May 2009 11:10:08 GMT

This is a great article, Solomon. Thank you.I do have some comments about the C# code. As daniel.janz already pointed out,[quote]If you wrap a object into a using scope it will automatically be Disposed (and therefore closed) if an exception is thrown. The call to the Close() method in your finally block will then cause another excetion because its called on a already disposed object.[/quote]I can add another point because it is amazing how many people consistently do it incorrectly. The SqlCommand inherits from DbCommand which in turn implements IDisposable interface. This means that a variable of SqlCommand reference type must be disposed, for example by enclosing it in the using block. The type does not implement Close() so wrapping it into using block will suffice. The using blocks exist entirely for our convenience, the compiler does not have a concept of using block and simply restates our code to try/finally whenever it finds using {}. It is a matter of pure luck based on the simple fact that the Microsoft's wrappers under SqlCommand are written so well that we can get away with forgetting to dispose it. If you needed to use OleDb connecting to something like Sybase (yes, I used the "S" word, sorry) then you will see how quickly the code would come to a screeching halt, so bad that the connection timeout related messages from the database engine will not even make it back to the C# code, which in turn will happily seat on the same line for days if you wish until someone will kill the process.The implementation of the using blocks in your sample code needs to be adjusted in all places related to both connection and stream reader. When the IDisposable type variable goes out of using scope, the framework calls its respective Dispose() method, which will attempt to call the Close() if present and then delegate the work to the protected Dispose(bool disposing) where disposing will be passed as true. This is by design is because some people opt to use the using blocks, some prefer to explicitly call Close() and then Dispose() and finally, there are people who unfortunately do nothing in which case the protected Dispose(bool disposing) will be called from destructor passing false as a disposing parameter though.In other words, if you remove your finally blocks, add respective calls to Close() methods, and wrap the SqlCommands into using blocks the code will be fine. The article is great, it is good to learn about alternative to bulk methods.Oleg

RE: Streaming Data Into SQL Server 2008 From an Application

Matt Whitfield — Thu, 28 May 2009 10:47:02 GMT

[quote][b]Solomon Rutzky (5/28/2009)[/b][hr]1) Using the IEnumerable streaming method that I described here provides the greatest benefit when doing transformations in the application layer. I realize that my example code was simplistic and did not show a transformation but this technique is more focused on the "sending" of the data than the "receiving" of it. And in these cases, bulk insert/copy operations do not address the issue of doing anything more than straight reads of the source data.[/quote]That's not strictly true - you can do whatever transformation you like on the data that you give to SqlBulkCopy - you don't point it at source data, you give it data through a dataset - which you are able to transform before you put it in there...[quote][b]Solomon Rutzky (5/28/2009)[/b][hr]2) As far as using TVPs in general goes, regardless of the full-streaming feature when using IEnumerable they do, in my opinion, provide a much cleaner interface than using a staging or loading table. True, using them is not compatible with prior versions of SQL Server but many people do not need such compatibility. Unless there is a specific reason not to do so I believe that you should use all features available in a product. I have mainly worked at Software as a Service companies where we host the application and do not need to worry about what version of the DB (or even what vendor) a customer might have.[/quote]Well, I think I just disagree there. If something is compatible, it opens up flexibility. If i tell my boss 'well this hardware is perfectly fine but you need to use x thousand pounds to upgrade your license because i've chosen this implementation route' then that usually doesn't go down too well, in my experience... Personally, having a slightly cleaner interface wouldn't want to make me go and tell my boss that! :-)

RE: Streaming Data Into SQL Server 2008 From an Application

Florian Reischl — Thu, 28 May 2009 08:33:19 GMT

I think the main difference between "BCP/BULK INSERT" and TVPs is the context to use it.Since BCP requires special privileges it's not a feature for front-end applications. TVPs can be used by usual import processes or even GUIs which create larger counts of objects.Flo

RE: Streaming Data Into SQL Server 2008 From an Application

don_goodman — Thu, 28 May 2009 08:23:07 GMT

The TVP feature is an improvement, yes. BUT you wrote dozens and dozens of lines of code that were only precompiled. The better path was is at the end of your article, "Large datasets that are inserted directly (i.e. not transformed) will likely be faster via T-SQL BULK INSERT, bcp.exe..."Here's the universally easy way to transform data1) dump it to ASCII text on the main frame, DB2, universe, oracle, informix or mySQL; parse the attributes with a character like tab or comma or whatever; end the lines with CRLF to be fully compatible with Windows.2) FTP pull/push the file to a disk on the SQL Server3) BULK INSERT or BCP the text into a staging table or a memory table like TVP (watch out for your tempdb if memory table)4) Transform the data in the staging table or the memory table5) dump the result into the destinationThis is the only sensible way to get data over a busy network into a SQL Server database. The rest just run slower and are buggier.

RE: Streaming Data Into SQL Server 2008 From an Application

daniel.janz — Thu, 28 May 2009 08:21:54 GMT

[quote]What if it's LINUX or a variation thereof hence no windows dll's...[/quote]You could try it with Mono but i'm not sure if it will work because of the referenced Microsoft.SqlServer-Assembly which might use PInvoke to native Windows-Specific DLLs.[EDIT]@Solomon: I think in the following part of the code is a small error which could cause an Exception:[code] try { using (dbConnection = new SqlConnection("Server=localhost;Database=Test;Trusted_Connection=True;")) { dbConnection.Open(); SqlCommand importProc = new SqlCommand("CHECKPOINT ; TRUNCATE TABLE dbo.ImportTest ; DBCC FREEPROCCACHE ; DBCC FREESYSTEMCACHE('ALL') ; CHECKPOINT ;", dbConnection); importProc.CommandType = CommandType.Text; Console.WriteLine("{0} -- Truncating the import table, clearing the caches, etc...", DateTime.Now); importProc.ExecuteNonQuery(); Console.WriteLine("{0} -- Done setting up for the test", DateTime.Now); } } catch (Exception exception) { Console.WriteLine("Error: {0}", exception.Message); } finally { dbConnection.Close(); }[/code]If you wrap a object into a using scope it will automatically be Disposed (and therefore closed) if an exception is thrown. The call to the Close() method in your finally block will then cause another excetion because its called on a already disposed object.

RE: Streaming Data Into SQL Server 2008 From an Application

Florian Reischl — Thu, 28 May 2009 08:12:10 GMT

Great job, Solomon!That's definitely a feature which was missing in my opinion.I think BCP is a very powerful and nice tool but it is much less flexible. XML is a way I use since now but it produces a large overhead. Anyway, the performance of XML vs. TVPs would be really interesting :-).Some little things (maybe optimizations) about your C# code:General )Since only the "Normal" approach requires a DataTable all other methods could be optimized by using a simple object[] and a List<> .OldSchoolImport )Well, creating 3,000,000 instances of a SqlCommand object instead of creating just one, prepare it and just bind the new values should be more realistic usage ;-)Thanks for this workFlo

RE: Streaming Data Into SQL Server 2008 From an Application

brian.neumeier-603907 — Thu, 28 May 2009 08:11:25 GMT

Looking at the site you mentioned that would make me think it would have to be PHP on a windows IIS Machine... What if it's LINUX or a variation thereof hence no windows dll's... Will this table call method, still be available to PHP??

RE: Streaming Data Into SQL Server 2008 From an Application

daniel.janz — Thu, 28 May 2009 08:05:47 GMT

[quote]Also, will the table approach work with other languages than .net like php, etc...[/quote]This Approach will most likely also work with PHP (Have a look at: http://php.benscom.com/manual/en/class.dotnet.php). .Net is a runtime environment which supports various languages and not a language itself.

RE: Streaming Data Into SQL Server 2008 From an Application

brian.neumeier-603907 — Thu, 28 May 2009 07:28:11 GMT

I do the XML thing also to the stored procedure... But I am curious to know if there is any performance gain by using the table approach vs the xml approach... Also, will the table approach work with other languages than .net like php, etc...

RE: Streaming Data Into SQL Server 2008 From an Application

LP-181697 — Thu, 28 May 2009 07:23:46 GMT

From article: - "Prior to SQL Server 2008 the only way to send data to the database was to send it in one row at a time"Really misleading statement. You can send XML with bunch of rows and translate it into a table inside of stored procedure. I were doing it for years. But still, good article.LP.

RE: Streaming Data Into SQL Server 2008 From an Application

Solomon Rutzky — Thu, 28 May 2009 07:19:29 GMT

Hello "dm_unseen" and thanks for that suggestion.[quote][b]Matt Whitfield (5/28/2009)[/b][hr]I don't really see the major benefit over using the SqlBulkCopy class here, which is also backwards compatible 2000 up.A minor benefit that I can see is that you don't need to have a 'loading table' in which data is placed before being transformed by a stored proc if that transformation is necessary.But it's a really minor benefit to break compatibility from 2000/2005.[/quote]Hello Matt. Regarding the similarity to BULK INSERT, SqlBulkCopy, and other methods, I would say that:1) Using the IEnumerable streaming method that I described here provides the greatest benefit when doing transformations in the application layer. I realize that my example code was simplistic and did not show a transformation but this technique is more focused on the "sending" of the data than the "receiving" of it. And in these cases, bulk insert/copy operations do not address the issue of doing anything more than straight reads of the source data.2) As far as using TVPs in general goes, regardless of the full-streaming feature when using IEnumerable they do, in my opinion, provide a much cleaner interface than using a staging or loading table. True, using them is not compatible with prior versions of SQL Server but many people do not need such compatibility. Unless there is a specific reason not to do so I believe that you should use all features available in a product. I have mainly worked at Software as a Service companies where we host the application and do not need to worry about what version of the DB (or even what vendor) a customer might have.Take care,Solomon...

RE: Streaming Data Into SQL Server 2008 From an Application

Matt Whitfield — Thu, 28 May 2009 03:25:31 GMT

I don't really see the major benefit over using the SqlBulkCopy class here, which is also backwards compatible 2000 up.A minor benefit that I can see is that you don't need to have a 'loading table' in which data is placed before being transformed by a stored proc if that transformation is necessary.But it's a really minor benefit to break compatibility from 2000/2005.

RE: Streaming Data Into SQL Server 2008 From an Application

DM Unseen — Thu, 28 May 2009 02:14:14 GMT

Nice topic Solomon.I have some suggestion on the log growth:in the sp: you could use select into, or enable minimal logged inserts on SQL server 2008, see http://msdn.microsoft.com/en-us/library/dd425070.aspx to get minimal logged inserts and reduce log growth. When you do this comparing the TVP streaming to bulk import methods becomes fairer, because they always are minimal logged.

Streaming Data Into SQL Server 2008 From an Application

Solomon Rutzky — Tue, 28 Apr 2009 02:43:52 GMT

Comments posted to this topic are about the item [B]Streaming Data Into SQL Server 2008 From an Application[/B]