Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Writing to a File Using the sp_OACreate Stored Procedure and OSQL: The Expand / Collapse
Author
Message
Posted Tuesday, February 7, 2006 9:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 2, 2012 9:31 AM
Points: 65, Visits: 46

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.





--------------------------
Zach

Odds_And_Ends Blog
Post #256463
Posted Tuesday, February 7, 2006 9:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 13, 2011 6:04 AM
Points: 37, Visits: 323
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.
Post #256472
Posted Tuesday, February 7, 2006 10:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 2, 2012 9:31 AM
Points: 65, Visits: 46
Great!  Thanks Charles I'll take a look.



--------------------------
Zach

Odds_And_Ends Blog
Post #256476
Posted Tuesday, February 7, 2006 12:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 22, 2010 8:59 AM
Points: 110, Visits: 952
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.
Post #256533
Posted Thursday, November 9, 2006 11:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 29, 2007 1:04 AM
Points: 38, Visits: 1

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.




Post #321864
Posted Friday, November 10, 2006 1:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 1:58 AM
Points: 1,150, Visits: 1,088

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.

Post #321874
Posted Friday, November 10, 2006 5:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:15 AM
Points: 55, Visits: 232
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.
Post #321919
Posted Friday, November 10, 2006 6:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:53 AM
Points: 246, Visits: 382

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?

Post #321925
Posted Friday, November 10, 2006 7:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 8, 2014 1:40 PM
Points: 925, Visits: 999

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




Post #321961
Posted Friday, November 10, 2006 8:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 2, 2012 9:31 AM
Points: 65, Visits: 46

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





--------------------------
Zach

Odds_And_Ends Blog
Post #321975
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse