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

Above given script is just sample. I have long list of columns in SELECT and around 12+ columns in Sub Query. In my From clause there are around 8 tables.

To fetch 2000 records full query take 21 seconds and if I remove Subquiries 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 Sub-Query the results us fine.

Any help thanks.