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