Fix performance of database 60GB with json strings

9

You are correct on both.
Unfortunately
1)WE do not have the luxury to copy 60GB per day to another DB ( or at least we are talking about that and will see)
2)I has insisting on creating the functions to insert XML and not JSON but nobody listened

The xml try is here:

Can't get it to work tho as doing something lie this will complain about cannot insert identity


  declare @json nvarchar(max) 


 DECLARE @intErrNo int, @intRowCount int

select IDENTITY(int, 1, 1) AS [MyID],(sessionid),(AbsolutePath),(query),(RequestHeaders),(RequestBody),(PartnerId),
(VistaMember),(ResponseHTTPStatusCode),(ResponseHeaders),(ResponseBody),(LogCreated) Into 
    #MyTempTable
    FROM [APILog]
  where AbsolutePath like '%complete%'
  and id = 52342

ALTER TABLE #MyTempTable ADD OriginalID INT

  DECLARE    @intLoop int = 1    

WHILE @intLoop >= 1
BEGIN
SELECT TOP 1
    @json = ResponseBody,
        @intLoop = MyID+1
    FROM    #MyTempTable
    WHERE    [MyID] >= @intLoop
    ORDER BY [MyID]
    SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT

    IF @intRowCount = 0
    BEGIN
        SELECT    @intLoop = 0    -- Prevent any more iterations
        GOTO LoopExit
    END

--    ... perform loop tasks based on @Col1, @Col2 ...
--insert into #MyTempTable 
if @intLoop =2 
Begin

 select * Into 
    #tmpBigTable
    from (
     select * from parseJSON(@json) ) AS MyAliasName
    
     SET IDENTITY_INSERT #tmpBigTable ON;
     End
ELSE  
 SET IDENTITY_INSERT #tmpBigTable ON;
    Insert into  #tmpBigTable
      select * from parseJSON(@json)  AS MyAliasName;
--select * from parseJSON(@json) 

LoopExit:

END    -- Next loop iteration
    -- Equivalent to FETCH NEXT FROM MyCursor INTO @Col1, @Col2, ...

select * from #tmpBigTable
DROP TABLE #tmpBigTable
-- Cleanup
DROP TABLE #MyTempTable    -- Equivalent to CLOSE MyCursor DEALLOCATE MyCursor