Hi,
I am new to Umbraco but have already seen a lot of badly optimized and not optimized Umbraco databases. I have optimized big databases and my starting point is always to give all tables a primary-key, to give all tables a Unique key and always make sure that all foreign-keys are - as pointed out by Andy - supported by an index. Below you find a script to create missing foreign-key indexes.
Naming conventions are usefull when trying to get an overview of the database. A common naming convention: pk_<tablename>, uk_<tablename>, fk_<tablename>_<foreignkeytablename>.
On small databases like (most) Umbraco installations the more indexes the happier? I dont recommend using scripts using information about missing indexes or Database Engine Tuning Advisor on large databases. They generate too many indexes. At least, make sure to have the foreing-key indexes in place first.
Jes
IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1
IF OBJECT_ID('tempdb..#FKTable') IS NOT NULL DROP TABLE #FKTable
--Create index temp table
CREATE TABLE #t1
(
do integer default(0),
index_name varchar(100),
index_descrip varchar(200),
index_keys varchar(200),
table_name varchar(100))
--Create FK temp table
CREATE TABLE #FKTable
(
fk_name varchar(100),
fk_keys varchar(200),
fk_keyno int,
table_name varchar(100))
--Collect and uppdate all index info
EXEC sp_msforeachtable "insert #t1 (index_name, index_descrip, index_keys) exec sp_helpindex '?'; update #t1 set table_name = '?', do = 1 where do = 0"
UPDATE #t1 SET table_name = replace(table_name , '[', '')
UPDATE #t1 set table_name = replace(table_name , ']', '')
--Collect all index info
INSERT INTO #FKTable
SELECT OBJECT_NAME(constid) AS FKName, COL_NAME(fkeyid, fkey) AS FKColumn, keyno,
s.name + '.' + OBJECT_NAME(fkeyid) AS TabName
FROM sysforeignkeys k
JOIN sys.objects c
ON k.constid = c.object_id
JOIN sys.schemas s
ON c.schema_id = s.schema_id
--If FK have two or more columns add them in one row to be able to compare with index columns.
DECLARE @FKName AS VARCHAR(200), @FKColumn as VARCHAR(100)
DECLARE FKCurusor CURSOR FOR
SELECT OBJECT_NAME(constid) AS FKName, COL_NAME(fkeyid, fkey) AS FKColumn
FROM sysforeignkeys k
JOIN sysobjects c
ON k.constid =
c.id
WHERE keyno > 1
ORDER BY keyno
DELETE FROM #FKTable WHERE fk_keyno > 1
OPEN FKCurusor
FETCH NEXT FROM FKCurusor INTO @FKName,@FKColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
UPDATE #FKTable SET
fk_keys = fk_keys + ', ' + @FKColumn
WHERE fk_name = @FKName
FETCH NEXT FROM FKCurusor INTO @FKName,@FKColumn
END
CLOSE FKCurusor
DEALLOCATE FKCurusor
/*
SELECT * FROM #FKTable
ORDER BY table_name
SELECT * FROM #t1
ORDER BY table_name
*/
PRINT '
---------------------------------------------------------------------
FK MISSING Indexes
----------------------------------------------------------------------------
'
SELECT DISTINCT table_name,fk_keys, 'CREATE NONCLUSTERED INDEX [' + fk_name + '] ON ' + table_name + '(' +fk_keys + ' ASC) 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]'
FROM #FKTable f1
WHERE NOT EXISTS (
SELECT fk_name
FROM #FKTable f
INNER JOIN #t1 t
ON f.table_name = t.table_name
WHERE (f1.fk_name = f.fk_name
AND fk_keys = index_keys)
OR ( f1.fk_name = f.fk_name
AND fk_keys = SUBSTRING (index_keys, 1 ,
CASE
WHEN CHARINDEX( ',',index_keys)= 0 THEN 0
ELSE CHARINDEX( ',',index_keys) -1
END
)))