Solved

sql2008 T-sql query

Posted on 2014-11-20
4
120 Views
Last Modified: 2014-12-10
Guys,
I have a query below which running very long and takes too much resources, any idea what will be the better way to improve this query ? if I don't specific MaxDOP, this query can  cause CPU fluctuate at 80-100% consume rate, and this such query run almost 8 hours without complete.

select distinct
 
  xbn.[WSLG ServerName] 'WS P ServerName'
 ,xbn.[WSLG Groupname] 'WS P Groupname'
 ,xbn.AccountDomain 'WS P AccountDomain'
 ,xbn.AccountName 'WS P AccountName'
 ,'WS Type' = 
  case xbn.[WSLG Type]
   when 'LUM' then '(LUM) Local User Member'
   when 'GUM' then '(GUM) Global User Member'
   when 'GGM' then '(GGM) Global Group Member'
   when 'LU'  then '(LU) Local User'
  end
 
 ,qdt..Account 'WS Account Exist in Server'
 
 ,jxt.[Classification] 'NQ Classification'
 ,jxt.[Platform] 'NQ Platform'
 ,jxt.[Network Node Name] 'NQ Log Source (N.N.N.)'
 ,jxt.[Target User] 'NQ Target User'
 ,jxt.[Native Event Classification]'NQ Event ID'
 ,jxt.[UTC Date] 'NQ UTC Date'
 ,jxt.[Count] 'NQ Count'
 ,jxt.[AccountDomain] 'NQ AccountDomain'
 ,jxt.[AccountName] 'NQ AccountName'
 ,jxt.[Source IP] 'NQ Source IP'
 
'


 ,'Account is Disabled' = 
 case IsNull(qdt..Account,'') 
  when '' then (case  pdy.[Disabled] when 1 then 'Yes' when 0 then 'No' else NULL end)
          else (case qdt..[Disabled] when 'True' then 'Yes' when 'False' then 'No' else NULL end)
  end
 
 ,'Account is Locked' = 
 case IsNull(qdt..Account,'') 
  when '' then (case  pdy.[Locked] when 1 then 'Yes' when 0 then 'No' else NULL end)
          else (case qdt..[Locked] when 'True' then 'Yes' when 'False' then 'No' else NULL end)
  end

 ,'Passw. Never Expires' = 
 case IsNull(qdt..Account,'') 
  when '' then (case  pdy.[PWneverexpires] when 1 then 'Yes' when 0 then 'No' else NULL end)
          else (case qdt..[PWneverexpires]  when 'True' then 'Yes' when 'False' then 'No' else NULL end)
  end

  from dbo.DataLoginST inf
 
 left outer join dbo.ADUsers pdy on (jxt.AccountDomain+jxt.AccountName)=(pdy.domain+pdy.BaccName)
  
 left outer join dbo.WSLocalUsers qdt. on qdt..ServerName=jxt.AccountDomain and qdt..Account=jxt.AccountName 
 
 left outer join dbo.vWSPrivilegedUsersOneWeek wspuow on (xbn.AccountDomain+xbn.AccountName)=(jxt.AccountDomain+jxt.AccountName)and xbn.[WSLG ServerName]=rtrim(jxt.[Network Node Primary Name])
  
 where jxt.[Count] >=2
 OPTION (MAXDOP 6);

Open in new window

0
Comment
Question by:motioneye
[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
4 Comments
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40456355
Please post the show plan..

you get it by :
> run
set showplan_all on;

Open in new window

> then highlight and run your query
> post the output on the thread
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40456495
Joins involving concatenations will always be inefficient
Joins involving functions (such as rtrim) will always be inefficient
"SELECT DISTINCT" adds time and effort to the query, there are usually better ways.

1. You should be able to avoid the concatenations



left outer join dbo.ADUsers pdy ON jxt.AccountDomain = pdy.domain
           AND  jxt.AccountName = pdy.BaccName


 left outer join dbo.vWSPrivilegedUsersOneWeek wspuow ON xbn.AccountDomain = jxt.AccountDomain
           AND xbn.AccountName = jxt.AccountName
           AND xbn.[WSLG ServerName]=rtrim(jxt.[Network Node Primary Name])

2. Try it without the RTRIM()

Is that RTRIM() really needed?
AND xbn.[WSLG ServerName]=rtrim(jxt.[Network Node Primary Name]

Try it without that.
If it is necessary is there anyway you can keep that data clean?

3. Get rid of the DISTINCT

Look at each of the joined tables. Find a way to use subqueries that don't multiply the number of result rows, often these subqueries will involve using GROUP BY.

See:
Why I Hate DISTINCT
Select Distinct is returning duplicates ...
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40456806
I'm wondering why are you using MAXDOP 6?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40457778
I don't see the source of the "jxt" alias anywhere ... where does it come from?
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

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