Sign in Register
Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Simon Dingley 1431 posts 3332 karma points c-trib
    Aug 18, 2016 @ 12:44
    Simon Dingley
    0

    Remove Automatic Entries

    I have something ridiculous like over 4 million rows in the table now and the interface is so painfully slow its unusable. I need to remove all automatically added entries. Is the following query safe to use for this?

    DELETE FROM [dbo].[icUrlTracker]
WHERE RedirectNodeId IS NULL AND RedirectUrl IS NULL

    Is there anything else I need to consider?

    Thanks, Simon

    Copy Link
  • Stefan Kip 1606 posts 4098 karma points c-trib
    Aug 18, 2016 @ 12:48
    Stefan Kip
    0

    Hi Simon,

    The UrlTracker source describes an automatic entry as follows:

    RedirectNodeId.HasValue && ((Notes.StartsWith("A parent") || Notes.StartsWith("An ancestor") || Notes.StartsWith("This page") || Notes.StartsWith("This document")) && (Notes.EndsWith(" was moved") || Notes.EndsWith(" was renamed") || Notes.EndsWith("'s property 'umbracoUrlName' changed")))

    Yes, it's ugly :-)

    Copy Link
  • Simon Dingley 1431 posts 3332 karma points c-trib
    Aug 18, 2016 @ 12:56
    Simon Dingley
    0

    Haha thanks Stefan, I think I might stick with my query which as far as I can tell is all useless data anyway as it's not redirecting anywhere. I just need to make sure it deletes in batches due to the volume of data to remove!

    Cheers, Simon

    Copy Link
  • Simon Dingley 1431 posts 3332 karma points c-trib
    Aug 18, 2016 @ 13:18
    Simon Dingley
    100

    Final query seemed to work nice and quick locally by copying the records I want to keep out to a temporary table, truncating the existing table and putting the records back:

    CREATE TABLE #TempUrlTracker
(
    [Id]                             INT             IDENTITY (1, 1) NOT NULL,
    [OldUrl]                         NVARCHAR (1000) NULL,
    [OldUrlQueryString]              NVARCHAR (1000) NULL,
    [OldRegex]                       NVARCHAR (1000) NULL,
    [RedirectRootNodeId]             INT             NULL,
    [RedirectNodeId]                 INT             NULL,
    [RedirectUrl]                    NVARCHAR (1000) NULL,
    [RedirectHttpCode]               INT             DEFAULT ((301)) NOT NULL,
    [RedirectPassThroughQueryString] BIT             DEFAULT ((1)) NOT NULL,
    [Notes]                          NVARCHAR (1000) NULL,
    [Is404]                          BIT             DEFAULT ((0)) NOT NULL,
    [Referrer]                       NVARCHAR (1000) NULL,
    [Inserted]                       DATETIME        DEFAULT (getdate()) NOT NULL,
    [ForceRedirect]                  BIT             CONSTRAINT [DF_icUrlTracker_ForceRedirect] DEFAULT (0) NOT NULL
);

SET IDENTITY_INSERT #TempUrlTracker ON;

INSERT INTO #TempUrlTracker ([Id], [OldUrl], [OldUrlQueryString], [OldRegex], [RedirectRootNodeId], [RedirectNodeId], [RedirectUrl], [RedirectHttpCode], [RedirectPassThroughQueryString], [Notes], [Is404], [Referrer], [Inserted], [ForceRedirect])
SELECT [Id],
       [OldUrl],
       [OldUrlQueryString],
       [OldRegex],
       [RedirectRootNodeId],
       [RedirectNodeId],
       [RedirectUrl],
       [RedirectHttpCode],
       [RedirectPassThroughQueryString],
       [Notes],
       [Is404],
       [Referrer],
       [Inserted],
       [ForceRedirect]
FROM   [dbo].[icUrlTracker]
WHERE  redirectNodeId IS NOT NULL
       OR RedirectUrl IS NOT NULL;

SET IDENTITY_INSERT #TempUrlTracker OFF;

TRUNCATE TABLE [dbo].[icUrlTracker];

SET IDENTITY_INSERT [dbo].[icUrlTracker] ON;

INSERT INTO [icUrlTracker] ([Id], [OldUrl], [OldUrlQueryString], [OldRegex], [RedirectRootNodeId], [RedirectNodeId], [RedirectUrl], [RedirectHttpCode], [RedirectPassThroughQueryString], [Notes], [Is404], [Referrer], [Inserted], [ForceRedirect])
SELECT [Id],
       [OldUrl],
       [OldUrlQueryString],
       [OldRegex],
       [RedirectRootNodeId],
       [RedirectNodeId],
       [RedirectUrl],
       [RedirectHttpCode],
       [RedirectPassThroughQueryString],
       [Notes],
       [Is404],
       [Referrer],
       [Inserted],
       [ForceRedirect]
FROM   #TempUrlTracker;

SET IDENTITY_INSERT [dbo].[icUrlTracker] OFF;

IF (OBJECT_ID('tempdb..#TempUrlTracker') IS NOT NULL)
    BEGIN
        DROP TABLE #TempUrlTracker;
    END
    Copy Link

  • This forum is in read-only mode while we transition to the new forum.

    You can continue this topic on the new forum by tapping the "Continue discussion" link below.

Please Sign in or register to post replies