Добавить комментарий

Производительность csharp assembly vs. sp_OA

мне друзья прислали sql текст, который сохраняет в файл бинарное поле таблицы. Я такую же задачу написал на си шарпе в виде sql сборки. Вот результат сравнения производительности.

78сек vs 1сек (моя сборка) на файле в 24Мгб
Почти на 2 порядка.

/*
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 
'Ole Automation Procedures' because this component is turned off as part 
of the security configuration for this server. A system administrator can 
enable the use of 'Ole Automation Procedures' by using sp_configure. 
For more information about enabling 'Ole Automation Procedures', 
see "Surface Area Configuration" in SQL Server Books Online. 
sp_configure 'Ole Automation Procedures',1
reconfigure
 
select top 1 * from booksb
exec saveImage2file 
'c:\1cinfo\159_Sosredotochimsa_na_nesomnennom.rar', 
'booksb',
'file_data1', 
'where book_id=100'
*/
CREATE PROCEDURE saveImage2file @filename nvarchar(128), @table nvarchar(128),@column nvarchar(128), @where nvarchar(4000)
--Saves an image from an image column to a file
/*
как занести экзешник в таблицу
INSERT INTO MyTable (MyFieldVarbinaryMax) VALUES
(0x97687445646548789898890...)
 
*/
AS
IF (@filename is NULL) OR (@table is NULL) OR (@column is NULL) OR (@where is NULL)
 BEGIN
 PRINT 'saveImage2file saves an image from an image column to a file'
 PRINT 'Usage:'
 PRINT 'EXEC saveImage2file FileName, tableName, columnName, WhereCondition'
 PRINT ''
 PRINT 'For example: EXEC saveImage2file ''C:\test.bmp'', ''customers'', ''picture'', ''where customerID=234'''
 RETURN
 END
 
DECLARE @hr int,@fso int,@i int, @j int, @blocks int, @c int, @v varbinary(10), @buffer varbinary(1000), @sql nvarchar(4000)
 
EXEC @hr = sp_OACreate 'ADODB.Stream', @fso OUT
exec @hr = sp_OASetProperty @fso, 'Type', 1--adTypeBinary=1
EXEC @hr = sp_OAMethod @fso, 'Open'
set @sql = N'SELECT @c =(select DATALENGTH('+@column+') from '+@table+' '+@where+')'
exec sp_executesql @sql, N'@c int OUTPUT', @c OUTPUT
set @j=0
create table #t ( t image ) 
SET @i=@c
--read 1000 bytes at a time
WHILE @i>1000
 BEGIN
	 insert into #t 
	 exec getREADTEXT @column,@table,@where,@j,1000
 
	 set @buffer=(select convert(varbinary(1000),t) from #t)
	 EXEC @hr = sp_OASetProperty @fso, 'Write', @buffer
	 delete #t
	 SET @i=@i-1000
	 SET @j=@j+1000
 END
 
--read remaining bytes
if @i>0
 BEGIN
	 insert into #t 
	 exec getREADTEXT @column,@table,@where,@j,@i
	 set @buffer=(select convert(varbinary(1000),t) from #t)
	 EXEC @hr = sp_OASetProperty @fso, 'Write', @buffer
	 delete #t 
 END
drop table #t
EXEC @hr = sp_OAMethod @fso, 'SaveToFile', NULL, @filename, 2
EXEC @hr = sp_OAMethod @fso, 'Close'
 
GO
 
CREATE PROCEDURE getREADTEXT 
@column   nvarchar(128), 
@table    nvarchar(128),
@where    nvarchar(4000),
@position int,
@length   int
/*
 * given a column, table and filter clause plus a position to start reading 
 * the text and length to be read, the selected portion of the text 
 * will be returned
*/
 
AS
 
  declare @sql nvarchar(4000)
  set @sql = 'declare @txtPtr varbinary(16)
              select @txtPtr = TEXTPTR(' + @column + ') from ' + @table + ' '
              + @where + ' READTEXT ' + @table + '.' + @column + ' @txtPtr '
              + str(@position) + ' ' + str(@length) + ''
  exec(@sql)
 
GO