Help on query performance

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Performance Tuning » Help on query performance

Help on query performance

Rate Topic

Display Mode

Topic Options

Author

Message

Luis Cazares

Posted Thursday, August 02, 2012 10:54 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 1,091, Visits: 2,205

Hi all,
I'm having some troubles working on this requirement, because the last time I tried to run this query it ran for 9 hours. I know it can be improved, but I'm limited using a SQL Server 2000 SP4 server.
Could you give me some ideas on how to attack this problem? I'm not really asking for a complete solution, but any ideas will help.
First of all, I have a view based on 2 views based on one table each. Every stage is on a different DB. Here's the DDL for them.

-- ========================================================
--Original Table with indexes
-- ========================================================
CREATE TABLE [dbo].[CLMTRK](
	[CUSNO] [char](5) NOT NULL,
	[PATNUM] [numeric](6, 0) NOT NULL,
	[INVNO] [char](8) NOT NULL,
	[LNENO] [numeric](3, 0) NOT NULL,
	[EVNTDATE] [numeric](8, 0) NOT NULL,
	[EVNTTIME] [numeric](6, 0) NOT NULL,
	[PAYORPOS] [numeric](2, 0) NOT NULL,
	[EVNTTYP] [numeric](2, 0) NOT NULL,
	[INSNO] [char](3) NOT NULL,
	[PATCVG_SEQ] [numeric](4, 0) NOT NULL,
	[INSFORM] [numeric](3, 0) NOT NULL,
	[BRANCH] [char](3) NOT NULL,
	[TERR] [char](3) NOT NULL,
	[USERNAME] [varchar](20) NOT NULL,
	[BATCHMODE] [numeric](1, 0) NOT NULL,
	[ROLLOVER] [numeric](1, 0) NOT NULL,
	[PROCDE] [varchar](12) NOT NULL,
	[PROCDEMOD] [varchar](4) NOT NULL,
	[SUPP] [char](3) NOT NULL,
	[PART] [varchar](25) NOT NULL,
	[LOC] [char](3) NOT NULL,
	[DESCR] [varchar](32) NOT NULL,
	[GASFLG] [numeric](1, 0) NOT NULL,
	[UNIT] [varchar](5) NOT NULL,
	[STDOS] [numeric](8, 0) NOT NULL,
	[ENDOS] [numeric](8, 0) NOT NULL,
	[ALLINS1] [char](3) NOT NULL,
	[ALLINS2] [char](3) NOT NULL,
	[ALLINS3] [char](3) NOT NULL,
	[INSASSN] [numeric](1, 0) NOT NULL,
	[BILAMT] [numeric](8, 2) NOT NULL,
	[AFACT] [numeric](1, 0) NOT NULL,
	[ALLOWABLE] [numeric](15, 6) NOT NULL,
	[PFACT] [numeric](1, 0) NOT NULL,
	[PRICE] [numeric](15, 6) NOT NULL,
	[QTYDEC] [numeric](1, 0) NOT NULL,
	[QTYSH] [numeric](12, 3) NOT NULL,
	[PRTQTY] [numeric](12, 3) NOT NULL,
	[CUFDEC] [numeric](1, 0) NOT NULL,
	[CUFT] [numeric](12, 3) NOT NULL,
	[CMNSENT] [numeric](1, 0) NOT NULL,
	[AUTHSENT] [numeric](1, 0) NOT NULL,
	[NARSENT] [numeric](1, 0) NOT NULL,
	[AUTHNO] [varchar](20) NOT NULL,
	[PRIORAUTHNO] [varchar](20) NOT NULL,
	[MODIFIER1] [varchar](4) NOT NULL,
	[MODIFIER2] [varchar](4) NOT NULL,
	[MODIFIER3] [varchar](4) NOT NULL,
	[MODIFIER4] [varchar](4) NOT NULL,
	[DIGCDE1] [varchar](6) NOT NULL,
	[DIGCDE2] [varchar](6) NOT NULL,
	[DIGCDE3] [varchar](6) NOT NULL,
	[DIGCDE4] [varchar](6) NOT NULL,
	[PHYNO] [varchar](4) NOT NULL,
	[PRNTWERR] [numeric](1, 0) NOT NULL,
	[CHCLMTYPE] [numeric](6, 0) NOT NULL,
	[CHPLNTYPE] [numeric](6, 0) NOT NULL,
	[CHINSPROVID] [varchar](15) NOT NULL,
	[DISCOUNT] [numeric](2, 2) NOT NULL,
	[PCN] [varchar](12) NULL,
	[CLEARINGHOUSEPROCESSDATE] [datetime] NULL
)
GO

CREATE CLUSTERED INDEX [clmtrk_primary] ON [dbo].[CLMTRK] 
(
	[CUSNO] ASC,
	[PATNUM] ASC,
	[INVNO] ASC,
	[LNENO] ASC,
	[EVNTDATE] ASC,
	[EVNTTIME] ASC,
	[PAYORPOS] ASC,
	[EVNTTYP] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_CLMTRK] ON [dbo].[CLMTRK] 
(
	[SUPP] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_CLMTRK_1] ON [dbo].[CLMTRK] 
(
	[PHYNO] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_CLMTRK_2] ON [dbo].[CLMTRK] 
