Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

can lot of CASTs/Union or ISNULL possibly cause blocks?

Posted on 2014-11-07
8
Medium Priority
?
107 Views
Last Modified: 2014-11-25
This query runs fine every day scheduled.. but yesterday, it was blocked by itself in sysprocesses.. when I did select * from master..sysprocesses where blocked<>0 I saw 12 lines all for this one process running this, and all with same spid and same blocked column data.

is it because of excessive UNION/CAST etc. CAn you suggest?
0
Comment
Question by:25112
[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
  • 2
  • 2
8 Comments
 
LVL 5

Author Comment

by:25112
ID: 40428647
The actual processing is from stored proc.. but I put it as code format by substituting the parameter values here to illustrate, any thoughts on making this code efficient to avoid blocks/locks?

 ~~~~~~~~~~~~~~~~~~~~~~
SET NOCOUNT ON        
        
DECLARE @JobYear varchar(4)          
SET @JobYear = '2004'        
DECLARE @PairedID char(3)          
SET @PairedID = '113'         
        
        
DECLARE @ExtractDt datetime        
SET @ExtractDt = (Select Distinct Max(dbo.PASNote.ExtractDt)         
   From dbo.PASNote         
   Where dbo.PASNote.JobYear = @JobYear)        
        
DECLARE @PPExtractDate datetime        
SET @PPExtractDate = (Select Distinct  PPExtractDate        
   From dbo.PASNote         
   Where dbo.PASNote.JobYear = @JobYear        
               And ExtractDt = @ExtractDt)        
          
 Select         
 CAST(@JobYear as char(4))         
        + '-'        
        + CAST(@JobYear+1 as char(4)) As [JobYear],        
 CAST([PairID] as int) As [FinalizedPair],        
          CAST([BCPERName] as varchar(60)) As [FinalizedBCPER],        
        CAST([UCANName] as varchar(30)) As [FinalizedUCAN],        
        CAST((SUBSTRING(Cast([PairID] as char),1,1) + '-'        
         + SUBSTRING(Cast([PairID] as char),2,2) + '-'        
          + SUBSTRING(Cast([PairID] as char),4,2) + '-'        
           + SUBSTRING(Cast([PairID] as char),6,3) + '-'        
          + SUBSTRING(Cast([PairID] as char),9,1)) as varchar(30)) As [mtPair],        
        CAST(TempTable.[PickID] as varchar(4)) PickID,        
        CASE WHEN TempTable.[PickID]  IN('J2','J3','J4','J5','J6') THEN 'J2'        
                    WHEN TempTable.[PickID]  IN('M2','M3') THEN 'M2'           
                    ELSE  TempTable.[PickID]   END AS  [PickGroupCd],        
 CAST(' ' as varchar(60)) As [RegulatingNUA],        
        CAST(' ' as varchar(30)) As [SchedProgramCd],        
 CAST(' ' as varchar(30)) As [SchedIdCd],        
CAST(' ' AS VARCHAR(5)) AS [LeadStatusCd],        
CAST(' ' AS VARCHAR(5)) AS [PLKStatusCd],        
 CAST(0 as smallint) As [PeakedCap],        
 CAST(0 as int) As [Accumul],        
        CAST(0 as decimal(18,3)) As [PeakedMDT],        
 CAST(0 as decimal(18,5)) As [PaceAdj],        
        CAST(0  as dec(13,0)) as [PrePHCstDst],        
 CAST(0  as dec(13,3)) as [PrePHMDT],        
 CAST(0  as dec(13,0)) as [PrePTCstDst],        
 CAST(0  as dec(13,3)) as [PrePTMDT],         
        CAST(0  as dec(13,0)) as [PeakedPHForCstDst],        
        CAST(0  as dec(13,3)) as [PeakedPHForMDT],        
        CAST(0  as dec(13,0)) as [PeakedPTForCstDst],        
        CAST(0  as dec(13,3)) as [PeakedPTForMDT],        
 CAST(0  as dec(13,0)) as [PHJustForCstDst],        
 CAST(0  as dec(13,3)) as [PHJustForMDT],        
 CAST(0  as dec(13,0)) as [PTJustForCstDst],        
 CAST(0  as dec(13,3)) as [PTJustForMDT],        
 CAST(0  as dec(13,0)) as [PHPunchCstDst],        
 CAST(0  as dec(13,3)) as [PHPunchMDT],        
 CAST(0  as dec(13,0)) as [PTPunchCstDst],        
 CAST(0  as dec(13,3)) as [PTPunchMDT],        
 CAST(0  as dec(13,0)) as [BaseCstDst],        
 CAST(0  as dec(13,3)) as [BaseMDT],        
 CAST(0  as dec(13,0)) as [AdvCstDst],        
 CAST(0  as dec(13,3)) as [AdvMDT],        
        CAST([UCANName] as varchar(30)) AS [UCANName],        
        Cast(@ExtractDt As datetime) As [ExtractDt],        
        Cast(@PPExtractDate as datetime) AS [PPExtractDate]        
 FROM  dbo.CI_NUA         
        INNER JOIN         
        (SELECT CAST('DX' AS VARCHAR(10)) AS [NUACTGY],        
       CAST(TBLSC.[PickID] AS VARCHAR(4)) PickID        
       FROM        
       (SELECT CAST('A1' AS VARCHAR(4)) PickID        
       UNION ALL        
       SELECT CAST('A2' AS VARCHAR(4)) PickID        
       UNION ALL        
       SELECT CAST('A3' AS VARCHAR(4)) PickID        
       UNION ALL        
       SELECT CAST('B1' AS VARCHAR(4)) PickID        
       UNION ALL        
       SELECT CAST('C1' AS VARCHAR(4)) PickID        
       UNION ALL        
       SELECT CAST('D1' AS VARCHAR(4)) PickID        
       UNION ALL        
       SELECT CAST('D2' AS VARCHAR(4)) PickID        
       UNION ALL        
       SELECT CAST('E1' AS VARCHAR(4)) PickID        
       UNION ALL        
       SELECT CAST('E2' AS VARCHAR(4)) PickID        
       UNION ALL        
       SELECT CAST('F1' AS VARCHAR(4)) PickID        
       UNION ALL        
       SELECT CAST('G1' AS VARCHAR(4)) PickID        
       UNION ALL        
       SELECT CAST('H1' AS VARCHAR(4)) PickID        
       UNION ALL        
       SELECT CAST('I1' AS VARCHAR(4)) PickID        
       UNION ALL        
       SELECT CAST('J1' AS VARCHAR(4)) PickID        
       UNION ALL        
       SELECT CAST('K1' AS VARCHAR(4)) PickID        
 UNION ALL        
       SELECT CAST('L1' AS VARCHAR(4)) PickID        
       UNION ALL        
  SELECT CAST('L2' AS VARCHAR(4)) PickID        
       UNION ALL        
       SELECT CAST('M1' AS VARCHAR(4)) PickID        
       UNION ALL        
       SELECT CAST('N1' AS VARCHAR(4)) PickID        
       UNION ALL        
       SELECT CAST('O1' AS VARCHAR(4)) PickID        
     ) AS TBLSC        
       ) AS TempTable        
       ON dbo.CI_NUA.[BCPERAbbr] = TempTable.[NUACTGY]        
       AND dbo.CI_NUA.[JobYearFK] = @JobYear        
        WHERE [BCPERAbbr] = 'DX' AND PairID = @PairedID        
        AND TempTable.[PickID] NOT IN        
        (SELECT DISTINCT [ReportPickID]        
                FROM dbo.PASNote         
                WHERE GroupPair = @PairedID AND ExtractDt = @ExtractDt      
                AND [JobYear] = @JobYear)        
--         
--        
Union All          
--        
-- Select Note report lines from table dbo.PASNote         
--        
 Select         
 CAST(JobYear as char(4)) + '-' +        
        CAST((CAST(JobYear as dec(4,0)) + 1) as char(4)) As [JobYear],        
 CAST(GroupPair as int) As [FinalizedPair],        
        CAST(NUA.[BCPERName] as varchar(60)) As [FinalizedBCPER],        
        CAST(NUA.[UCANName] as varchar(30)) As [FinalizedUCAN],        
        CAST((SUBSTRING(Cast([GroupPair] as char),1,1) + '-'        
         + SUBSTRING(Cast([GroupPair] as char),2,2) + '-'        
          + SUBSTRING(Cast([GroupPair] as char),4,2) + '-'        
           + SUBSTRING(Cast([GroupPair] as char),6,3) + '-'        
          + SUBSTRING(Cast([GroupPair] as char),9,1)) as varchar(30)) As [mtPair],        
        CAST(ReportPickID as varchar(4)) PickID,        
        CASE WHEN ReportPickID  IN('J2','J3','J4','J5','J6') THEN 'J2'        
                    WHEN ReportPickID  IN('M2','M3') THEN 'M2'        
                    ELSE  ReportPickID   END AS  [PickGroupCd],        
 CAST(RegulatingBCPERName as varchar(60)) As [RegulatingNUA],        
        CAST(IsNull(SchedProgramCd,' ') as varchar(30)) As [SchedProgramCd],        
 CAST(IsNull(SchedIdCd,' ') as varchar(30)) As [SchedIdCd],        
    CAST([LeadStatusCd] AS VARCHAR(5)) AS [LeadStatusCd],        
CAST([PLKStatusCd] AS VARCHAR(5)) AS [PLKStatusCd],        
 CAST(IsNull(PeakedCap,0) as smallint) As [PeakedCap],        
 CAST(IsNull(Accumul,0) as int) As [Accumul],        
        CAST(IsNull(PeakedMDT1,0) as decimal(18,3)) As [PeakedMDT],        
 CAST(IsNull(DistrictPaceAdj,0) as decimal(18,5)) As [PaceAdj],        
        CAST(IsNull(PrePHCstDst,0)  as dec(13,0)) as [PrePHCstDst],        
 CAST(IsNull(PrePHMDT,0)  as dec(13,3)) as [PrePHMDT],        
 CAST(IsNull(PrePTCstDst,0)  as dec(13,0)) as [PrePTCstDst],        
 CAST(IsNull(PrePTMDT,0)  as dec(13,3)) as [PrePTMDT],         
        CAST((CAST(IsNull(PHJustForCstDst,0)  as dec(13,0)) + CAST(IsNull(PHPunchCstDst,0)  as dec(13,0)))  as dec(13,0)) as [PeakedPHForCstDst],        
        CAST((CAST(IsNull(PHJustForMDT,0)  as dec(13,3)) + CAST(IsNull(PHPunchMDT,0)  as dec(13,3)))  as dec(13,3)) as [PeakedPHForMDT],        
        CAST((CAST(IsNull(PTJustForCstDst,0)  as dec(13,0)) + CAST(IsNull(PTPunchCstDst,0)  as dec(13,0)))  as dec(13,0)) as [PeakedPTForCstDst],        
        CAST((CAST(IsNull(PTJustForMDT,0)  as dec(13,3)) + CAST(IsNull(PTPunchMDT,0)  as dec(13,3)))  as dec(13,3)) as [PeakedPTForMDT],        
 CAST(IsNull(PHJustForCstDst,0)  as dec(13,0)) as [PHJustForCstDst],        
 CAST(IsNull(PHJustForMDT,0)  as dec(13,3)) as [PHJustForMDT],        
 CAST(IsNull(PTJustForCstDst,0)  as dec(13,0)) as [PTJustForCstDst],        
 CAST(IsNull(PTJustForMDT,0)  as dec(13,3)) as [PTJustForMDT],        
 CAST(IsNull(PHPunchCstDst,0)  as dec(13,0)) as [PHPunchCstDst],        
 CAST(IsNull(PHPunchMDT,0)  as dec(13,3)) as [PHPunchMDT],        
 CAST(IsNull(PTPunchCstDst,0)  as dec(13,0)) as [PTPunchCstDst],        
 CAST(IsNull(PTPunchMDT,0)  as dec(13,3)) as [PTPunchMDT],        
 CAST(IsNull(BaseCstDst,0)  as dec(13,0)) as [BaseCstDst],        
 CAST(IsNull(BaseMDT,0)  as dec(13,3)) as [BaseMDT],        
 CAST(IsNull(AdvCstDst,0)  as dec(13,0)) as [AdvCstDst],        
 CAST(IsNull(AdvMDT,0)  as dec(13,3)) as [AdvMDT],        
      CAST(NUA.UCANName as varchar(30)) AS [UCANName],        
        Cast(ExtractDt As datetime) As [ExtractDt],        
        Cast(PPExtractDate As datetime) As [PPExtractDate]        
 FROM  dbo.PASNote         
        Inner Join dbo.CI_NUA NUA        
        On dbo.PASNote.GroupPair = NUA.PairID        
        AND NUA.[JobYearFK] = @JobYear        
        WHERE        
        JobYear = @JobYear and      
        GroupPair = @PairedID AND        
        ExtractDt = @ExtractDt        
   
 Order by FinalizedPair,         
                 PickID,RegulatingNUA,        
                 PeakedCap,SchedIdCd         
        
SET NOCOUNT OFF

Open in new window

0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40428666
What causes locks are long running queries. How long usually this query takes to run?
0
 
LVL 5

Author Comment

by:25112
ID: 40428806
right now it just ran in 3 seconds..  it has run scheduled for many months.. it was just yesterday that this has misbehaved (that we saw)..

what was puzzling was generally in sysprocesses, the blocked id is different than spid.. but here spid was same as blocked id and also several lines...
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 668 total points
ID: 40428820
Maybe was a single time issue. I recommend you to let it be and keep an eye on the process to see if will happen again.
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1332 total points
ID: 40429769
Just a small observation,

      SELECT DISTINCT
            MAX(dbo.PASNote.ExtractDt)
      FROM dbo.PASNote
      WHERE dbo.PASNote.JobYear = @JobYear


There is only a single value returned, so "distinct" is completely redundant in such circumstances.
"select distinct": use it sparingly because it is the enemy of performance.
0
 
LVL 5

Author Comment

by:25112
ID: 40438226
thank you. I will keep an eye on it.

Paul, appreciate your note.

do you have an mock tsql example to show how bad performance can deteriorate by adding a distinct?
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1332 total points
ID: 40439216
do you have an mock tsql example to show how bad performance can deteriorate by adding a distinct?
No. (And note, the impact would probably not be measurable in the query above. )

I raise it so you will be more aware of the implications it brings; it can be horrible. Literally horrible.


I do have an article on Distinct: Select Distinct is returning duplicates (Look for "Hail Mary..." in particular)
and in the references: Why I Hate DISTINCT
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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. …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 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