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


  • Matt 10 posts 70 karma points
    Apr 24, 2014 @ 08:30
    Matt
    0

    Performance with large dataset

    We've got 2.5M rows in the icUrlTracker table and some queries are upwards of 3 seconds:

    RowNumber      DatabaseName Duration              CPU       TextData

     

    784         Umbraco          2751       1309       SELECT * FROM icUrlTracker WHERE Is404 = 0 AND RedirectRootNodeId = @redirectRootNodeId AND OldRegex IS NOT NULL ORDER BY Inserted DESC

    Would it be advisable to put another index on this table for the fields being queried?

    thanks

    Matt

     

    Copy Link
  • Dave Woestenborghs 3325 posts 11170 karma points MVP 5x admin c-trib
    Apr 24, 2014 @ 09:24
    Dave Woestenborghs
    0

    Hey,

    Maybe you can run this script : http://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/

    And see what it suggests for the icUrlTracker table. I think adding a index on columns Is404, RederictRootNodeId, OldRegex and inserted can do the trick.

    Copy Link
  • Stefan Kip 1606 posts 4098 karma points c-trib
    Apr 24, 2014 @ 09:29
    Stefan Kip
    0

    Hi Matt,

    Thanks for reporting this. At the moment I'm working on performance of the UrlTracker, as far as I've got any time to work on the UrlTracker ;-)
    Someone submitted a pull request to create an index for performance reasons.

    Could you execute the following SQL query to add this index, measure the same SQL queries which took 3 seconds and report your findings here?

    Thanks in advance!

    SQL query:

    IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'icUrlTracker')
BEGIN
    CREATE NONCLUSTERED INDEX [IX_icUrlTracker] ON [icUrlTracker] 
    (
        [ForceRedirect] ASC,
        [Is404] ASC,
        [RedirectRootNodeId] ASC
    )
    INCLUDE ( [Id],
    [OldUrl],
    [OldUrlQueryString],
    [OldRegex],
    [RedirectNodeId],
    [RedirectUrl],
    [RedirectHttpCode],
    [RedirectPassThroughQueryString],
    [Notes],
    [Referrer],
    [Inserted]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
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