SQLServerCentral / Discuss Content Posted by Zach Nichter / Article Discussions / Article Discussions by Author / Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The / Latest Posts

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Jeff Moden — Sat, 20 Oct 2007 19:02:56 GMT

Actually, just had cause to revisit this particular thread...... and it just dawned on me that tests in the article are [i]not [/i]measuring the performance of each method... the tests are measuring how well each method runs a loop which has nothing to do with transferring data to a file. If you want a real test, build a table with desired number of rows (100k for example) and then simply select from that table using each method. The results will be drastically different.Here's how to build a 100k row table of numbers in an eye-blink...[code] SELECT TOP 100000 IDENTITY(INT,1,1) AS N INTO dbo.Numbers FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2[/code]And, yeah... where is the test for BCP?

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Andy Brons — Thu, 16 Nov 2006 05:21:00 GMT

I know. weird.

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

BeerBeerBeer — Wed, 15 Nov 2006 19:40:00 GMT

255 is usually a significant number in this industry of ours. I wonder where that fits into the issue you just mentioned.

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Andy Brons — Wed, 15 Nov 2006 09:52:00 GMT

I modified a stored procedure that moved DTS packages from one server to another. It is from http://www.dbazine.com/sql/sql-articles/larsen8

It would stop after moving 255 packages, everytime, no mateter how the packages were sorted.

It would always stop at sp_OAMethod @object, 'LoadFromSQLServer'. Make of it what you will. Smells like a memory leak.

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Todd Townley — Wed, 15 Nov 2006 05:38:00 GMT

Additional note: The 446,000 documents totaled 274MB in size.

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Todd Townley — Wed, 15 Nov 2006 05:32:00 GMT

11/13/2006

I have discovered the problem with my text file generation degradation.

It all comes back to the file system. As files are being generated and more and more files are generated into one folder, the subsequent file generates get slower. By moving to a new folder, the file generates pick right up.

In my final test scenario I did the following:

Using sp_OACreate, sp_OAMethod, and sp_OADestroy, I generated 75000 text files into test folder 1

Using sp_OACreate, sp_OAMethod, and sp_OADestroy, I generated 75000 text files into test folder 2, sub folder K, where K was incremented every 10000 files.

Using sp_OACreate, sp_OAMethod, and sp_OADestroy, I generated 75000 text files into test folder 3

Step 1 ran in 25 minutes with the file generation degrading from 16800/min down to 600/min.

Step 2 ran in 5 minutes with no noticeable degradation, averaging about 17000/min.

Step 3 ran in 24 minutes with the file generation degrading from 18000/min down to 350/min.

So I will include multiple subfolders in my file generation process.

11/15/2005

I modified my process to include subfolders and ran a partial test. 446,000 documents were generated in 68 minutes, giving an average rate of over 6500 documents generated per minute.

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Jeff Moden — Mon, 13 Nov 2006 19:00:00 GMT

Heh... let's hear it for progress... pay more, get less. Gotta love that

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

BeerBeerBeer — Mon, 13 Nov 2006 18:43:00 GMT

I just tried the first osql cmd you wrote in SQL 2005 and replaced osql with sqlcmd

osql command runs in 7 seconds

sqlcmd runs in 2 minutes 26

SQL 2005 docs say that sqlcmd is the replacement for osql. Can this be right? Obviously they are structured very differently, but wouldn't you expect that sontinuity of performance might be desirable?

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Jeff Moden — Mon, 13 Nov 2006 16:32:00 GMT

Set @append = '>>' -- to create a file

Set @append = '>' -- to append a file

I'm thining the above are backwards... >> is the append symbol and > is the create symbol in DOS (Cmd Window)...

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Will Sperry — Mon, 13 Nov 2006 09:26:00 GMT

Very good summary of methods, but you forgot the oldest one in the book. We routinely use CMD calls to write files, but surprisingly it's just as fast to write using the ECHO command to write any amount of data necessary. Something like:

Declare @cmd varchar(255), Declare @data varchar(1000), Declare @append varchar(2), Declare @path varchar(255), Declare @filename varchar(255), Declare @output varchar(2000)

Set @cmd = 'echo'

Set @data = 'some sort of data'

Set @append = '>>' -- to create a file

Set @append = '>' -- to append a file

Set @path = '\\someservershare\share' or 'c:\localpath\localdir'

Set @filename = 'somefilename'

Set @output = @cmd + ' ' + @data + ' ' + @append + ' ' + @path + @filename

EXEC master..xp_cmdshell @output

There are other techniques to just create an empty file by using the @echo command, and dozens of other things you can do with echo. Plus, if you place the whole thing in params in a Store Procedure you can pass in other commands and do most any DOS command you want. Of course be sure the security is set correctly so it can't be abused. This method outputs about a 1.5MB text file in about 30 seconds.

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Bill Ramos — Fri, 10 Nov 2006 20:16:00 GMT

