The optimization process and recommendations:

1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

CREATE INDEX regressionrun_idx_regression_id ON RegressionRun (regression_run_id);
CREATE INDEX result_idx_test_id ON Result (test_run_id);
CREATE INDEX testrun_idx_regression_id ON TestRun (regression_run_id);

The optimized query:

SELECT
        TOP (1) *,
        (SELECT
            COUNT(result_id) 
        FROM
            Result 
        JOIN
            TestRun 
                ON (
                    TestRun.test_run_id = Result.test_run_id
                ) 
        JOIN
            RegressionRun 
                ON (
                    RegressionRun.regression_run_id = TestRun.regression_run_id
                ) 
        WHERE
            RegressionRun.regression_run_id = RR.regression_run_id 
            AND result_type_id = 31) a,
        (SELECT
            COUNT(result_id) 
        FROM
            Result 
        JOIN
            TestRun 
                ON (
                    TestRun.test_run_id = Result.test_run_id
                ) 
        JOIN
            RegressionRun 
                ON (
                    RegressionRun.regression_run_id = TestRun.regression_run_id
                ) 
        WHERE
            RegressionRun.regression_run_id = RR.regression_run_id 
            AND result_type_id = 32) b,
        (SELECT
            COUNT(result_id) 
        FROM
            Result 
        JOIN
            TestRun 
                ON (
                    TestRun.test_run_id = Result.test_run_id
                ) 
        JOIN
            RegressionRun 
                ON (
                    RegressionRun.regression_run_id = TestRun.regression_run_id
                ) 
        WHERE
            RegressionRun.regression_run_id = RR.regression_run_id 
            AND result_type_id = 33) c 
    FROM
        RegressionRun RR 
    ORDER BY
        RR.regression_run_id DESC