Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help on query performance Expand / Collapse
Author
Message
Posted Thursday, August 2, 2012 10:54 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:33 PM
Points: 3,373, Visits: 7,289
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1339375
Posted Thursday, August 2, 2012 10:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:33 PM
Points: 3,373, Visits: 7,289
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1339379
Posted Thursday, August 2, 2012 12:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:14 PM
Points: 13,126, Visits: 11,964
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1339463
Posted Thursday, August 2, 2012 1:10 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:33 PM
Points: 3,373, Visits: 7,289
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1339480
Posted Thursday, August 2, 2012 1:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:14 PM
Points: 13,126, Visits: 11,964
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1339487
Posted Friday, August 3, 2012 6:53 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:33 PM
Points: 3,373, Visits: 7,289
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1339787
Posted Friday, August 3, 2012 11:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:33 PM
Points: 3,373, Visits: 7,289
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1340011
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse