 next
|
  Subject: Skipping invalid records on SQL insert
Author: chris misztur
Date: 20 Jun 2013 08:39 AM
|
Is this good practice?
for $row in /a:f46010/a:row
return
if(exists(collection("JDE_DEVELOPMENT.TESTDTA.F46010")/F46010[IPMCU/text() = mf:pad-string-to-length-left(' ',$row/a:MCU,12)and IPITM/text() = $row/a:ITM]))
then ()
else (
ddtek:sql-insert("sqlserver_us09sqldev_1433:JDE_DEVELOPMENT.TESTDTA.F46010",
"IPMCU",mf:pad-string-to-length-left(' ',$row/a:MCU,12),
"IPITM",$row/a:ITM,
"IPMLIT",$row/a:MLIT,
"IPMLOT",$row/a:MLOT,
"IPSPLP",$row/a:SPLP,
"IPPTPH",$row/a:PTPH,
"IPPKPH",$row/a:PKPH,
"IPRPPH",$row/a:RPPH,
"IPDLTN",$row/a:DLTN,
"IPVLOC",$row/a:VLOC,
"IPHLOC",$row/a:HLOC,
"IPPPUT",$row/a:PPUT,
"IPPPIK",$row/a:PPIK,
"IPFIFR",$row/a:FIFR,
"IPUSER",mf:getAuditInformation()/@user)
)
|
 top
|
  Subject: Skipping invalid records on SQL insert
Author: Ivan Pedruzzi
Date: 20 Jun 2013 12:11 PM
|
Look at the query plan on this version see if you gain
for $row in /a:f46010/a:row
,$pad in mf:pad-string-to-length-left(' ',$row/a:MCU,12)
where not(exists(collection("JDE_DEVELOPMENT.TESTDTA.F46010")/F46010[IPMCU/text() = $pad and IPITM/text() = $row/a:ITM]))
return
ddtek:sql-insert("sqlserver_us09sqldev_1433:JDE_DEVELOPMENT.TESTDTA.F46010",
"IPMCU",mf:pad-string-to-length-left(' ',$row/a:MCU,12),
"IPITM",$row/a:ITM,
"IPMLIT",$row/a:MLIT,
"IPMLOT",$row/a:MLOT,
"IPSPLP",$row/a:SPLP,
"IPPTPH",$row/a:PTPH,
"IPPKPH",$row/a:PKPH,
"IPRPPH",$row/a:RPPH,
"IPDLTN",$row/a:DLTN,
"IPVLOC",$row/a:VLOC,
"IPHLOC",$row/a:HLOC,
"IPPPUT",$row/a:PPUT,
"IPPPIK",$row/a:PPIK,
"IPFIFR",$row/a:FIFR,
"IPUSER",mf:getAuditInformation()/@user)
)
Hope this helps
Ivan Pedruzzi
Stylus Studio Team
|
|
