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

Yukke · #1 (**permalink**) 09-27-07, 10:44

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.

blindman · #2 (**permalink**) 09-27-07, 11:38

Before accusing the JOINS of being the culprit, what indexes are on these tables?

gvee · #3 (**permalink**) 09-27-07, 11:44

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

blindman · #4 (**permalink**) 09-27-07, 12:59

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.

TallCowboy0614 · #5 (**permalink**) 09-27-07, 17:21

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.

jezemine · #6 (**permalink**) 09-28-07, 02:40

ah, the sublime joy of everyone's favorite pig, the EAV model. yummy!

kaffenils · #7 (**permalink**) 09-28-07, 05:04

... and fourth: You're asking in the wrong forum. This in not SQL Server TSQL syntax.

mike_bike_kite · #8 (**permalink**) 09-28-07, 05:25

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