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 Friday, November 10, 2006 8:17 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

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...

  1. A single Parent table A record and...
  2. 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





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

Odds_And_Ends Blog
Post #321978
Posted Friday, November 10, 2006 11:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 19, 2014 11:36 AM
Points: 244, Visits: 378

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.

Post #322053
Posted Friday, November 10, 2006 8:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, March 17, 2012 8:26 PM
Points: 2, Visits: 12
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 Ramos
Lead Program Manager
SQL Server Manageability
Post #322126
Posted Monday, November 13, 2006 9:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 10, 2007 8:51 AM
Points: 2, Visits: 1

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.

Post #322357
Posted Monday, November 13, 2006 4:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202

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)...



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #322453
Posted Monday, November 13, 2006 6:43 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 4:10 PM
Points: 241, Visits: 74

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?




Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

Post #322467
Posted Monday, November 13, 2006 7:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
Heh... let's hear it for progress... pay more, get less.  Gotta love that

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #322471
Posted Wednesday, November 15, 2006 5:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 19, 2014 11:36 AM
Points: 244, Visits: 378

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:

 

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

 

  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.

 

  1. 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.

 

Post #322989
Posted Wednesday, November 15, 2006 5:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 19, 2014 11:36 AM
Points: 244, Visits: 378
Additional note: The 446,000 documents totaled 274MB in size.
Post #322990
Posted Wednesday, November 15, 2006 9:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 11, 2014 8:12 AM
Points: 55, Visits: 228

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.

Post #323143
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse