August 2, 2012 at 10:54 am
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.allowableALW_AMT_CT, --??this figure looks like the raw amount (no pfact translation) in datawarehouse
ctrk.afactALW_PF_CT,
ctrk.insassnASSIGN_CT,
ctrk.authsentAUTHCLM_CT,
ctrk.authnoAUTHNUM_CT,
ctrk.batchmodeBATCHMODE_CT,
ctrk.bilamtBILAMT_CT,--??explicit decimals
convert(int,ctrk.branch)BRANCH_CT,
ctrk.chclmtypeCHCATGRY_CT,
ctrk.chplntypeCHPLNTYP_CT,
ctrk.chinsprovidCHPRVDR_CT,
ctrk.cmnsentCMNSNT_CT,
'00'CMP_EVNTDT_CT,
ctrk.evnttimeCMP_EVNTTM_CT,
ctrk.cusnoCUSTNUM_CT,
ctrk.qtydecDECQTY_CT,
ctrk.cufdecDECVOL_CT,
ctrk.descrDESCRIPTION_CT,
ctrk.digcde1DIAGCD001_CT,
ctrk.digcde2DIAGCD002_CT,
ctrk.digcde3DIAGCD003_CT,
ctrk.digcde4DIAGCD004_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.evnttimeEVNT_TM_CT,--actual timeHHMMSS
right('00',2-len(ctrk.evnttyp)) + convert(varchar(2),ctrk.evnttyp)EVNT_TYP_CT,
ctrk.gasflgGAS_CT,
ctrk.allins1INS001_CT,
ctrk.allins2INS002_CT,
ctrk.allins3INS003_CT,
ctrk.insformINSFRM_CT,
ctrk.insnoINSNUN_CT,
convert(int, ctrk.invno)INVNUM_CT,
ctrk.partITEMNUM_CT,
ctrk.lnenoLNNUM_CT,
ctrk.locLOC_CT,
ctrk.modifier1MODIFIER001_CT,
ctrk.modifier2MODIFIER002_CT,
ctrk.modifier3MODIFIER003_CT,
ctrk.modifier4MODIFIER004_CT,
ctrk.narsentNARSNT_CT,
ctrk.patnumPATNUM_CT,
ctrk.payorposPAYOR_CT,
ctrk.pfactPF_CT,
ctrk.phynoPHYCD_CT,
ctrk.priorauthnoPRIORAUTH_CT,
ctrk.prntwerrPRNTERR_CT,
ctrk.procdemodPROCDEMOD_CT,
ctrk.procdePROCDE_CT,
ctrk.prtqtyPRT_QTY_CT,
ctrk.qtyshQTY_CT,
ctrk.rolloverROLLOVR_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.suppSUP_CT,
convert(int,ctrk.terr)TER_CT,
ctrk.priceUNIT_PRICE_CT,
ctrk.unitUOM_CT,
ctrk.usernameUSERNAME_CT,
ctrk.cuftVOL_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@tCodestable(
codevarchar(5),
daysint,
yearsint)
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(*)
FROMdbo.vuClaimTrackAll c
JOIN@tCodes tON c.PROCDE_CT = t.code
--SELECT COUNT(*) FROM dbo.vuClaimTrackAll
--71,407,538
--SELECT COUNT(*)
--FROMdbo.vuClaimTrackAll c
--JOIN@tCodes tON c.PROCDE_CT = t.code
--2,452,814
INSERT INTOdbo.Log1262_OverlapViolations(
customer,
invoice,
hcpc,
lineNumber,
insurance,
stdos,
allowedDate,
prevDate,
billAmount)
SELECTc.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
FROMdbo.vuClaimTrackAll c WITH (NOLOCK)
LEFT
JOINdbo.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 tON c.PROCDE_CT = t.hcpc
WHEREc.INSNUN_CT IN ('MCA', 'MCB', 'MCC', 'MCD', 'MC1', 'MC2', 'MC3', 'MC4')
ANDNOT EXISTS( SELECT*
FROMdbo.Log1262_OverlapViolations v WITH (NOLOCK)
WHEREc.CUSTNUM_CT = v.customer
ANDc.PROCDE_CT = v.hcpc
ANDc.INSNUN_CT = v.insurance
ANDc.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.
August 2, 2012 at 10:59 am
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.bilamtBILAMT_CT, --
c.cusnoCUSTNUM_CT, --
c.insnoINSNUN_CT, --
CONVERT(int, c.invno)INVNUM_CT, --
c.lnenoLNNUM_CT, --
c.procdePROCDE_CT, --
CASE WHEN ISDATE(c.stdos) = 1
THEN CONVERT(datetime,cast(c.stdos as char(8)),112)
ELSE '1/1/1800' ENDSTDOS_CT --
FROM AR_LOAD_DATA.dbo.clmtrk c WITH(NOLOCK)
UNION ALL
SELECT
h.bilamtBILAMT_CT,
h.cusnoCUSTNUM_CT,
h.insnoINSNUN_CT,
CONVERT(int, h.invno)INVNUM_CT,
h.lnenoLNNUM_CT,
h.procdePROCDE_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)
August 2, 2012 at 12:55 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 2, 2012 at 1:10 pm
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.
August 2, 2012 at 1:25 pm
Luis Cazares (8/2/2012)
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).
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 3, 2012 at 6:53 am
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@tHCPCstable(
hcpcvarchar(5),
daysint,
yearsint)
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_Datedatetime
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.bilamtBILAMT_CT,
c.cusnoCUSTNUM_CT,
c.insnoINSNUN_CT,
CONVERT(int, c.invno)INVNUM_CT,
c.lnenoLNNUM_CT,
c.procdePROCDE_CT,
CASE WHEN ISDATE(c.stdos) = 1
THEN CONVERT(datetime,cast(c.stdos as char(8)),112)
ELSE '1/1/1800' ENDSTDOS_CT
FROM AR_LOAD_DATA.dbo.clmtrk c
WHEREc.insno IN ('MCA', 'MCB', 'MCC', 'MCD', 'MC1', 'MC2', 'MC3', 'MC4')
UNION ALL
SELECT
h.bilamtBILAMT_CT,
h.cusnoCUSTNUM_CT,
h.insnoINSNUN_CT,
CONVERT(int, h.invno)INVNUM_CT,
h.lnenoLNNUM_CT,
h.procdePROCDE_CT,
CASE WHEN ISDATE(h.stdos) = 1
THEN CONVERT(datetime,cast(h.stdos as char(8)),112)
ELSE '1/1/1800' ENDSTDOS_CT
FROMAR_LOAD_DATA.dbo.cltrkH h
WHEREh.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()
SELECTc.CUSTNUM_CT,
c.INVNUM_CT,
c.PROCDE_CT,
c.LNNUM_CT,
c.INSNUN_CT,
c.STDOS_CT,
c.BILAMT_CT
INTO ##Test2
FROMdbo.vuClaimTrackAll c
JOIN@tHCPCs t ON c.PROCDE_CT = t.hcpc
WHEREc.INSNUN_CT IN ('MCA', 'MCB', 'MCC', 'MCD', 'MC1', 'MC2', 'MC3', 'MC4')
SELECT DATEDIFF( ms, @Start_Date, GETDATE())
DROP TABLE ##Test1
DROP TABLE ##Test2
August 3, 2012 at 11:42 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply