Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

By Zach Nichter, (first published: 2005/02/07)

Recently I had an engagement where I needed to collect DBCC SQLPERF data at 15 second intervals and wanted to write the results to a file. I’ve always used OSQL for this type of thing but wanted to see if there was another way so I searched the Internet to see if DBA’s were using any different methods to accomplish this task. In my search I found some articles relating to the sp_OACreate and the sp_OAMethod stored procedures and thought I would see if using these stored procedures would be better for performance than my old method.

The tests below were used to gauge performance for the sp_OACreate stored procedures versus the osql.exe application.

Test 1: Query Analyzer Baseline

This T-SQL script will print @i 100,000 times. This script was used to get a duration baseline for the same statements below while using different options. This script took 37 seconds to complete on my test machine.

DECLARE @i INT
SET @i = 1

WHILE @i <= 100000
 BEGIN
  PRINT @i
  SET @i = @i + 1
 END

Test 2: OSQL – From Query Analyzer

This T-SQL script uses xp_cmdshell to utilize osql.exe to print @i 100,000 times. This script took only 5.86 seconds to complete. It looks like the additional overhead of returning results for display in Query Analyzer cost me 32.6 seconds.

DECLARE @cmd VARCHAR(255)

SET @cmd = 'osql -E /Q "SET NOCOUNT ON SELECT GETDATE() DECLARE @i INT SET @i = 1 WHILE @i <= 100000
            BEGIN PRINT @i SET @i = @i + 1 END SELECT GETDATE()" /o c:\test2.csv' 
EXEC xp_cmdshell @cmd

Test 3: OSQL – From a Command Prompt

This script uses osql.exe and is executed from a command prompt to print @i 100,000 times and save the output to a csv file. This script only took 5.69 seconds to complete. It seems as though the additional overhead for running the xp_cmdshell from T-SQL is very small.

osql -E /Q "SET NOCOUNT ON SELECT GETDATE() DECLARE @i INT SET @i = 1
           WHILE @i <= 100000 BEGIN PRINT @i SET @i = @i + 1 END 
			  SELECT GETDATE()" /o c:\test3.csv

Test 4: VB Script

This script is a VB script that creates a COM object, creates a csv file and loops through “i” for 100,000 iterations and appends the writeline for “i” to the end of the file for each iteration. This script creates a file COM object and destroys it only once for the duration of the script but is executed from the OS and not T-SQL. This script completed in 5.8 seconds which is about as quick as the osql.exe command.

Dim objFS
Dim objText
Dim i

i = 1
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objText = objFS.OpenTextFile("c:\test4.csv", 8, True)

For i = 1 To 100000
    If i = 1 Then
      objText.Writeline("Start:  " & Date & " " & Time())
      objText.Writeline(i)
    ElseIf i = 100000 Then
      objText.Writeline(i)
      objText.Writeline("End:  " & Date & " " & Time())
    Else
      objText.Writeline(i)
    End If
Next

objText.Close()

Test 5: sp_OA – From Query Analyzer

This script uses the sp_OA stored procedures and a user stored procedure to create a COM object and destroy it for each @i value for 100,000 iterations. This approach took 487 seconds for this script to complete (obviously not a likely option for production systems).

Code for the usp_UseOA Stored Procedure

IF NOT EXISTS (SELECT (1) FROM master..sysdatabases WHERE name = 'SE')
	BEGIN
		CREATE DATABASE SE
	END
GO
USE SE
GO

IF EXISTS (SELECT (1) FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[usp_UseOA]')
				AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	BEGIN
		DROP PROCEDURE [dbo].[usp_UseOA]
	END
GO
CREATE PROCEDURE usp_UseOA (
   @File varchar(1000)
 , @Str varchar(1000)
) 
AS
DECLARE @FS int
        , @OLEResult int
        , @FileID int

EXECUTE @OLEResult = sp_OACreate 
 'Scripting.FileSystemObject'
 , @FS OUT

IF @OLEResult <> 0 

	BEGIN
		PRINT
'Error: Scripting.FileSystemObject'
	END

-- Opens the file specified by the @File input parameter 
execute @OLEResult = sp_OAMethod 
   @FS
   , 'OpenTextFile'
	, @FileID OUT
	, @File
	, 8
	, 1
-- Prints error if non 0 return code during sp_OAMethod OpenTextFile execution 
IF @OLEResult <> 0 
	BEGIN
		PRINT 'Error: OpenTextFile'
	END

-- Appends the string value line to the file specified by the @File input parameter
execute @OLEResult = sp_OAMethod 
   @FileID
	, 'WriteLine'
	, Null
	, @Str
-- Prints error if non 0 return code during sp_OAMethod WriteLine execution 
IF @OLEResult <> 0 
	BEGIN
		PRINT 'Error : WriteLine'
	END

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

Execution Script

DECLARE
  @file VARCHAR(255)

, @i INT SET @i = 1 SET @file = 'c:\test5.csv' WHILE @i <= 100000 BEGIN -- executes this stored procedure for each @i value EXEC SE..usp_UseOA @file, @i SET @i = @i + 1 END

Test 6: sp_OACreate – From Query Analyzer

This script uses the sp_OA stored procedures to create a file object and destroys it once. This script completed in 67 seconds. It is apparent that the additional overhead of the sp_OACreate, sp_OAMeathod and sp_OADestroy are significant enough to cost the execution 61 seconds compared to the VB script.

DECLARE 
 @i INT
 , @File VARCHAR(1000)
 , @FS INT
 , @RC INT
 , @FileID INT
 , @Date DATETIME

SET @File = 'c:\test6.csv'
SET @i = 1
EXEC @RC = sp_OACreate 
  'Scripting.FileSystemObject'
	, @FS OUT

IF @RC <> 0 
 BEGIN
	PRINT 'Error:  Creating the file system object'
 END

-- Opens the file specified by the @File input parameter
EXEC @RC = sp_OAMethod 
 @FS
 , 'OpenTextFile'
 , @FileID OUT
 , @File
 , 8
 , 1
-- Prints error if non 0 return code during sp_OAMethod OpenTextFile execution 
IF @RC <> 0 
 BEGIN
	PRINT 'Error:  Opening the specified text file'
 END
-- Printing start time in file
SET @Date = GETDATE()
EXEC @RC = sp_OAMethod 
 @FileID
 , 'WriteLine'
 , Null
 , @Date
-- Prints error if non 0 return code during sp_OAMethod WriteLine execution
IF @RC <> 0 
	BEGIN
		PRINT 'Error:  Writing string data to file'
	END

WHILE @i <= 100000
 BEGIN
   -- Appends the string value line to the file specified by the @File input parameter
	EXEC @RC = sp_OAMethod 
      @FileID
		, 'WriteLine'
		, Null 
      , @i
   -- Prints error if non 0 return code during sp_OAMethod WriteLine execution
	IF @RC <> 0 
		BEGIN
			PRINT 'Error:  Writing string data to file'
		END

	SET @i = @i + 1
END

-- Printing completion time in file
SET @Date = GETDATE()
EXEC @RC = sp_OAMethod @FileID
	, 'WriteLine'
	, Null
	, @Date
IF @RC <> 0 
 BEGIN
	PRINT 'Error:  Writing string data to file'
 END

EXECUTE @RC = sp_OADestroy @FileID
EXECUTE @RC = sp_OADestroy @FS

Conclusion


Duration (seconds)

Test 1:
Query Analyzer Baseline

37

Test 2:
OSQL - From Query Analyzer

32.6

Test 3:
OSQL – From a Command Prompt

5.69

Test 4:
VB Script

5.8

Test 5:
sp_OA – From Query Analyzer

487

Test 6:
sp_OACreate – From Query Analyzer

67

The conclusion to these tests dramatically show the performance benefits of using OSQL over the sp_OA stored procedures. The overhead associated to the use of sp_OA stored procedures comes from the additional operations required for looking up the PROGID, finding the path and name of the dll or exe in the registry, then testing for a set of base methods for validity and so on. The performance implications can also be very bad for the rest of the SQL Server system. The sp_OA stored procedures run in the SQL Server memory space and they use SQL Servers resources, more specifically in the mem-to-leave memory area. This can also induce memory pressure to the areas of SQL Server also running in the virtual address space such as the procedure cache and other vital caches. Also each use of the sp_OA stored procedures cost a context switch to the system costing at lease 1000 ticks in CPU processing.

When comparing OSQL running from a command prompt to a VB Script running from the OS, there is no real performance gain either way with these simple operations tested in this document.

OSQL seems to be a much better choice for performance when attempting to save output to a file. The sp_OA series of stored procedures allow for a great deal of flexibility but should not be used when performance is a factor, more to the point, they should probably be avoided altogether unless they are used for administrative tasks or other one off operations.

Zach Nichter

DBA Consultant

znichter@scalabilityexperts.com

Total article views: 33905 | Views in the last 30 days: 36
 
Related Articles
FORUM

Connditional executing of script

Connditional executing of script

FORUM

Procedure not executing in trigger

problem when execute procedure in trigger

FORUM

Error in Printing the document using SQL CLR Stored Procedure

Error in Printing the document using SQL CLR Stored Procedure

FORUM

Rights to execute stored procedure

Rights to execute stored procedure

Tags
miscellaneous    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones