Solved

SQL Server long running query

Posted on 2009-05-20
3
282 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
AJAX pass along a variable 3 68
TSQL Challenge... 7 44
Query - Duplicate dates with different activities counts 10 43
removing unwanted rows from an sql server ranked table 13 35
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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.​
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

756 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