SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Zach Nichter
Zach Nichter
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 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
Charles Barnhart-229428
Charles Barnhart-229428
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 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.
Zach Nichter
Zach Nichter
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 46
Great! Thanks Charles I'll take a look.



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

Odds_And_Ends Blog
Mike Dougherty
Mike Dougherty
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 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.
Per-Johan@oermen.dk
Per-Johan@oermen.dk
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
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.





Michael Lysons
Michael Lysons
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1755 Visits: 1427

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.


SQLGuy64
SQLGuy64
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 292
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.
Todd Townley
Todd Townley
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 478

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?


DavidSimpson
DavidSimpson
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1276 Visits: 1081

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





Zach Nichter
Zach Nichter
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search