Solved

SQL Server long running query

Posted on 2009-05-20
3
277 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

776 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