Sometimes you wonder why you should invest into optimizing you website.   And certainly if you have lots of JavaScript on you page.

Let me give an example.  This is what I found in the top 10 results if I search on my name. 

You see, even the masters of search might struggle to get their SEO right.  Or is it the Google index failing this time?

Side note: you might wonder why this pages ranks so high.  I guess it’s the strong top domain.

Somehow I got duplicate relations in my Umbraco Relations table (about 120 k).  That’s impossible to clean them up manually, so I decided to remove the duplicates using an SQL statement directly on the database.  

Apparently deleting duplicates is not so hard on newer SQL servers when using Common Table Expressions.  CTE’s are a kind of temporary result sets that can be used within a single statement.  The difference with a temporary table is that it is not stored as an object.

WITH Dublicates_CTE(parentId, childId, Id)
AS
(
SELECT parentId, childId , Min(Id) Id
from umbracoRelation
group by  parentId, childId
having COUNT(*) >1
)

DELETE FROM umbracoRelation
WHERE Id IN (
    SELECT umbracoRelation.Id
    FROM umbracoRelation
    INNER JOIN Dublicates_CTE
    ON umbracoRelation.parentId= Dublicates_CTE.parentId
    AND umbracoRelation.childId= Dublicates_CTE.childId
    AND umbracoRelation.id <> Dublicates_CTE.Id
)

I found the idea of removing duplicates on this blogpost.

Based on http://our.umbraco.org/forum/developers/api-questions/3550-Delete-items-in-Recycle-Bin but also removes the previewXML, ContentVersions and Relations.

-- Uncomment below to verify the number of nodes returned is the
-- same as the number of nodes that is in the Recycle Bin
-- select * from umbracoNode where path like '%-20%' and id!=-20

delete
from cmsPreviewXml
where nodeid in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete
from cmsContentVersion
where contentId in (select id from umbracoNode where path like '%-20%' and id!=-20)

-- Delete all 'related' nodes and table contents...
delete from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsContentXML where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsDocument where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsPropertyData where contentNodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete
from umbracoRelation  where parentId in (select id from umbracoNode where path like '%-20%' and id!=-20) or
childId in (select id from umbracoNode where path like '%-20%' and id!=-20)

-- delete the XML nodes....
delete from umbracoNode where path like '%-20%' and id!=-20

 

EDIT 2014-08-06

in umbraco v6 you need to run this script a few times (because it's out of order) and include this:

    delete umbracodomains
    --select umbracodomains.*
    from umbracoDomains inner join umbracoNode on umbracodomains.domainrootstructureid = umbraconode.id
    where umbraconode.path like '%-20%' and umbraconode.id!=-20

If you have been developing JavaScript in visual studio 2010 and know you are missing the brace matching and cold folding capabilities. Apparently there is a plug-in supporting these useful features for JavaScript, it’s called the Jscript Editor Extensions.

Many thanks to Katrien De Grave who blogged about it!