How to replace while loop from Stored procedure using CLR proc C#

317 Views Asked by At

I want to replace the both While loops from the SP using CLR Proc in C# can somebody help on this We want to optimize the performance of the application some better thoughts are most welcomed thanks in advance Here is my code snippet.

SET nocount ON

DECLARE @maxsize INT
DECLARE @x INT
DECLARE @starttime1 datetime
DECLARE @stoptime1 datetime
DECLARE @starttime2 datetime
DECLARE @stoptime2 datetime

CREATE TABLE #temp (
    [Alternate Name] VARCHAR(100),
    [Size] INT,
    [Creation Date] CHAR(8),
    [Creation Time] CHAR(6),
    [Last Written Date] CHAR( 8),
    [Last Written Time] CHAR(6),
    [Last Accessed Date] CHAR(8),
    [Last Accessed Time] CHAR(6),
    [Attributes] INT
    )

SET @x = 0 
SET @starttime1 = GETDATE()
WHILE @x < 20
    BEGIN 
    SET @x = @x + 1
    INSERT INTO #temp 
       EXEC master.dbo.tsql_getfiledetails_OLE 'C:\temp\longfilename.txt'
    END

SET @stoptime1 = GETDATE()
SET @x = 0 
SET @starttime2 = GETDATE()

WHILE @x < 20
    BEGIN 
    SET @x = @x + 1
    INSERT INTO #temp EXEC master.dbo.xp_getfiledetails 'C:\temp\longfilename.txt'
END

SET @stoptime2 = GETDATE()
SELECT DATEDIFF(ms,@starttime1, @stoptime1) ole_duration, 
    DATEDIFF(ms,@starttime2,@stoptime2) clr_duration 

DROP TABLE #temp
0

There are 0 best solutions below