Hi Zach, It's worth pointing out to your readers that using OLE Automation in T-SQL not only has performance issues, but has real drawbacks when it comes to security. For example, you need to be a member of the sysadmin role to execute these SPs. Bart Duncan wrote a good paper on the topic at ttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_10182004.asp. In addition, it is very easy to cause memory leaks in the server that could impact the server's memory space.Thank you,Bill RamosLead Program ManagerSQL Server Manageability

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Todd Townley — Fri, 10 Nov 2006 11:24:00 GMT

Zach, you have this correct.

To flesh out the scenario a bit, this is a conversion process from one application to another involving as much as 10-15 years worth of data. In the original application, every time a real-life event happened (which could be tens or hundreds of time per day) a report (or narrative) was written. These narratives vary in from one 70-character line to as many as 1000 70-character lines.

The database in the new application is designed differently, so these old narratives are converted to text documents and then “attached” to the converted event in the new application. This allows users to see all the old information, especially when the old info doesn’t fit well into the new database.

This conversion process will happen when the customer goes live on the new application. They will stop the old system, the data will be converted, and the customer will go live on the new system that now contains their converted data. The text documents don’t have to be generated exactly within that window, but will come very shortly thereafter. So we are attempting to create these documents as quickly as possible.

For the particular customer I am currently helping, they have roughly 3 million events for which the narratives have to be converted to text files.

Any thoughts/suggestions are welcome.

I agree that this sounds like some sort of memory-pressure issue. I'm working to diagnose 1) exactly what the cause is, and 2) how I can fix it/work around it.

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Zach Nichter — Fri, 10 Nov 2006 08:17:00 GMT

Todd, let me make sure that I understand your senario before I post an answer. Am I right in saying that you have Parent table A which can or does have multiple Child table B records. Each Parent table A record requires that a single file be created to contain...

A single Parent table A record and...
Multiple Child table B records for the Parent A record.

Are you saying there are around 3 million of these parent records?

I think that the slowdown in your file creation rate "sounds" like mem-to-leave or other memory pressure.

Can you correct or confirm my summation of the issue before we go on?

Thanks,Zach

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Zach Nichter — Fri, 10 Nov 2006 08:07:00 GMT

I am really glad that a the conversation on this topic is continuing and that people are pointing out differences in results, and yes my typo's too.

Looking back at when I wrote this article, it was my first, I realize that I had a limited understanding of the sp_OA procedures and also that I didn't know how to use them appropriately.

I guess I started out just sharing what I was doing then I tried to prove myself right. If I had it to do over again I would have looked for better options (trust me there are better meathods) than the ones that I came up with and let the tests prove themselves out.

There have been a lot of good suggestions and finding on the comments of this article and I'm glad to see that the discussions continue. More than anything, I'm glad that my conclusions didnt finalize anything but people shared their ideas and findings with the community too.

-Zach

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

DavidSimpson — Fri, 10 Nov 2006 07:38:00 GMT

Thanks for sharing your research on this. Just wanted to point out one mistype... In your summary and graph you list test 2(OSQL - From Query Analyzer) at 32.6 seconds but in your notes you list it at 5.86 seconds.

David

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Todd Townley — Fri, 10 Nov 2006 06:13:00 GMT

Be very careful with how you interpret the article’s conclusions!

Real world scenario: I am currently having to take narrative information stored in a SQL Server 2000 db and convert it into a text document. The narratives are stored with a header record and detail records (one line of narrative per detail record, many detail records per header record). I have to use the header record to create a report title, then add the narrative.

To accomplish this (in general), I create a title from the header record and store it to a new text file. I then use a query to collect the detail information and append it to the text file.

I have to generate roughly 3 million text files this way.

Using the sp_OACreate (and related) procedures, I start out generating files at the rate of 5000-6000 per minute, with spikes as high as 8000/minute. This quickly degrades so that 30 minutes later I’m down to about 1100/minute, and 30 minutes after that I’m down to 600/min. Eventually I get to the point where I’m only generating about one file per second.

Using OSQL, the results are more consistent but far less dramatic. I plod along at about 300/minute. To generate 3 million documents at this rate is going to take a week!

Any suggestions or thoughts?

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Andy Brons — Fri, 10 Nov 2006 05:48:00 GMT

On our system we get out of memory errors when we use code that utilizes sp_OACreate and other associated extended stored procedures. The MemToLeave seems to get fractured and the fix is stopping and starting SQL Server to reallocate the virtual memory.

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Michael Lysons — Fri, 10 Nov 2006 01:44:00 GMT

I'm glad there're people like you to do this stuff for us - nice article

A comment about the VB code. I think it's un-necessary to have the If statement to determine what to write to the file. As with the other approaches, just write the Start Time, then iterate through the loop, then write the End Time. I'm not sure what sort of performance difference you'd see, but I think it would be a fairer test.

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Per-Johan@oermen.dk — Thu, 09 Nov 2006 23:39:00 GMT

From experience: The print command from T-sql has degrading performance as the file grows.

Optimal solution may be dependant upon filesize of written file.

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Mike Dougherty — Tue, 07 Feb 2006 12:56:00 GMT

