title: How to optimize this query in SQL Server 2008
body: <p>How can I optimize query in <code>SQL Server 2008</code>? </p>
  
  <p>Here is my Query.</p>
  
  <pre><code>SELECT DISTINCT
          ListName ,
          ( SELECT    COUNT(id)
            FROM      tbl_SurveyAssign
            WHERE     ListName = a.ListName
                      AND UserName IN (
                      SELECT  UserName
                      FROM    tbl_Panelist
                      WHERE   tbl_Panelist.Subscribe = '1'
                              AND tbl_Panelist.Pending = '0'
                              AND tbl_Panelist.UserName IN (
                              SELECT  UserName
                              FROM    tbl_PanelistActivity
                              WHERE   tbl_PanelistActivity.ActivityDate &gt; ( GETDATE()
                                                            - 180 ) ) )
          ) AS Active ,
          ( SELECT    COUNT(id)
            FROM      tbl_SurveyAssign
            WHERE     ListName = a.ListName
                      AND UserName IN (
                      SELECT  UserName
                      FROM    tbl_Panelist
                      WHERE   tbl_Panelist.Subscribe = '1'
                              AND tbl_Panelist.Pending = '1' )
          ) AS Pending ,
          ( SELECT    COUNT(id)
            FROM      tbl_SurveyAssign
            WHERE     ListName = a.ListName
                      AND UserName IN (
                      SELECT  UserName
                      FROM    tbl_Panelist
                      WHERE   tbl_Panelist.Subscribe = '0'
                              AND tbl_Panelist.Pending = '0' )
          ) AS UnSubscribe ,
          ( SELECT    COUNT(id)
            FROM      tbl_SurveyAssign
            WHERE     ListName = a.ListName
                      AND UserName IN (
                      SELECT  UserName
                      FROM    tbl_Panelist
                      WHERE   tbl_Panelist.Subscribe = '1'
                              AND tbl_Panelist.Pending = '0'
                              AND tbl_Panelist.UserName NOT IN (
                              SELECT  UserName
                              FROM    tbl_PanelistActivity
                              WHERE   tbl_PanelistActivity.ActivityDate &gt; ( GETDATE()
                                                            - 180 ) ) )
          ) AS Inactive ,
          ( SELECT    COUNT(id)
            FROM      tbl_SurveyAssign
            WHERE     ListName = a.ListName
          ) AS Total ,
          ( SELECT    COUNT(id)
            FROM      tbl_SurveyAssign
            WHERE     ListName = a.ListName
                      AND UserName NOT IN ( SELECT    UserName
                                            FROM      tbl_Panelist )
          ) AS NotMember
  FROM    tbl_SurveyAssign a
  </code></pre>