Оптимизация запросов

--Создаем Базу данных
CREATE DATABASE Houses
 
--Потом создаем таблицы
USE Houses
 
CREATE TABLE monarch
    (
    id INT NOT NULL PRIMARY KEY,
    name NVARCHAR(50) NOT NULL,
    nickname NVARCHAR(50) NOT NULL,
    dynasty NVARCHAR(50) NOT NULL,
    dtStartReign DATETIME NOT NULL,
    dtEndReign DATETIME NOT NULL
    )
 
CREATE TABLE Parents
    (
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY ,
    ChildName NVARCHAR(50) NOT NULL,
    ParentName NVARCHAR(50) NOT NULL
    )

--загружаем рандомные строки (пока сделал для дам больше 01.01.1753)
DECLARE 
    @idrow INT = 1,
    @text NVARCHAR(50),
    @length INT = RAND() * 50,
    @dtStart DATETIME2,
    @dtFinish DATETIME2,
    @i INT
    
WHILE @idrow <= 10000
BEGIN
    SET @dtStart = (select DateAdd(d, ROUND(DateDiff(d, '17530101', '20130531') * RAND(), 0), '17530101'))
    SET @dtFinish = DateAdd(d, ROUND(DateDiff(d, @dtStart, '2013-05-31') * RAND(), 0), @dtStart)
    
    SET @text  = ''
    SET @length = RAND() * 50
    SET @i = 0
    WHILE (@i < @length)
    BEGIN
        SET @text = @text + CHAR(RAND() * 26 + 65)
        SET @i = @i + 1
    END
 
    INSERT INTO monarch VALUES
        (
        @idrow
        ,@text
        ,@text
        ,@text
        ,@dtStart
        ,@dtFinish
        )
    SET @idrow = @idrow + 1
END
 
 
INSERT INTO Parents
SELECT m1.name, m2.name
FROM monarch m1 INNER JOIN monarch m2 ON m1.id <> m2.id AND m1.dtStartReign = m2.dtEndReign

SELECT *, DATEDIFF(dd, dtStartReign, dtEndReign) AS ReignPeriod
FROM monarch
ORDER BY DATEDIFF(dd, dtStartReign, dtEndReign) DESC
 
SELECT TOP(1) *, DATEDIFF(dd, dtStartReign, dtEndReign) AS ReignPeriod
FROM monarch
WHERE DATEDIFF(dd, dtStartReign, dtEndReign) <> (SELECT(MAX(DATEDIFF(dd, dtStartReign, dtEndReign))) FROM monarch)
ORDER BY DATEDIFF(dd, dtStartReign, dtEndReign) DESC

SELECT res.*
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY DATEDIFF(dd, m1.dtStartReign, m1.dtEndReign) + DATEDIFF(dd, m2.dtStartReign, m2.dtEndReign) DESC) AS Row, p.*,  DATEDIFF(dd, m1.     dtStartReign, m1.dtEndReign) + DATEDIFF(dd, m2.dtStartReign, m2.dtEndReign) AS ReignPeriod
FROM Parents p INNER JOIN monarch m1 ON  m1.name = p.ParentName 
    INNER JOIN monarch m2 ON m2.name = p.ChildName
) AS res
WHERE Row = 2