OK sorry, I was not intending to be truculent.It seemed to me that you were writing as an authority, who would have had an editor review your submission for completeness. Had I understood that you were sharing the process of discovering a significant performance difference between two methods (regardless of other options) - I would have taken a much different tone in my comment.Thanks for pointing out that sqlservercentral.com articles are community-driven. What I have read to date made me believe they're authored by paid writers - so in that vein, keep up the good work.

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Zach Nichter — Tue, 07 Feb 2006 10:00:00 GMT

Great! Thanks Charles I'll take a look.

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Charles Barnhart-229428 — Tue, 07 Feb 2006 09:52:00 GMT

Yet another approach is to write to a table, then use sp_OA... stored procedure to create a BulkCopy object and "export" the table to a file. I got this method from K Henderson's Guru's Guide to SQL Server Stored Procedures... He includes a procedure, sp_ExportTable that performs this operation very quickly (using speed of BulkCopy) and avoids xp_cmdshell altogether. I am curious how its speed compares--might have to test it.

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Zach Nichter — Tue, 07 Feb 2006 09:28:00 GMT

Rick you are right about Test5 (see below for Test6 comments) the (open-write-close) does contribute to all of the additional overhead there, and it really isn't a valid comparison to all of the other tests. It was added because it was part of the learning process with writing to multiple files for multiple collects and then trying to wrap that in an over all waitfor.

Test6 though does open once, write 100,000 times then close and still the duration was 67 sec, so I think this is a valid comparison.

Let me know if you disagree, I'm always up for learning more.

Thanks for the comment.

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Rick Harker — Tue, 07 Feb 2006 08:51:00 GMT

I believe this was an unfair comparison against sp_OACreate.

Test2, Test3, and Test4 all open the file first, then write the 100,000 lines, then close the file.

Test5 and Test6 100,000 times does this: creates the object, opens the file, writes a line, and then destroys the object (which I am assuming close the file handle?).

If the same flow (open-write-close) was done on the other files 100k times, I am sure they would seem slower also. A better comparison of sp_OACreate would be to create, open, write 100k, close, then destroy.

But then again it feels like Monday morning... Am I missing something about these tests?

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Zach Nichter — Tue, 07 Feb 2006 07:52:00 GMT

Mike, thanks for the responce. The goal of the article was more to identify the performance factor involved in the use of the sp_oa extended procs and using it and other meathods through Query analyzer to identify the overhead associated to them. But you are right though, bcp is a much better option, and as for DTS, it really didn't fit the need of the situation that I was in. Maybe you should consider contributing to the community yourself sometime, it might make you a little less grumpy...

Oh and thanks for the comments Charles.

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Charles Barnhart-229428 — Tue, 07 Feb 2006 06:45:00 GMT

I agree that it is too bad that you did not include bcp and DTS in your analysis. For a simple write to file, though, DTS seems hardly appropriate. And when you're trying avoid the dreaded xp_cmdshell in a user application, then the cost xp_OACreate is much more tolerable. However, I never realized how big of a cost xp_OACreate was. Thanks for the article!

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Mike Dougherty — Tue, 07 Feb 2006 06:14:00 GMT

Where was the example using bcp or DTS?If you're going to use a hammer to drive a screw, be thorough enough to examine the rest of the non-screwdriver solutions.

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Zach Nichter — Tue, 08 Feb 2005 08:40:00 GMT

Thanks for all of the responces on the article. I'll have to look into the schema.ini option I'll let you know if I find anything out on it.I wish I knew Perl, I've been wanting to pick up book on it for some time but have never gotten around to it.Thanks Again,Zach

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Jeff Moden — Mon, 07 Feb 2005 22:18:00 GMT

Very Nice article. Covered a lot of ground and it's a pleasure to actually see commented formatted code. I've seen a method to write to text files using linked servers through the use of a file called Schema.ini (I think that's what it's called). Sure would be nice to see a follow-up article on that.

Like someone said before me, "Thanks for sharing your research!"

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

JT Lovell — Mon, 07 Feb 2005 10:31:00 GMT

Nice article, thanks for sharing your research!

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Tatsu — Mon, 07 Feb 2005 06:41:00 GMT

I would generally pump the performance data to a table. This allows a lot more flexibility in the reporting. If you need some kind of report you can use a reporting package or even just DTS it out to a text file.

Your article had an excellent conclusion. Based on it's title, I really expected a pro-sp_OAx article but was very pleasantly suprised. It's great to see someone taking time out to make decisions based on solid data and I think this sets an excellent example for the novices that come here to learn.

Kudos!

RE: Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Rich James — Mon, 07 Feb 2005 03:51:00 GMT

Excellent article.It's always useful to know just how the server performs under different situations.I tend to use Perl with a pipe from OSQL to access data exported from the SQL server (which allows filtering/formatting on the fly if necessary) as a default method of handling data output to a file.There have been times when I've been tempted to keep the file writing on the server side, to aid reliability of write for certain actions, but this article will definitely make me think twice before using the SP methods for file creation.Thanks for a good read!

Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The

Zach Nichter — Thu, 20 Jan 2005 12:40:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/zNichter/writingtoafileusingthesp_oacreatestoredprocedurean.asp