SearchItemWordPosition grows very fast because duplicates

One of my clients got this problem. His DB went from the 80 Mb to 1 Gb in just one week. After deep research i found table SearchItemWordPosition contains 33 millions records and takes a lot of space in the DB. On deeper search i found it contains a lot of duplicates and this problem is very common in the DNN Community. So first of all i have made a script to remove duplicates:


SELECT DISTINCT searchitemwordid,
                contentposition
INTO   #temptable
FROM   {databaseOwner}{objectQualifier}searchitemwordposition;

TRUNCATE TABLE {databaseOwner}{objectQualifier}searchitemwordposition;

INSERT INTO {databaseOwner}{objectQualifier}searchitemwordposition
SELECT searchitemwordid,
       contentposition
FROM   #temptable;

DROP TABLE #temptable; 

Next step i did a fix for stored procedure AddSearchItemWordPosition (thanks to Sebastian Leupold):

ALTER PROCEDURE {databaseOwner}{objectQualifier}AddSearchItemWordPosition  
  @SearchItemWordID INT,
  @ContentPositions VARCHAR(2000)
AS
    SET nocount ON

    DECLARE @TempList TABLE
      (
         itemwordid INT,
         position   INT
      )
    DECLARE @Position VARCHAR(10),
            @Pos      INT

    SET @ContentPositions = Ltrim(Rtrim(@ContentPositions)) + ','
    SET @Pos = Charindex(',', @ContentPositions, 1)

    IF Replace(@ContentPositions, ',', '') <> ''
      BEGIN
          WHILE @Pos > 0
            BEGIN
                SET @Position = Ltrim(Rtrim(LEFT(@ContentPositions, @Pos - 1)))

                IF @Position <> ''
                  BEGIN
                      INSERT INTO @TempList
                                  (itemwordid,
                                   position)
                      VALUES      (@SearchItemWordID,
                                   Cast(@Position AS INT))
                  END

                SET @ContentPositions = RIGHT(@ContentPositions,
                                        Len(@ContentPositions) - @Pos)
                SET @Pos = Charindex(',', @ContentPositions, 1)
            END
      END

    MERGE INTO {databaseOwner}{objectQualifier}SearchItemWordPosition T
    using @TempList S
    ON ( T.searchitemwordid = s.itemwordid AND T.contentposition = s.position)
    WHEN NOT matched THEN
      INSERT (searchitemwordid, contentposition)
      VALUES (itemwordid, position); 

After all of these changes it works pretty good now.