(
	[PCN] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


-- ========================================================
-- First view
-- ========================================================
CREATE view [dbo].[tblCLMTRK] (
	ALW_AMT_CT,
	ALW_PF_CT,
	ASSIGN_CT,
	AUTHCLM_CT,
	AUTHNUM_CT,
	BATCHMODE_CT,
	BILAMT_CT,
	BRANCH_CT,
	CHCATGRY_CT,
	CHPLNTYP_CT,
	CHPRVDR_CT,
	CMNSNT_CT,
	CMP_EVNTDT_CT,
	CMP_EVNTTM_CT,
	CUSTNUM_CT,
	DECQTY_CT,
	DECVOL_CT,
	DESCRIPTION_CT,
	DIAGCD001_CT,
	DIAGCD002_CT,
	DIAGCD003_CT,
	DIAGCD004_CT,
	DISCPCT_CT,
	ENDDOS_CT,
	EVNT_DT_CT,
	EVNT_TM_CT,
	EVNT_TYP_CT,
	GAS_CT,
	INS001_CT,
	INS002_CT,
	INS003_CT,
	INSFRM_CT,
	INSNUN_CT,
	INVNUM_CT,
	ITEMNUM_CT,
	LNNUM_CT,
	LOC_CT,
	MODIFIER001_CT,
	MODIFIER002_CT,
	MODIFIER003_CT,
	MODIFIER004_CT,
	NARSNT_CT,
	PATNUM_CT,
	PAYOR_CT,
	PF_CT,
	PHYCD_CT,
	PRIORAUTH_CT,
	PRNTERR_CT,
	PROCDEMOD_CT,
	PROCDE_CT,
	PRT_QTY_CT,
	QTY_CT,
	ROLLOVR_CT,
	STDOS_CT,
	SUP_CT,
	TER_CT,
	UNIT_PRICE_CT,
	UOM_CT,
	USERNAME_CT,
	VOL_CT)
as
select 
	ctrk.allowable				ALW_AMT_CT, --??this figure looks like the raw amount (no pfact translation) in datawarehouse
	ctrk.afact				ALW_PF_CT,
	ctrk.insassn				ASSIGN_CT,
	ctrk.authsent				AUTHCLM_CT,
	ctrk.authno				AUTHNUM_CT,
	ctrk.batchmode				BATCHMODE_CT,
	ctrk.bilamt				BILAMT_CT,	--??explicit decimals
	convert(int,ctrk.branch)				BRANCH_CT,
	ctrk.chclmtype				CHCATGRY_CT,
	ctrk.chplntype				CHPLNTYP_CT,
	ctrk.chinsprovid			CHPRVDR_CT,
	ctrk.cmnsent				CMNSNT_CT,
	'00'					CMP_EVNTDT_CT,	
	ctrk.evnttime				CMP_EVNTTM_CT,	
	ctrk.cusno				CUSTNUM_CT,
	ctrk.qtydec				DECQTY_CT,
	ctrk.cufdec				DECVOL_CT,
	ctrk.descr				DESCRIPTION_CT,
	ctrk.digcde1				DIAGCD001_CT,
	ctrk.digcde2				DIAGCD002_CT,
	ctrk.digcde3				DIAGCD003_CT,
	ctrk.digcde4				DIAGCD004_CT,
	convert(int,ctrk.discount * 100)	DISCPCT_CT,	
	case when isdate(ctrk.endos) = 1 then convert(datetime,cast(ctrk.endos as char(8)),112) else '1/1/1800' end
						ENDDOS_CT,
	case when isdate(ctrk.evntdate) = 1 then convert(datetime,cast(ctrk.evntdate as char(8)),112) else '1/1/1800' end
						EVNT_DT_CT,	--actual date
	ctrk.evnttime				EVNT_TM_CT,	--actual time	HHMMSS
	right('00',2-len(ctrk.evnttyp)) + convert(varchar(2),ctrk.evnttyp)				EVNT_TYP_CT,
	ctrk.gasflg				GAS_CT,
	ctrk.allins1				INS001_CT,
	ctrk.allins2				INS002_CT,
	ctrk.allins3				INS003_CT,
	ctrk.insform				INSFRM_CT,
	ctrk.insno				INSNUN_CT,
	convert(int, ctrk.invno)			INVNUM_CT,
	ctrk.part				ITEMNUM_CT,
	ctrk.lneno				LNNUM_CT,
	ctrk.loc				LOC_CT,
	ctrk.modifier1				MODIFIER001_CT,
	ctrk.modifier2				MODIFIER002_CT,
	ctrk.modifier3				MODIFIER003_CT,
	ctrk.modifier4				MODIFIER004_CT,
	ctrk.narsent				NARSNT_CT,
	ctrk.patnum				PATNUM_CT,
	ctrk.payorpos				PAYOR_CT,
	ctrk.pfact				PF_CT,
	ctrk.phyno				PHYCD_CT,
	ctrk.priorauthno			PRIORAUTH_CT,
	ctrk.prntwerr				PRNTERR_CT,
	ctrk.procdemod				PROCDEMOD_CT,
	ctrk.procde				PROCDE_CT,
	ctrk.prtqty				PRT_QTY_CT,
	ctrk.qtysh				QTY_CT,
	ctrk.rollover				ROLLOVR_CT,
	case when isdate(ctrk.stdos) = 1 then convert(datetime,cast(ctrk.stdos as char(8)),112) else '1/1/1800' end
						STDOS_CT,
	ctrk.supp				SUP_CT,
	convert(int,ctrk.terr)			TER_CT,
	ctrk.price				UNIT_PRICE_CT,
	ctrk.unit				UOM_CT,
	ctrk.username				USERNAME_CT,
	ctrk.cuft				VOL_CT			
from 
	AR_LOAD_DATA.dbo.clmtrk ctrk WITH(NOLOCK)


-- ========================================================
-- Work View (Basically the same as the other one but with lesser fields/columns)
-- ========================================================
CREATE view [dbo].[vuClaimTrackAll] as

SELECT     ALW_AMT_CT, ALW_PF_CT, BILAMT_CT, BRANCH_CT, CUSTNUM_CT, DESCRIPTION_CT, DIAGCD001_CT, DIAGCD002_CT, DIAGCD003_CT, 
                      DIAGCD004_CT, EVNT_DT_CT, EVNT_TM_CT, EVNT_TYP_CT, INS001_CT, INS002_CT, INS003_CT, INSFRM_CT, INSNUN_CT, INVNUM_CT, LNNUM_CT, 
                      MODIFIER001_CT, MODIFIER002_CT, MODIFIER003_CT, MODIFIER004_CT, PAYOR_CT, PF_CT, PROCDEMOD_CT, PROCDE_CT, QTY_CT, STDOS_CT, 
                      ROLLOVR_CT, ITEMNUM_CT
FROM         AR.dbo.tblCLMTRK WITH (nolock)
/*UNION
SELECT     ALW_AMT_CK, ALW_PF_CK, BILAMT_CK, BRANCH_CK, CUSTNUM_CK, DESCRIPTION_CK, DIAGCD001_CK, DIAGCD002_CK, DIAGCD003_CK, 
                      DIAGCD004_CK, EVNT_DT_CK, EVNT_TM_CK, EVNT_TYP_CK, INS001_CK, INS002_CK, INS003_CK, INSFRM_CK, INSNUM_CK, INVNUM_CK, 
                      LNNUM_CK, MODIFIER001_CK, MODIFIER002_CK, MODIFIER003_CK, MODIFIER004_CK, PAYOR_CK, PF_CK, PROCDEMOD_CK, PROCDE_CK, 
                      QTY_CK, STDOS_CK, ROLLOVR_CT, ITEMNUM_CK
FROM         AR.dbo.tblCLTRKH WITH (nolock)
*/

The problem seems to be when I make a self join, but I'm not sure how to approach this problem.
It's an overlapping issue and I've seen better ways to work with it but from 2005 and later.
Here's the code:

DECLARE	@tCodes		table(
		code		varchar(5),
		days		int,
		years		int)
INSERT INTO @tCodes 
SELECT 'A4310', 90,0 UNION ALL
SELECT 'A4311', 90,0 UNION ALL
SELECT 'J3490', 0,5 UNION ALL
SELECT 'L0631', 0,5 UNION ALL
SELECT 'L7900', 0,5

SELECT COUNT(*)
FROM	dbo.vuClaimTrackAll c 
JOIN	@tCodes t			ON c.PROCDE_CT = t.code

--SELECT COUNT(*) FROM dbo.vuClaimTrackAll
--71,407,538
--SELECT COUNT(*)
--FROM	dbo.vuClaimTrackAll c 
--JOIN	@tCodes t			ON c.PROCDE_CT = t.code
--2,452,814

INSERT INTO	dbo.Log1262_OverlapViolations(
		customer		,
		invoice			,
		hcpc			,
		lineNumber		,
		insurance		,
		stdos			,
		allowedDate		,
		prevDate		,
		billAmount		)
SELECT	c.CUSTNUM_CT	, 
		c.INVNUM_CT		, 
		c.PROCDE_CT		, 
		c.LNNUM_CT		, 
		c.INSNUN_CT		, 
		c.STDOS_CT		,
		DATEADD( yy, t.years, DATEADD( dd, (t.days) - 5, MAX( h.STDOS_CT))) AS allowedDate,
		MAX( h.STDOS_CT),
		c.BILAMT_CT
FROM	dbo.vuClaimTrackAll c WITH (NOLOCK)
LEFT
JOIN	dbo.vuClaimTrackAll h WITH (NOLOCK)	ON c.CUSTNUM_CT = h.CUSTNUM_CT
											AND c.PROCDE_CT = h.PROCDE_CT
											AND c.INSNUN_CT = h.INSNUN_CT
											AND c.STDOS_CT > h.STDOS_CT
JOIN	@tHCPCs t							ON c.PROCDE_CT = t.hcpc
WHERE	c.INSNUN_CT IN ('MCA', 'MCB', 'MCC', 'MCD', 'MC1', 'MC2', 'MC3', 'MC4')
AND		NOT EXISTS( SELECT	* 
					FROM	dbo.Log1262_OverlapViolations v WITH (NOLOCK)
					WHERE	c.CUSTNUM_CT = v.customer
					AND		c.PROCDE_CT  = v.hcpc
					AND		c.INSNUN_CT  = v.insurance
					AND		c.STDOS_CT   = v.stdos)
GROUP BY c.CUSTNUM_CT	, 
		c.INVNUM_CT		, 
		c.PROCDE_CT		, 
		c.LNNUM_CT		, 
		c.INSNUN_CT		, 
		c.INS001_CT		,
		c.STDOS_CT		, 
		t.years			,
		t.days			,
		c.BILAMT_CT

And here's some sample data, it might need some extra work and I can correct it later.

INSERT INTO [dbo].[CLMTRK]
SELECT '0000E','1','17091095','1','20120327','152816','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120319','20120319','MCD','OXX','   ','1','724.40','2','490.490000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','443.9','401.9',' ','4YMW','0','0','0','0333860001','0.00','170910959421',NULL UNION ALL 
SELECT '0003C','1','17136042','2','20120329','150448','1','1','MCA','1','400','110','001','batchop0','1','0','L0631',' ','DER','15006','  1','PROLIGN BACK BRACE XXLRG F30/32','2','EA','20120327','20120327','MCA','B52','   ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','847.2',' ',' ',' ','39SJ','0','0','0','0333860001','0.00','171360421500',NULL UNION ALL 
SELECT '0007A','1','17079561','1','20120320','141909','1','1','MCB','1','401','110','001','batchop0','1','0','L7900',' ','AUG','94009201','  1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120316','20120316','MCB','B01','   ','1','689.90','2','487.610000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','4KGD','0','0','0','0333860001','0.00','170795618872',NULL UNION ALL 
SELECT '0007X','1','17055723','2','20120404','151822','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15002','  1','PROLIGN BACK BRACE SML F14/18','2','EA','20120312','20120312','MCD','PXC','   ','1','1384.29','2','946.220000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.02',' ',' ',' ','NQ80','0','0','0','0333860001','0.00','170557236961',NULL UNION ALL 
SELECT '0007X','1','17055723','2','20120426','142451','2','1','PXC','2','405','110','001','batchop0','1','0','L0631',' ','DER','15002','  1','PROLIGN BACK BRACE SML F14/18','2','EA','20120312','20120312','MCD','PXC','   ','1','1384.29','2','946.220000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.02',' ',' ',' ','NQ80','0','0','0',' ','0.00','170557232227',NULL UNION ALL 
SELECT '0008Z','1','17104499','1','20120323','141745','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','AUG','94009201','  1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120321','20120321','MCA','J51','   ','1','689.90','2','475.940000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','1AEC','0','0','0','0333860001','0.00','171044996270',NULL UNION ALL 
SELECT '0008Z','1','17104499','1','20120412','141521','2','1','J51','2','405','110','001','batchop0','1','0','L7900',' ','AUG','94009201','  1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120321','20120321','MCA','J51','   ','1','689.90','2','475.940000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','1AEC','0','0','0',' ','0.00','171044997969',NULL UNION ALL 
SELECT '00090','1','17060022','1','20120329','144750','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','ENC','44007003','  1','ENCORE REVIVE BATTERY VAC SYSTEM','2','EA','20120313','20120313','MCD','836','   ','1','724.40','2','490.490000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','185',' ',' ','0IET','0','0','0','0333860001','0.00','170600224201',NULL UNION ALL 
SELECT '00090','1','17060022','1','20120514','143213','2','1','836','2','405','110','001','batchop0','1','0','L7900',' ','ENC','44007003','  1','ENCORE REVIVE BATTERY VAC SYSTEM','2','EA','20120313','20120313','MCD','836','   ','1','724.40','2','490.490000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','185',' ',' ','0IET','0','0','0','650193983','0.00','170600224568',NULL UNION ALL 
SELECT '0009R','1','17099854','1','20120322','135631','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCC','MWV','   ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','YIQ5','0','0','0','0333860001','0.00','170998543298',NULL UNION ALL 
SELECT '0009R','1','17099854','1','20120420','140641','2','1','MWV','2','405','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCC','MWV','   ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','YIQ5','0','0','0','ZZ332B00000X','0.00','170998549236',NULL UNION ALL 
SELECT '000A7','1','17055724','1','20120329','154413','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15001','  1','PROLIGN BACK BRACE XSM F4/12','2','EA','20120312','20120312','MCC','836','   ','1','1384.29','2','956.030000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.02','724.2',' ',' ','5R6K','0','0','0','0333860001','0.00','170557240493',NULL UNION ALL 
SELECT '000B8','1','17155001','1','20120419','171126','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120330','20120330','MCC','   ','   ','1','724.40','2','503.580000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','401.9',' ',' ','XTC1','0','0','0','0333860001','0.00','171550011824',NULL UNION ALL 
SELECT '000E8','1','17104500','1','20120323','141745','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120321','20120321','MCA','935','   ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','443.9','401.9','49WN','0','0','0','0333860001','0.00','171045006294',NULL UNION ALL 
SELECT '000FA','1','17148817','1','20120419','171120','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','AUG','94009201','  1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120329','20120329','MCC','B92','   ','1','689.90','2','504.980000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','OD16','0','0','0','0333860001','0.00','171488170133',NULL UNION ALL 
SELECT '000FA','1','17148817','1','20120514','143215','2','1','B92','2','405','110','001','batchop0','1','0','L7900',' ','AUG','94009201','  1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120329','20120329','MCC','B92','   ','1','689.90','2','504.980000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','OD16','0','0','0',' ','0.00','171488174948',NULL UNION ALL 
SELECT '000FK','1','17060296','1','20120329','150456','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120313','20120313','MCA','836','   ','1','724.40','2','497.150000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.61','443.9','401.9','OA24','0','0','0','0333860001','0.00','170602963047',NULL UNION ALL 
SELECT '000FP','1','17286604','1','20120427','140232','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15004','  1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120425','20120425','MCB','AW1','   ','1','1384.29','2','956.030000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.02',' ',' ',' ','MK07','0','0','0','0333860001','0.00','172866045737',NULL UNION ALL 
SELECT '000GB','1','17099899','1','20120322','135141','1','1','MCB','1','401','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCB','FH8','   ','1','724.40','2','487.610000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.61',' ',' ','4VUH','0','0','0','0333860001','0.00','170998991293',NULL UNION ALL 
SELECT '000GB','1','17099899','1','20120412','141520','2','1','FH8','2','405','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCB','FH8','   ','1','724.40','2','487.610000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.61',' ',' ','4VUH','0','0','0',' ','0.00','170998997900',NULL UNION ALL 
SELECT '000I7','1','17066072','1','20120320','141949','1','1','MCA','1','400','110','001','batchop0','1','0','L0631',' ','DER','15002','  1','PROLIGN BACK BRACE SML F14/18','2','EA','20120314','20120314','MCA','QJI','   ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','3CUD','0','0','0','0333860001','0.00','170660727903',NULL UNION ALL 
SELECT '000IV','1','17292310','2','20120430','142023','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15003','  1','PROLIGN BACK BRACE MED F20/22','2','EA','20120426','20120426','MCB','836','   ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','4CRF','0','0','0','0333860001','0.00','172923102972',NULL UNION ALL 
SELECT '000JN','1','17098025','1','20120417','150426','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCC','OE7','   ','1','724.40','2','504.980000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','332X','0','0','0','0333860001','0.00','170980256835',NULL UNION ALL 
SELECT '000JN','1','17098025','1','20120507','142933','2','1','OE7','2','405','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCC','OE7','   ','1','724.40','2','504.980000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','332X','0','0','0',' ','0.00','170980254004',NULL UNION ALL 
SELECT '000JN','1','17104423','2','20120417','150426','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15004','  1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120321','20120321','MCC','OE7','   ','1','1384.29','2','955.960000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','332X','0','0','0','0333860001','0.00','171044236836',NULL UNION ALL 
SELECT '000JN','1','17104423','2','20120507','142933','2','1','OE7','2','405','110','001','batchop0','1','0','L0631',' ','DER','15004','  1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120321','20120321','MCC','OE7','   ','1','1384.29','2','955.960000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','332X','0','0','0',' ','0.00','171044234005',NULL UNION ALL 
SELECT '000JO','1','17078945','1','20120419','163015','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','AUG','94009201','  1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120316','20120316','MCD','F40','   ','1','689.90','2','503.580000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','IX24','0','0','0','0333860001','0.00','170789454307',NULL UNION ALL 
SELECT '000JO','1','17078945','1','20120516','145109','2','1','F40','2','405','110','001','batchop0','1','0','L7900',' ','AUG','94009201','  1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120316','20120316','MCD','F40','   ','0','689.90','2','503.580000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','IX24','0','0','0',' ','0.00','170789453335',NULL UNION ALL 
SELECT '000KR','1','17090344','2','20120419','153306','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120319','20120319','MCA','B98','8D2','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','185',' ',' ','2OE9','0','0','0','0333860001','0.00','170903440723',NULL UNION ALL 
SELECT '000KR','1','17090344','2','20120425','160959','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120319','20120319','MCA','B98','8D2','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','185',' ',' ','2OE9','0','0','0','0333860001','0.00','170903446490',NULL UNION ALL 
SELECT '000KS','1','17153878','1','20120410','144802','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120330','20120330','MCD','MMT','   ','1','724.40','2','503.580000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','272.0',' ',' ','2THI','0','0','0','0333860001','0.00','171538788070',NULL UNION ALL 
SELECT '000L3','1','17141333','1','20120330','135848','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120328','20120328','MCD','MWY','   ','1','724.40','2','503.580000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','53YI','0','0','0','0333860001','0.00','171413335002',NULL UNION ALL 
SELECT '000L3','1','17141333','1','20120420','140641','2','1','MWY','2','405','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120328','20120328','MCD','MWY','   ','1','724.40','2','503.580000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','53YI','0','0','0','111874900','0.00','171413339239',NULL UNION ALL 
SELECT '000NO','1','17097954','1','20120329','144737','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','AUG','94009201','  1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCD','836','   ','1','689.90','2','490.490000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','5QKT','0','0','0','0333860001','0.00','170979542192',NULL UNION ALL 
SELECT '000NO','1','17097954','1','20120529','140801','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','AUG','94009201','  1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCD','836','   ','1','689.90','2','490.490000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','5QKT','0','0','0','0333860001','0.00','170979544083',NULL UNION ALL 
SELECT '000PA','1','17141627','1','20120330','135552','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120328','20120328','MCA','B1E','   ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','EV65','0','0','0','0333860001','0.00','171416273883',NULL UNION ALL 
SELECT '000PB','1','17066469','1','20120320','143325','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','ENC','44007003','  1','ENCORE REVIVE BATTERY VAC SYSTEM','2','EA','20120314','20120314','MCD','   ','   ','1','724.40','2','503.580000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','185',' ',' ','QYF9','0','0','0','0333860001','0.00','170664695880',NULL UNION ALL 
SELECT '000QN','1','17118438','1','20120329','144715','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15005','  1','PROLIGN BACK BRACE XLRG F28/30','2','EA','20120323','20120323','MCD','836','   ','1','1384.29','2','963.230000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','4QTV','0','0','0','0333860001','0.00','171184389247',NULL UNION ALL 
SELECT '000R8','1','17213175','1','20120413','141149','1','1','MCB','1','401','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120411','20120411','MCB','B0S','   ','1','724.40','2','487.610000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','4WLL','0','0','0','0333860001','0.00','172131750234',NULL UNION ALL 
SELECT '000RA','1','17104268','1','20120323','144058','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15002','  1','PROLIGN BACK BRACE SML F14/18','2','EA','20120321','20120321','MCD','L39','   ','1','1384.29','2','963.230000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','722.52',' ',' ',' ','5RJK','0','0','0','0333860001','0.00','171042681175',NULL UNION ALL 
SELECT '000RF','1','17154625','1','20120419','153330','1','1','MCA','1','400','110','001','batchop0','1','0','L0631',' ','DER','15006','  1','PROLIGN BACK BRACE XXLRG F30/32','2','EA','20120330','20120330','MCA','1LR','   ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','847.2',' ',' ',' ','5ROO','0','0','0','0333860001','0.00','171546254260',NULL UNION ALL 
SELECT '000RF','1','17154625','1','20120425','161025','1','1','MCA','1','400','110','001','batchop0','1','0','L0631',' ','DER','15006','  1','PROLIGN BACK BRACE XXLRG F30/32','2','EA','20120330','20120330','MCA','1LR','   ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','847.2',' ',' ',' ','5ROO','0','0','0','0333860001','0.00','171546250012',NULL UNION ALL 
SELECT '000WS','1','17091916','2','20120404','144125','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15001','  1','PROLIGN BACK BRACE XSM F4/12','2','EA','20120319','20120319','MCB','   ','   ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','RUY9','0','0','0','0333860001','0.00','170919167998',NULL UNION ALL 
SELECT '000WS','1','17207160','1','20120412','140712','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15002','  1','PROLIGN BACK BRACE SML F14/18','2','EA','20120410','20120410','MCB','   ','   ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','RUY9','0','0','0','0333860001','0.00','172071605483',NULL UNION ALL 
SELECT '000WS','1','17237671','1','20120418','134855','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15004','  1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120416','20120416','MCB','   ','   ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','RUY9','0','0','0','0333860001','0.00','172376710833',NULL UNION ALL 
SELECT '000XH','1','17154403','1','20120419','171134','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15002','  1','PROLIGN BACK BRACE SML F14/18','2','EA','20120330','20120330','MCC','B44','   ','1','1384.29','2','956.030000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','JPA1','0','0','0','0333860001','0.00','171544034428',NULL UNION ALL 
SELECT '000ZF','1','17155605','1','20120402','150607','1','1','MCA','1','400','110','001','batchop0','1','0','L0631',' ','DER','15001','  1','PROLIGN BACK BRACE XSM F4/12','2','EA','20120330','20120330','MCA','   ','   ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','847.2',' ',' ','AHMI','0','0','0','0333860001','0.00','171556058909',NULL UNION ALL 
SELECT '0010J','1','17097290','2','20120322','135629','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15003','  1','PROLIGN BACK BRACE MED F20/22','2','EA','20120320','20120320','MCC','B44','   ','1','1384.29','2','956.030000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','1VZV','0','0','0','0333860001','0.00','170972903118',NULL UNION ALL 
SELECT '0010Q','1','17172595','1','20120410','144757','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120403','20120403','MCD','X97','   ','1','724.40','2','490.490000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','414.04',' ',' ','FGQ8','0','0','0','0333860001','0.00','171725957437',NULL UNION ALL 
SELECT '0010R','1','17098737','1','20120329','154410','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCC','836','   ','1','724.40','2','505.010000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','52S8','0','0','0','0333860001','0.00','170987379695',NULL UNION ALL 
SELECT '0014M','1','17248353','1','20120420','143819','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15003','  1','PROLIGN BACK BRACE MED F20/22','2','EA','20120418','20120418','MCC','B44','   ','1','1384.29','2','955.960000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','BKR2','0','0','0','0333860001','0.00','172483531013',NULL UNION ALL 
SELECT '0014Q','1','17078261','2','20120419','161545','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15004','  1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120316','20120316','MCC','115','   ','1','1384.29','2','955.960000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.02',' ',' ',' ','5RCH','0','0','0','0333860001','0.00','170782619867',NULL UNION ALL 
SELECT '0015C','1','17145759','1','20120419','163000','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15003','  1','PROLIGN BACK BRACE MED F20/22','2','EA','20120328','20120328','MCD','B98','   ','1','1384.29','2','946.220000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','AZJ9','0','0','0','0333860001','0.00','171457591017',NULL UNION ALL 
SELECT '0015C','1','17145759','1','20120510','141029','2','1','B98','2','405','110','001','batchop0','1','0','L0631','NU','DER','15003','  1','PROLIGN BACK BRACE MED F20/22','2','EA','20120328','20120328','MCD','B98','   ','1','1384.29','2','946.220000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ','NU',' ',' ',' ','724.2',' ',' ',' ','AZJ9','0','0','0',' ','0.00','171457596160',NULL UNION ALL 
SELECT '0015L','1','17080356','1','20120329','150438','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','AUG','94009201','  1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120316','20120316','MCA','836','   ','1','689.90','2','475.940000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.61',' ',' ','BT04','0','0','0','0333860001','0.00','170803569979',NULL UNION ALL 
SELECT '0017H','1','17080320','1','20120327','144826','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','AUG','94009201','  1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120316','20120316','MCC','   ','   ','1','689.90','2','505.010000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.60',' ',' ','5QJP','0','0','0','0333860001','0.00','170803204000',NULL UNION ALL 
SELECT '001A6','1','17142125','1','20120330','135847','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15002','  1','PROLIGN BACK BRACE SML F14/18','2','EA','20120328','20120328','MCD','0XY','   ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','5RYM','0','0','0','0333860001','0.00','171421254737',NULL UNION ALL 
SELECT '001BR','1','17097956','1','20120419','161622','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','AUG','94009201','  1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCC','   ','   ','1','689.90','2','505.010000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.60',' ',' ','ABO6','0','0','0','0333860001','0.00','170979564661',NULL UNION ALL 
SELECT '001BR','1','17097956','1','20120525','140752','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','AUG','94009201','  1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120320','20120320','MCC','   ','   ','1','689.90','2','505.010000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.60',' ',' ','ABO6','0','0','0','0333860001','0.00','170979568969',NULL UNION ALL 
SELECT '001BV','1','17092613','1','20120327','144846','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120319','20120319','MCC','   ','   ','1','724.40','2','505.010000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','302.74',' ',' ','1K4B','0','0','0','0333860001','0.00','170926137289',NULL UNION ALL 
SELECT '001D0','1','17111768','2','20120327','141424','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15005','  1','PROLIGN BACK BRACE XLRG F28/30','2','EA','20120322','20120322','MCB','B01','   ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','724.4',' ',' ','4KP5','0','0','0','0333860001','0.00','171117683034',NULL UNION ALL 
SELECT '001EF','1','17097292','1','20120327','144839','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15001','  1','PROLIGN BACK BRACE XSM F4/12','2','EA','20120320','20120320','MCC','DL8','   ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','5RE6','0','0','0','0333860001','0.00','170972926050',NULL UNION ALL 
SELECT '001EF','1','17097292','1','20120416','135956','2','1','DL8','2','405','110','001','batchop0','1','0','L0631',' ','DER','15001','  1','PROLIGN BACK BRACE XSM F4/12','2','EA','20120320','20120320','MCC','DL8','   ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','5RE6','0','0','0',' ','0.00','170972929623',NULL UNION ALL 
SELECT '001ER','1','17091107','1','20120327','144823','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','AUG','94009201','  1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120319','20120319','MCC','   ','   ','1','689.90','2','504.980000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.60',' ',' ','0AQ2','0','0','0','0333860001','0.00','170911073472',NULL UNION ALL 
SELECT '001GB','1','17134620','1','20120329','150455','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120327','20120327','MCA','   ','   ','1','724.40','2','497.150000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','2ULG','0','0','0','0333860001','0.00','171346202818',NULL UNION ALL 
SELECT '001HE','1','17097293','1','20120327','144836','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15002','  1','PROLIGN BACK BRACE SML F14/18','2','EA','20120320','20120320','MCC','BI2','   ','1','1384.29','2','955.960000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','737.30',' ',' ',' ','1MSQ','0','0','0','0333860001','0.00','170972935674',NULL UNION ALL 
SELECT '001HE','1','17097293','1','20120416','135956','2','1','BI2','2','405','110','001','batchop0','1','0','L0631',' ','DER','15002','  1','PROLIGN BACK BRACE SML F14/18','2','EA','20120320','20120320','MCC','BI2','   ','1','1384.29','2','955.960000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','737.30',' ',' ',' ','1MSQ','0','0','0','650193983','0.00','170972939575',NULL UNION ALL 
SELECT '001HE','1','17097293','1','20120606','134745','2','1','AW1','3','405','110','001','batchop0','1','0','L0631',' ','DER','15002','  1','PROLIGN BACK BRACE SML F14/18','2','EA','20120320','20120320','MCC','AW1','   ','1','1384.29','2','955.960000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','737.30',' ',' ',' ','1MSQ','0','0','0','650193983','0.00','170972932066',NULL UNION ALL 
SELECT '001J2','1','17112600','2','20120329','154414','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15004','  1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120322','20120322','MCC','836','   ','1','1384.29','2','956.030000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','724.02',' ',' ','5RVN','0','0','0','0333860001','0.00','171126000773',NULL UNION ALL 
SELECT '001J9','1','17094008','2','20120419','153323','1','1','MCA','1','400','110','001','batchop0','1','0','A4310','KX','BRD','802130','  1','INSERTION TRAY W/BZK 30CC SYR','2','EA','20120319','20120319','MCA','MMD','   ','1','30.42','4','7.670000','4','10.140000','0','3.000','3.000','0','1.000','0','0','1',' ',' ','KX',' ',' ',' ','952.9',' ',' ',' ','1VXS','0','0','0','0333860001','0.00','170940082971',NULL UNION ALL 
SELECT '001J9','1','17094008','2','20120425','161018','1','1','MCA','1','400','110','001','batchop0','1','0','A4310','KX','BRD','802130','  1','INSERTION TRAY W/BZK 30CC SYR','2','EA','20120319','20120319','MCA','MMD','   ','1','30.42','4','7.670000','4','10.140000','0','3.000','3.000','0','1.000','0','0','1',' ',' ','KX',' ',' ',' ','952.9',' ',' ',' ','1VXS','0','0','0','0333860001','0.00','170940088728',NULL UNION ALL 
SELECT '001J9','1','17094008','2','20120511','142603','2','1','MMD','2','405','110','001','batchop0','1','0','A4310','KX','BRD','802130','  1','INSERTION TRAY W/BZK 30CC SYR','2','EA','20120319','20120319','MCA','MMD','   ','1','30.42','4','7.670000','4','10.140000','0','3.000','3.000','0','1.000','0','0','1',' ',' ','KX',' ',' ',' ','952.9',' ',' ',' ','1VXS','0','0','0','113400100','0.00','170940084897',NULL UNION ALL 
SELECT '001JZ','1','17111151','1','20120327','144834','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','ENC','44007003','  1','ENCORE REVIVE BATTERY VAC SYSTEM','2','EA','20120322','20120322','MCC','B0S','   ','1','724.40','2','505.010000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','401.9','185',' ','0BGP','0','0','0','0333860001','0.00','171111515309',NULL UNION ALL 
SELECT '001LJ','1','17165826','1','20120404','144103','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15002','  1','PROLIGN BACK BRACE SML F14/18','2','EA','20120402','20120402','MCB','836','   ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','715.09',' ',' ',' ','0AQA','0','0','0','0333860001','0.00','171658265064',NULL UNION ALL 
SELECT '001LK','1','17105828','1','20120327','141423','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15003','  1','PROLIGN BACK BRACE MED F20/22','2','EA','20120321','20120321','MCB','B0S','   ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','5RSF','0','0','0','0333860001','0.00','171058282729',NULL UNION ALL 
SELECT '001LW','1','17288037','1','20120427','140835','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','AUG','94009201','  1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120425','20120425','MCD','MCL','   ','1','689.90','2','490.490000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','5AFB','0','0','0','0333860001','0.00','172880376683',NULL UNION ALL 
SELECT '001MK','1','17170852','1','20120405','135343','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15004','  1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120403','20120403','MCD','DL8','   ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','716.90',' ',' ',' ','KHB0','0','0','0','0333860001','0.00','171708520782',NULL UNION ALL 
SELECT '001N3','1','17111153','1','20120327','152820','1','1','MCD','1','403','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120322','20120322','MCD','OXX','   ','1','724.40','2','490.490000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','WSN6','0','0','0','0333860001','0.00','171111530035',NULL UNION ALL 
SELECT '001NU','1','17104507','1','20120327','141505','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120321','20120321','MCA','B44','   ','1','724.40','2','497.150000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00',' ',' ','V332','0','0','0','0333860001','0.00','171045075736',NULL UNION ALL 
SELECT '001OH','1','17104505','1','20120327','144835','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120321','20120321','MCC','B44','   ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','185',' ',' ','CVZ9','0','0','0','0333860001','0.00','171045055440',NULL UNION ALL 
SELECT '001PE','1','17148818','1','20120402','145417','1','1','MCB','1','401','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120329','20120329','MCB','   ','   ','1','724.40','2','487.610000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','TBN2','0','0','0','0333860001','0.00','171488187318',NULL UNION ALL 
SELECT '001PJ','1','17170839','2','20120405','135028','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15002','  1','PROLIGN BACK BRACE SML F14/18','2','EA','20120403','20120403','MCB','GO6','   ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','724.02',' ',' ','5MJU','0','0','0','0333860001','0.00','171708390368',NULL UNION ALL 
SELECT '001PJ','1','17170839','2','20120425','142227','2','1','GO6','2','405','110','001','batchop0','1','0','L0631',' ','DER','15002','  1','PROLIGN BACK BRACE SML F14/18','2','EA','20120403','20120403','MCB','GO6','   ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','724.02',' ',' ','5MJU','0','0','0','650193983','0.00','171708395236',NULL UNION ALL 
SELECT '001QE','1','17137289','1','20120329','144742','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15004','  1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120327','20120327','MCD','127','   ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','724.02',' ',' ','PJW8','0','0','0','0333860001','0.00','171372892979',NULL UNION ALL 
SELECT '001QJ','1','17134621','1','20120404','150432','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120327','20120327','MCA','   ','   ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','5RY9','0','0','0','0333860001','0.00','171346212058',NULL UNION ALL 
SELECT '001QJ','1','17134621','1','20120430','144701','2','1','017','2','405','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120327','20120327','MCA','017','   ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','5RY9','0','0','0','3400118','0.00','171346214969',NULL UNION ALL 
SELECT '001SB','1','17212357','1','20120419','171105','1','1','MCC','1','402','110','001','batchop0','1','0','L0631',' ','DER','15002','  1','PROLIGN BACK BRACE SML F14/18','2','EA','20120411','20120411','MCC','8CJ','G10','1','1384.29','2','956.030000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','847.2',' ',' ','0XE2','0','0','0','0333860001','0.00','172123575544',NULL UNION ALL 
SELECT '001TY','1','17154410','2','20120402','150842','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15002','  1','PROLIGN BACK BRACE SML F14/18','2','EA','20120330','20120330','MCB','B44','   ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','724.02',' ',' ','4RI3','0','0','0','0333860001','0.00','171544109228',NULL UNION ALL 
SELECT '001TZ','1','17177283','1','20120410','160011','1','1','MCC','1','402','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120404','20120404','MCC','B44','   ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','185',' ',' ','4BZL','0','0','0','0333860001','0.00','171772838341',NULL UNION ALL 
SELECT '001UL','1','17191318','1','20120409','140205','1','1','MCB','1','401','110','001','batchop0','1','0','L7900',' ','AUG','94009201','  1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120406','20120406','MCB','B0S','   ','1','689.90','2','487.610000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.01','401.9',' ','TP39','0','0','0','0333860001','0.00','171913182825',NULL UNION ALL 
SELECT '001VH','1','17298310','1','20120430','144430','1','1','MCB','1','401','110','001','batchop0','1','0','L0631',' ','DER','15002','  1','PROLIGN BACK BRACE SML F14/18','2','EA','20120427','20120427','MCB','OXX','   ','1','1384.29','2','950.870000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.02',' ',' ',' ','NTU2','0','0','0','0333860001','0.00','172983109895',NULL UNION ALL 
SELECT '001X2','1','17118466','1','20120404','151827','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15003','  1','PROLIGN BACK BRACE MED F20/22','2','EA','20120323','20120323','MCD','QW1','   ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','4A9D','0','0','0','0333860001','0.00','171184667845',NULL UNION ALL 
SELECT '001XU','1','17201362','1','20120417','150402','1','1','MCC','3','402','110','001','batchop0','1','0','L0631',' ','DER','15002','  1','PROLIGN BACK BRACE SML F14/18','2','EA','20120409','20120409','MCC','B44','   ','1','1384.29','2','956.030000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2','724.02',' ',' ','590P','0','0','0','0333860001','0.00','172013622278',NULL UNION ALL 
SELECT '001YD','1','17334483','1','20120507','143920','1','1','MCB','1','401','110','001','batchop0','1','0','L7900',' ','AUG','94009201','  1','ELITE CUSTOM MANUAL VAC SYSTEM','2','EA','20120504','20120504','MCB','B01','   ','1','689.90','2','487.610000','2','689.900000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','401.9',' ','0W2G','0','0','0','0333860001','0.00','173344834683',NULL UNION ALL 
SELECT '001YO','1','17134623','1','20120404','150430','1','1','MCA','1','400','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120327','20120327','MCA','   ','   ','1','724.40','2','475.940000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','401.9',' ',' ','JS48','0','0','0','0333860001','0.00','171346231440',NULL UNION ALL 
SELECT '001YS','1','17104426','1','20120329','144751','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15004','  1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120321','20120321','MCD','14X','   ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','RVW2','0','0','0','0333860001','0.00','171044264233',NULL UNION ALL 
SELECT '001YS','1','17279331','1','20120426','140821','1','1','MCD','1','403','110','001','batchop0','1','0','L0631',' ','DER','15005','  1','PROLIGN BACK BRACE XLRG F28/30','2','EA','20120424','20120424','MCD','14X','   ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','RVW2','0','0','0','0333860001','0.00','172793318100',NULL UNION ALL 
SELECT '0020M','1','17170843','1','20120405','135028','1','1','MCB','1','401','110','001','batchop0','1','0','L7900',' ','ENC','44007001','  1','ENCORE PREMIUM MANUAL VAC SYSTEM','2','EA','20120403','20120403','MCB','BT1','   ','1','724.40','2','487.610000','2','724.400000','0','1.000','1.000','0','1.000','0','0','1',' ',' ',' ',' ',' ',' ','607.84','250.00','443.9','401.9','OM92','0','0','0','0333860001','0.00','171708430311',NULL UNION ALL 
SELECT '0022G','1','17171280','2','20120405','134927','1','1','MCA','1','400','110','001','batchop0','1','0','L0631',' ','DER','15004','  1','PROLIGN BACK BRACE LRG F24/26','2','EA','20120403','20120403','MCA','836','   ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','724.2',' ',' ',' ','DQ56','0','0','0','0333860001','0.00','171712808574',NULL UNION ALL 
SELECT '0023T','1','17148705','1','20120419','153327','1','1','MCA','1','400','110','001','batchop0','1','0','L0631',' ','DER','15003','  1','PROLIGN BACK BRACE MED F20/22','2','EA','20120329','20120329','MCA','BF9','   ','1','1384.29','2','928.090000','2','1384.290000','0','1.000','1.000','0','1.000','0','0','0',' ',' ',' ',' ',' ',' ','847.2',' ',' ',' ','4R0H','0','0','0','0333860001','0.00','171487053582',NULL

I couldn't save the execution plan and only managed to copy the screenshots.
If there's anything more I could do, I'll do my best.

Luis C.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post Attachments

Exec_Plan_Complete.png (5 views, 129.66 KB)

Exec_Plan1Top.png (2 views, 20.21 KB)

Exec_Plan1Bottom.png (0 views, 11.12 KB)

Exec_Plan2.png (0 views, 20.13 KB)

Exec_Plan3.png (1 view, 27.99 KB)

Post #1339375

Luis Cazares

Posted Thursday, August 02, 2012 10:59 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 1,091, Visits: 2,205

Would a SELECT of the original tables using just the required columns help? or it wouldn't make any significant improvement?
Or maybe a view limiting the rows I need before I use the query on my SP?

SELECT * FROM(
SELECT 
	c.bilamt					BILAMT_CT, --
	c.cusno						CUSTNUM_CT, --
	c.insno						INSNUN_CT, --
	CONVERT(int, c.invno)		INVNUM_CT, --
	c.lneno						LNNUM_CT, --
	c.procde					PROCDE_CT, --
	CASE WHEN ISDATE(c.stdos) = 1 
		THEN CONVERT(datetime,cast(c.stdos as char(8)),112) 
		ELSE '1/1/1800' END		STDOS_CT --
FROM AR_LOAD_DATA.dbo.clmtrk c WITH(NOLOCK)
UNION ALL
SELECT
	h.bilamt					BILAMT_CT,
	h.cusno						CUSTNUM_CT,
	h.insno						INSNUN_CT,
	CONVERT(int, h.invno)		INVNUM_CT,
	h.lneno						LNNUM_CT,
	h.procde					PROCDE_CT,
	ISNULL( CONVERT(datetime,cast(NULLIF(h.stdos, 0) as char(8)),112), '18000101')
								STDOS_CT
FROM AR_LOAD_DATA.dbo.cltrkH h WITH(NOLOCK)) Claims
JOIN (	SELECT 'A4310', 90,0 UNION ALL
         SELECT 'A4311', 90,0 UNION ALL
         SELECT 'J3490', 0,5 UNION ALL
         SELECT 'L0631', 0,5 UNION ALL
         SELECT 'L7900', 0,5)

Luis C.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1339379

Sean Lange

Posted Thursday, August 02, 2012 12:55 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 8,980, Visits: 8,540

Luis Cazares (8/2/2012)

Would a SELECT of the original tables using just the required columns help? or it wouldn't make any significant improvement?
Or maybe a view limiting the rows I need before I use the query on my SP?

Whenever possible you should only select columns of data that you actually need. It is hard to tell what this all doing without spending a bit of time digging. If I get some time I will dig deeper and see if I can find anything that might help.

Why the NOLOCK hints everywhere? This looks like some sort of claim system, just the type of system where dirty reads would be considered a bad idea.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1339463

Luis Cazares

Posted Thursday, August 02, 2012 1:10 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 1,091, Visits: 2,205

Thank you Sean, I know it might be a lot of work looking at it in depth (when is not really your job).
About the NOLOCKs they were there when I got here and considered as a good practice in here, but after reading some articles you've posted in other topics I'm trying to change this (they're off my query now).
However, my question about the columns was if it made a significant difference if I took the information directly from the table (which is in another database) instead of taking them from the view (based on the views, based on the original tables). I'm making the test right now.

Luis C.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1339480

Sean Lange

Posted Thursday, August 02, 2012 1:25 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 8,980, Visits: 8,540

Luis Cazares (8/2/2012)

Sorry if I come off a bit preachy on this topic but it is something that really hits home. I won't get into great detail but I worked for a company that mandated the use of the NOLOCK hint on every single query for performance. I did everything I could to convince them this was a bad idea for a lot of reasons. We processed debit card transactions on a scale of hundreds of thousands of transactions a day. All sorts of nasty stuff happened because of dirty reads and a lack of understanding.

However, my question about the columns was if it made a significant difference if I took the information directly from the table (which is in another database) instead of taking them from the view (based on the views, based on the original tables). I'm making the test right now.

I somehow missed the detail about this data residing on another database. It looks like the view has more columns than you are using in your select statement? It would definitely increase performance if the view either reduces the columns or you get the data directly.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1339487

Luis Cazares

Posted Friday, August 03, 2012 6:53 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 1,091, Visits: 2,205

This is shocking for me, I've run both queries several times and using the view is at least twice faster. Am I missing something?
This is the code I used to compare.
Note: The code to query from the original tables is the code used by the view.

DECLARE	@tHCPCs		table(
	hcpc		varchar(5),
	days		int,
	years		int)
INSERT INTO @tHCPCs 
SELECT 'A4310', 90,0 UNION ALL
SELECT 'A4311', 90,0 UNION ALL
SELECT 'A4314', 90,0 UNION ALL
SELECT 'A4320', 90,0 UNION ALL
SELECT 'A4322', 90,0 

DECLARE @Start_Date	datetime
SET @start_Date = GETDATE()

SELECT Claims.BILAMT_CT, 
	Claims.CUSTNUM_CT,
	Claims.INSNUN_CT, 
	Claims.INVNUM_CT, 
	Claims.LNNUM_CT, 
	Claims.PROCDE_CT, 
	Claims.STDOS_CT  
INTO ##Test1
FROM(
SELECT 
	c.bilamt					BILAMT_CT, 
	c.cusno						CUSTNUM_CT,
	c.insno						INSNUN_CT, 
	CONVERT(int, c.invno)		INVNUM_CT, 
	c.lneno						LNNUM_CT, 
	c.procde					PROCDE_CT, 
	CASE WHEN ISDATE(c.stdos) = 1 
		THEN CONVERT(datetime,cast(c.stdos as char(8)),112) 
		ELSE '1/1/1800' END		STDOS_CT 
FROM AR_LOAD_DATA.dbo.clmtrk c
WHERE	c.insno IN ('MCA', 'MCB', 'MCC', 'MCD', 'MC1', 'MC2', 'MC3', 'MC4')
UNION ALL
SELECT
	h.bilamt					BILAMT_CT,
	h.cusno						CUSTNUM_CT,
	h.insno						INSNUN_CT,
	CONVERT(int, h.invno)		INVNUM_CT,
	h.lneno						LNNUM_CT,
	h.procde					PROCDE_CT,
	CASE WHEN ISDATE(h.stdos) = 1 
		THEN CONVERT(datetime,cast(h.stdos as char(8)),112) 
		ELSE '1/1/1800' END		STDOS_CT
FROM	AR_LOAD_DATA.dbo.cltrkH h
WHERE	h.insno IN ('MCA', 'MCB', 'MCC', 'MCD', 'MC1', 'MC2', 'MC3', 'MC4')) Claims
JOIN	@tHCPCs HCPCs ON Claims.PROCDE_CT = HCPCs.HCPC


SELECT DATEDIFF( ms, @Start_Date, GETDATE())
USE OtherDatabase


SET @start_Date = GETDATE()

SELECT	c.CUSTNUM_CT	, 
		c.INVNUM_CT		, 
		c.PROCDE_CT		, 
		c.LNNUM_CT		, 
		c.INSNUN_CT		, 
		c.STDOS_CT		,
		c.BILAMT_CT
INTO ##Test2
FROM	dbo.vuClaimTrackAll c 
JOIN	@tHCPCs t ON c.PROCDE_CT = t.hcpc
WHERE	c.INSNUN_CT IN ('MCA', 'MCB', 'MCC', 'MCD', 'MC1', 'MC2', 'MC3', 'MC4')

SELECT DATEDIFF( ms, @Start_Date, GETDATE())

DROP TABLE ##Test1
DROP TABLE ##Test2

Luis C.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1339787

Luis Cazares

Posted Friday, August 03, 2012 11:42 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 1,091, Visits: 2,205

I guess I figured out how to reduce that over 5 hours query to a 5 minutes query (at least for the first run) and that's a really nice improvement.
There might be a better way to do it and maybe I'm doing extrawork during the query but it turned out that the most expensive part in the query is when I filter the information (reducing 71million rows to 1 million) but that could only improve if I had any indexes that change the index scans to index seeks.
I found out that just by organizing the information and process for you (using the articles from Gail Shaw and Jeff Moden) was a great eyesopener on what I could work on.

What I'm doing now is inserting the rows I need into a temporary table and work with that table instead of the view.

Luis C.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1340011

« Prev Topic | Next Topic »

Permissions