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.
