Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2006
    Posts
    13

    Unanswered: How to get rid of multiple inner joins from the same table?

    I have a table that contains a lot of Jobs, their attribute names, and attribute values.

    I am working on fixing a stored procedure written by someone else which is very slow due to the many inner joins on the same table. Here is the original SP:

    Code:
    with JOB_ATTRIBUTES_2 as (SELECT job_id as job_id, int(attribute_value) as orig  
    FROM adfapp.job_attributes where attribute_name = 'Job.TotalDocs.Original') 
    SELECT DISTINCT JOB_HISTORY.JOB_ID AS JobID, 
    CASE RIGHT(JOB_ATTRIBUTES_1.ATTRIBUTE_VALUE, 3) 
        WHEN '-JR' THEN 'Job Reprint'
        WHEN '-MR' THEN 'Mail Piece Reprint'
        WHEN '-RR' THEN 'Retain Reprint' 
        ELSE 'Print' end AS Job_Name, JOB_ATTRIBUTES_5.ATTRIBUTE_VALUE AS Job_Tracking,
        JOB_ATTRIBUTES_3.ATTRIBUTE_VALUE AS Appl_Nb,
        int(JOB_ATTRIBUTES_4.ATTRIBUTE_VALUE) AS TOTAL_DOCS, JOB_ATTRIBUTES_2.orig AS ORIG_DOCS 
    
    FROM ADFAPP.JOB_HISTORY INNER JOIN 
    ADFAPP.JOB_ATTRIBUTES AS JOB_ATTRIBUTES_5 ON JOB_HISTORY.JOB_ID = JOB_ATTRIBUTES_5.JOB_ID INNER JOIN 
    ADFAPP.JOB_ATTRIBUTES AS JOB_ATTRIBUTES_4 ON JOB_HISTORY.JOB_ID = JOB_ATTRIBUTES_4.JOB_ID INNER JOIN 
    ADFAPP.JOB_ATTRIBUTES AS JOB_ATTRIBUTES_3 ON JOB_HISTORY.JOB_ID = JOB_ATTRIBUTES_3.JOB_ID INNER JOIN 
    ADFAPP.JOB_ATTRIBUTES AS JOB_ATTRIBUTES_1 ON JOB_HISTORY.JOB_ID = JOB_ATTRIBUTES_1.JOB_ID LEFT OUTER JOIN
    JOB_ATTRIBUTES_2 ON JOB_HISTORY.JOB_ID = JOB_ATTRIBUTES_2.JOB_ID
    WHERE JOB_HISTORY.PROCESS_NAME = 'Download' AND JOB_HISTORY.STEP_NAME = 'Submit' AND 
    	JOB_HISTORY.STATE_NAME = 'Complete' AND
    	(JOB_HISTORY.TIME_STAMP >= timestamp('2007-07-01 00:00:00.0') AND JOB_HISTORY.TIME_STAMP <= timestamp('2007-09-21 00:00:00.0')) AND 
            JOB_ATTRIBUTES_1.ATTRIBUTE_NAME = 'Job.MVS.JobName' AND 
            JOB_ATTRIBUTES_3.ATTRIBUTE_NAME = 'Job.Site.Assigned' AND 
            JOB_ATTRIBUTES_4.ATTRIBUTE_NAME = 'Job.TotalDocs' AND 
            JOB_ATTRIBUTES_5.ATTRIBUTE_NAME = 'Job.Tracking'
    ORDER BY Job_Name, JOB_ATTRIBUTES_3.ATTRIBUTE_VALUE
    for read only with ur
    Again, I want to get rid of all the inner joins on the JOB_ATTRIBUTES table but I am stuck on how to go about this. Any tips?

    Thanks for all your help.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Before accusing the JOINS of being the culprit, what indexes are on these tables?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Firstly :- format the SQL to a readable format

    Secondly :- do you understand what it does (or supposed to do)?

    Thirdly :- Why?

    Perhaps some sample data with the output would help
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The problem is not the query. The problem is the schema, which forces you to use klunky SQL to get any results out of it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Yeah, what Geo said. It's pretty easy to get rid of joins in a query. It becomes a tad more difficult if you are also constrained by the unreasonable requirement that it still work for it's purpose after you remove them.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    ah, the sublime joy of everyone's favorite pig, the EAV model. yummy!

  7. #7
    Join Date
    Nov 2005
    Posts
    122
    ... and fourth: You're asking in the wrong forum. This in not SQL Server TSQL syntax.

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    The code style is horrific but out of kindness (and a small challenge from Rudy) here goes ...

    Your first step should be to create a function to return an attribute for any given id. Then reformat the code so it begins to make (a tiny bit of) sense. Get rid of all joins and replace with function calls to get the attribute values. When the code is working I'd ensure the original coder gets sacked for the sake of everyones sanity.

    Test this function to ensure we're pulling the right attribute for any given JOB_ID. If there are multiple values for any attribute then you'll want to use @out_value = max( ATTRIBUTE_VALUE ).
    Code:
    create function dbo.get_attribute( @id int, @param varchar(30) ) returns varchar(80)
    as
    begin
    	declare @out_value varchar(80)
    
    	select @out_value = ATTRIBUTE_VALUE
    	from	ADFAPP.JOB_ATTRIBUTES
    	where	JOB_ID = @id
    		and ATTRIBUTE_NAME = @param
    
    	return @out_value
    end
    Then rewrite your original code using this function ...
    Code:
    select	distinct JOB_ID as JobID, 
    	case right( dbo.get_attribute(JOB_ID,'Job.MVS.JobName') , 3) 
    	    when '-JR' THEN 'Job Reprint'
    	    when '-MR' THEN 'Mail Piece Reprint'
    	    when '-RR' THEN 'Retain Reprint' 
    	    else 'Print' 
    	end as Job_Name, 
    	dbo.get_attribute(JOB_ID,'Job.Tracking') AS Job_Tracking,
    	dbo.get_attribute(JOB_ID,'Job.Site.Assigned') AS Appl_Nb,
    	int( dbo.get_attribute(JOB_ID,'Job.TotalDocs') ) AS TOTAL_DOCS, 
    	dbo.get_attribute(JOB_ID,'Job.OrigDocs') AS ORIG_DOCS
    from	ADFAPP.JOB_HISTORY
    where	PROCESS_NAME = 'Download' 
    	and STEP_NAME = 'Submit'
    	and STATE_NAME = 'Complete'
    	and TIME_STAMP between timestamp('2007-07-01 00:00:00.0') 
                       and timestamp('2007-09-21 00:00:00.0')
    order by Job_Name, dbo.get_attribute(JOB_ID,'Job.Site.Assigned')
    I can't promise it works as I don't have your tables or a SQL Server near me.

    The field ORIG_DOCS is all wrong in the original code as it goes to the attribute table but doesn't specify an attribute and then it pulls out a new field called orig - this looks very odd to me - so I guessed a bit in the above.

    Mike
    Last edited by mike_bike_kite; 09-29-07 at 10:03.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by kaffenils
    ... and fourth: You're asking in the wrong forum. This in not SQL Server TSQL syntax.
    Uhmm...yes it is, with the exception of the int() function which I assumed might be a UDF...).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by blindman
    Uhmm...yes it is, with the exception of the int() function which I assumed might be a UDF...).
    FOR READ ONLY WITH UR?
    int() and timestamp() functions? They can't be scalar value UDFs becuase they are not called with a two-part name.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ah. DB2. I'll move it to that forum.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •