Sub Query making Query slow

Please copy and paste following script.

DECLARE @MainTable TABLE(MainTablePkId int)
INSERT INTO @MainTable SELECT 1
INSERT INTO @MainTable SELECT 2

DECLARE @SomeTable TABLE(SomeIdPk int, MainTablePkId int, ViewedTime1 datetime)
INSERT INTO @SomeTable SELECT 1, 1, DATEADD(dd, -10, getdate())
INSERT INTO @SomeTable SELECT 2, 1, DATEADD(dd, -9, getdate())
INSERT INTO @SomeTable SELECT 3, 2, DATEADD(dd, -6, getdate())

DECLARE @SomeTableDetail TABLE(DetailIdPk int, SomeIdPk int, Viewed INT, ViewedTimeDetail datetime)
INSERT INTO @SomeTableDetail SELECT 1, 1, 1, DATEADD(dd, -7, getdate())
INSERT INTO @SomeTableDetail SELECT 2, 2, NULL, DATEADD(dd, -6, getdate())
INSERT INTO @SomeTableDetail SELECT 3, 2, 2, DATEADD(dd, -8, getdate())
INSERT INTO @SomeTableDetail SELECT 4, 3, 1, DATEADD(dd, -6, getdate())

SELECT  m.MainTablePkId,
        (SELECT COUNT(Viewed) FROM @SomeTableDetail),
        (SELECT TOP 1 s2.ViewedTimeDetail FROM @SomeTableDetail s2
                                                INNER JOIN @SomeTable s1 ON s2.SomeIdPk = s1.SomeIdPk
                                开发者_运维技巧                WHERE s1.MainTablePkId = m.MainTablePkId)
FROM @MainTable m

This script is just a sample. I have long list of columns in SELECT and around 12+ columns in a subquery. In my From clause there are around 8 tables.

To fetch 2000 records full query take 21 seconds and if I remove subqueries it just take 4 seconds.

I have tried to optimize query using 'Database Engine Tuning Advisor' and on adding new advised indexes and statistics but these changes make query time even bad.

Note:

As I have mentioned that this is test data to explain my question the real data has lot of tables joins columns but without subquery the results us fine.

Any help thanks.

Here is a CTE example that comes close to what you have but not exactly what you wanted but could get you started.

    WITH _CountViewed AS
(
    SELECT COUNT(Viewed) AS Viewed FROM @SomeTableDetail
),
_SomeDate AS 
(
    SELECT MAX(s2.ViewedTimeDetail) As ChangeTime, s1.MainTablePkId FROM @SomeTableDetail s2
                                                INNER JOIN @SomeTable s1 ON s2.SomeIdPk = s1.SomeIdPk
                                                GROUP BY s1.MainTablePkId
)
SELECT sd.MainTablePkId, cv.Viewed, sd.ChangeTime FROM _SomeDate sd 
    OUTER APPLY _CountViewed cv

Correlated subqueries run row-by-agoniziong-row, what you have is essentially a lot of cursors in your proc. Change them to joins (or joins to derived tables or CTEs). It is a bad practice to use correlated subqueries from a performance standard. They can always be replaced with joins, CTEs or derived table joins.

I'm trying to read between the lines a little and fill in the gaps, but I think this may be closer to what you're trying to accomplish.

SELECT m.MainTablePkId, SUM(std.Viewed), MAX(std.ViewedTimeDetail)
    FROM @MainTable m
        INNER JOIN @SomeTable st
            ON m.MainTablePkId = st.MainTablePkId
        INNER JOIN @SomeTableDetail std
            ON st.SomeIdPk = std.SomeIdPk
    GROUP BY m.MainTablePkId

I would create indexes on s1.MainTablePkId and m.MainTablePkId, s2.SomeIdPk and s1.SomeIdPk if they're not formally declared as PK. (which is already indexed by sql server)

You can do non-locking reads by adding (nolock), it will speed it up by not locking the table.

SELECT  m.MainTablePkId,
        (SELECT COUNT(Viewed) FROM @SomeTableDetail (nolock)),
        (SELECT TOP 1 s2.ViewedTimeDetail FROM @SomeTableDetail s2 (nolock)
                                                INNER JOIN @SomeTable s1 (nolock) ON s2.SomeIdPk = s1.SomeIdPk
                                                WHERE s1.MainTablePkId = m.MainTablePkId)
FROM @MainTable m (nolock)