This is procedure code.

The biggest table is prj_detail ( 53,000,000 records)

ALTER PROCEDURE UnitPricingTrans

(

@P_PER_START int,

@P_PER_END int,

@P_CO_CODE varchar(1000),

@P_UNIT_TYPE varchar(1000),

@P_UNIT varchar(1000)

)

AS

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#UnitType')) DROP TABLE #UnitType;

DECLARE @XML XML

SET @XML = '<list><i>' + Replace(@P_UNIT_TYPE, ',', '</i><i>') + '</i></list>'

SELECT x.y.value('.', 'varchar(1000)') AS Value INTO #UnitType FROM @XML.nodes('list/i/text()') AS x(y)

--select Value from #UnitType

--return

select

prj_detail.accounting_period,

prj_detail.co_code,

prj_detail.acct_currency_code,

equip_type.equip_type_code + ' - ' + equip_type.equip_type_name as UnitType,

equip_code.equip_code + ' - ' + equip_code.equip_name as Unit,

prj_info.prj_code + ' - ' + prj_info.prj_name as Project,

equip_code.equip_code + ' - ' + equip_code.equip_name as Equipment,

prj_detail.qty_amt as Quantity,

measure_code.measure_name as Measure,

prj_detail.goal_multiplier as Multiplier,

up_sched.up_sched_name as UPSchedule,

prj_detail.cost_cc AS Cost,

prj_detail.eff_cc AS Effort,

prj_detail.prj_code,

per_end_date

from

equip_type

inner join

equip_code

on equip_type.equip_type_code = equip_code.equip_type_code

inner join

prj_detail

on equip_code.equip_code = prj_detail.equip_code

inner join

doc_type

on prj_detail.sys_doc_type_code = doc_type.sys_doc_type_code

inner join

prj_info

on prj_detail.prj_code = prj_info.prj_code

inner join

unit_code

on prj_detail.prj_detail_code = unit_code.unit_code

inner join

measure_code

on unit_code.measure_code = measure_code.measure_code

inner join

up_sched_units

on unit_code.unit_code = up_sched_units.unit_code

inner join

up_sched

on up_sched_units.up_sched_code = up_sched.up_sched_code

where

doc_type.sys_doc_type_code = 'UP'

and

prj_detail.co_code IN (@P_CO_CODE)

and

equip_type.equip_type_code IN (select Value from #UnitType)

and

equip_code.equip_code IN (@P_UNIT)

and

accounting_period between @P_PER_START and @P_PER_END