Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server long running query

Posted on 2009-05-20
3
Medium Priority
?
291 Views
Last Modified: 2012-05-07
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)


0
Comment
Question by:sonictitan
3 Comments
 

Author Comment

by:sonictitan
ID: 24434588
oops that should say NOT IN instead of IN
0
 
LVL 8

Assisted Solution

by:Bob Hoffman
Bob Hoffman earned 300 total points
ID: 24437135
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 450 total points
ID: 24437764
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Loops Section Overview
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question