3
\$\begingroup\$

The following stored procedure allows me to write in a file stored on my SQL Server:

CREATE PROCEDURE [dbo].[spWriteToFile]
(
  @PATH_TO_FILE nvarchar(MAX),
  @TEXT_TO_WRITE nvarchar(MAX)
)
AS
BEGIN
  DECLARE @OLE int 
  DECLARE @FileID int

  EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT 
  EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @PATH_TO_FILE, 8, 1 
  EXECUTE sp_OAMethod @FileID, 'WriteLine', NULL, @TEXT_TO_WRITE
  EXECUTE sp_OADestroy @FileID
  EXECUTE sp_OADestroy @OLE 
END

In order to grant all the needed permissions, I had to execute the following query:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Here is an exemple to execute the stored procedure:

-- NB: The folder "D:\test\" has to be created before
EXEC dbo.spWriteToFile N'D:\test\t1.txt', N'Hello World !'

My problem is that I have to run this stored procedure a lot and therefore I have some performance issues. Is there something I can improve in my implementation to improve performance ? Is there another way to write in a file using SQL Server that could be faster ? If not, I think I'll have to try to call it less often.

NB: I'm using SQL Server Express Edition 2014 in case it could be relevant to my issue.

\$\endgroup\$
  • \$\begingroup\$ Can you elaborate on the performance issue? Is it compounding (getting worse)? \$\endgroup\$ – scsimon Jul 6 '17 at 13:52
  • \$\begingroup\$ I'm using this to insert some logs in a file. For example, if I decide to update 10 articles in my db, it is possible that this procedure will be called 100 times to log what fields changed, etc. Since it is opening and closing that file everytime, I have performance issues during that update. It is not getting worse but it is already too slow imo. I don't think I can improve this implementation. I should probably use it in another way. I was thinking about adding those lines in a table and have a script that runs every X minutes to add those lines in the file instead of adding them everytime. \$\endgroup\$ – Aleph0 Jul 6 '17 at 16:34
  • \$\begingroup\$ Thus the update should run faster but the lines I have to insert won't be directly added to the file. \$\endgroup\$ – Aleph0 Jul 6 '17 at 16:37
  • \$\begingroup\$ Oh i see... well if this is for logging changes then i would definitely log it in the DB itself with a trigger (on update, delete, insert, etc) or use versioning. I've never seen anyone log it to a flat file like that. Also,you could back up your transaction logs \$\endgroup\$ – scsimon Jul 6 '17 at 16:37
  • \$\begingroup\$ Since I'm working with Express Edition, a lot of things such as mirroring, log shipping, ... are a lot harder to implement. I have to find some workarounds to do the job but I don't always have good ideas and I end up trying things like that. \$\endgroup\$ – Aleph0 Jul 6 '17 at 16:44
1
\$\begingroup\$

As discussed in comments with @scsimon and @Milney, I've created the following table:

CREATE TABLE [dbo].[file_write] 
(
  [ID] bigint NOT NULL IDENTITY(1,1) ,
  [PATH_FILE] nvarchar(500) NOT NULL ,
  [TEXT_FILE] nvarchar(500) NOT NULL ,
  [DATE_WRITE] datetime NOT NULL ,
  PRIMARY KEY ([ID])
)

After that, i've written a stored procedure in order to insert into this table using only PATH_FILE and TEXT_FILE as parameters. This procedure replaces the one I had written in my question.

CREATE PROCEDURE [dbo].[insertFileWrite]
(
  @PATH_FILE nvarchar(500),
  @TEXT_FILE nvarchar(500)
)
AS
BEGIN
  DECLARE @NOW datetime = CURRENT_TIMESTAMP

  INSERT INTO dbo.file_write(PATH_FILE, TEXT_FILE, DATE_WRITE) 
         VALUES (@PATH_FILE , @TEXT_FILE , @NOW)
END

Finally, i've written a second stored procedure in order to write the lines that are in my table. This stored procedure is executed every 5 minutes by a scheduled task on the server.

CREATE PROCEDURE [dbo].[writeAllIntoFile]
AS
BEGIN
  DECLARE @PATH_FILE nvarchar(500)
  DECLARE @PATH_FILE_PREV nvarchar(500)
  DECLARE @TEXT_FILE nvarchar(500)
  DECLARE @ID bigint

  DECLARE @OLE int 
  DECLARE @FileID int

  BEGIN TRANSACTION t_writeAllIntoFile
  BEGIN TRY

    DECLARE c_writeFile CURSOR FOR
      SELECT PATH_FILE,
             TEXT_FILE,
             ID
      FROM dbo.file_write
      ORDER BY PATH_FILE ASC,
               DATE_WRITE ASC

    OPEN c_writeFile
    FETCH NEXT FROM c_writeFile INTO @PATH_FILE, @TEXT_FILE, @ID

    SET @PATH_FILE_PREV = @PATH_FILE

    EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT 
    EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @PATH_FILE, 8, 1 

    WHILE @@FETCH_STATUS = 0
    BEGIN
      IF (@PATH_FILE <> @PATH_FILE_PREV)
      BEGIN
        EXECUTE sp_OADestroy @FileID
        EXECUTE sp_OADestroy @OLE 

        EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT 
        EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @PATH_FILE, 8, 1 
      END

      EXECUTE sp_OAMethod @FileID, 'WriteLine', NULL, @TEXT_FILE
      DELETE FROM dbo.file_write WHERE ID = @ID

      SET @PATH_FILE_PREV = @PATH_FILE
      FETCH NEXT FROM c_writeFile INTO @PATH_FILE, @TEXT_FILE, @ID
    END

    CLOSE c_writeFile
    DEALLOCATE c_writeFile

    EXECUTE sp_OADestroy @FileID
    EXECUTE sp_OADestroy @OLE 

    COMMIT TRANSACTION t_writeAllIntoFile
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION t_writeAllIntoFile

    IF (SELECT CURSOR_STATUS('global','c_writeFile')) >= -1
    BEGIN
      IF (SELECT CURSOR_STATUS('global','c_writeFile')) > -1
      BEGIN
        CLOSE c_writeFile
      END

      DEALLOCATE c_writeFile
    END

    RETURN -999
  END CATCH
END

Whis this solution, I don't have to open and close my file everytime I want to write one line into it. Since writing is now running on its side, performances have been a lot improved.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.