SQL Server long running query

I have a query that is taking a long time i think it may ahve to do with the not in statement. please see below(this is customer logon info):
select rtrim(Cast(Datepart(mm, Date)as char)) + '/' + cast(DatePart(yyyy,Date) as char), count(distinct(ID)) from ActSrv_2007 WITH (NOLOCK)

where Type = 'SUCCESS'

and Date >= '2007-12-01'

and Date < '2008-01-01'

and SoftwareFamily = 'SoftPro'
--/*
and ID in

      (Select distinct(ID) from ActSrv_2007 WITH (NOLOCK)

            where

            Date >= '2007-12-01'

            and Date < '2008-01-01'

            and SoftwareFamily = 'SoftApplet'

      )
--*/
group by

rtrim(Cast(Datepart(mm, Date)as char)) + '/' + cast(DatePart(yyyy,Date) as char)


sonictitanAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Follow the advice previously given.  If you are unable then try something like this:

select      RIGHT(CONVERT(char(10), a.[Date], 103), 5),
            COUNT(DISTINCT a.ID)
From      ActSrv_2007 a WITH (NOLOCK)
            Inner Join (
                  Select      DISTINCT ID
                  From      ActSrv_2007 WITH (NOLOCK)
                  Where      [Date] >= '2007-12-01'
                              And [Date] < '2008-01-01'
                              And SoftwareFamily = 'SoftApplet'
                              ) b On a.ID = b.ID
Where      a.Type = 'SUCCESS'
            And [Date] >= '2007-12-01' And [Date] < '2008-01-01'
            And SoftwareFamily = 'SoftPro'
Group By
            RIGHT(CONVERT(char(10), a.[Date], 103), 5)
0
 
sonictitanAuthor Commented:
oops that should say NOT IN instead of IN
0
 
Bob HoffmanConnect With a Mentor DeveloperCommented:
Run the statement in TSQL, select the "Show execution plan" button. When the query completes look at the execution plan. Build indexed to eliminate any table scans. Try the query again.
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.