john hill (5/29/2009)
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)
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...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR