Solved

SQL Server long running query

Posted on 2009-05-20
3
271 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 100 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 150 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now