topic XML iteration is slow in Talend Data Catalog

XML iteration is slow

veryimportantdude — Fri, 15 Nov 2024 23:11:50 GMT

I have an tFileXMLinput and i map it to another XML

the file is quite big (26 MB) with 8000 plus records (products)

Now If i put generation mode to Memory-consuming (Xerces or Dom4j) it is too slow to be working but if I set it to fast (SAX), the xml is not read correctly and majority of data is missing.

Any suggestions and ideas?

thank you

Re: XML iteration is slow

Anonymous — Fri, 18 Feb 2022 12:09:03 GMT

SAX is fast because it doesn't store the whole XML in memory. It processes elements as it reads them. As such, it doesn't work particularly well with XPath queries especially if they need to look back. This is probably why you are missing data when using SAX.

You have to keep in mind that XML is essentially just text. The Complete Works of Shakespeare is only something like 5MB of text. So there is A LOT of data you are processing. How long does it take when using Xerces or Dom4j?

Re: XML iteration is slow

veryimportantdude — Mon, 21 Feb 2022 07:54:59 GMT

It is processing like 3 rows per second. and there are a lot of records (smth like 8k). I did not even let the whole job go trough.... but lets just say too long 🙂 Is there a work around?

Re: XML iteration is slow

veryimportantdude — Mon, 21 Feb 2022 07:59:53 GMT

Re: XML iteration is slow

Anonymous — Mon, 21 Feb 2022 11:24:09 GMT

There are ways in which you can possibly speed this up, but it's very difficult to assess without seeing the data and without knowing what you need to get from it. As an example, if your file is just a list of looped records (and you only need to extract data from those records without cross-referencing) you could try to use SAX to split the "loops" into individual XML segments (so a loop per row) and then use a tExtractXMLField component to extract the data from each segment.

Re: XML iteration is slow

veryimportantdude — Mon, 21 Feb 2022 13:52:08 GMT

Here is an exapmle of xml and I added some xpaths at the end

I am extracting stuff like sapid, ean, picture URL, tax etc etc.

</idents>

<base>

<name>APC BatteryKit 1000I 1000INET BP SU SUA</name>

<longname>APC BatteryKit 1000I 1000INET BP SU SUA</longname>

<categoryName value="I10002003">Diskovni sistemi in knjižnice / UPS / Baterije</categoryName>

<category sequence="1">Diskovni sistemi in knjižnice</category>

<category sequence="3">Baterije</category>

<warranty unit="month" value="0">Brez garancije</warranty>

<countryoforigin value="CN">China</countryoforigin>

</base>

<stock>

<backlog uom="ea" value="0" isotimestamp="2022 - 02 - 14T10:02: 40.004+00:00" unixtimestamp="1644832960" timezone="Europe/Ljubljana">20220225: Ni naročeno</backlog>

</backlogs>

</stock>

</prices>

<picture sequence="0" view="Glavna slika" width="200" height="150" link="https://actebis-images.com/productimages/530fc204-60f3-41a8-9951-ba3f14d177d2.jpg">Glavna slika</picture>

<picture sequence="1" view="Product shot Right-angle" width="400" height="300" link="http://cdn.cnetcontent.com/10/37/1037ab71-d709-4e3b-9b46-2d66eda464ab.jpg">Product shot Right-angle</picture>

<picture sequence="2" view="Product shot Right-angle" width="200" height="150" link="http://cdn.cnetcontent.com/33/e9/33e9534a-7436-48af-be58-443cfe124f5f.jpg">Product shot Right-angle</picture>

<picture sequence="3" view="Product shot Right-angle" width="640" height="480" link="http://cdn.cnetcontent.com/9c/e4/9ce4f526-8748-4a94-a959-857f8659405a.jpg">Product shot Right-angle</picture>

<picture sequence="4" view="Product shot Right-angle" width="75" height="75" link="http://cdn.cnetcontent.com/f7/ab/f7ab1f4f-7b14-457d-a959-ead6c112b7c4.jpg">Product shot Right-angle</picture>

</pictures>

<specification lang="SL" name="Oblika baterije" value="9">Plug-in module</specification>

<specification lang="SL" name="Vrsta naprave" value="10">UPS baterija</specification>

<specification lang="SL" name="Garancija proizvajalca" value="3">2-letna garancija</specification>

<specification lang="SL" name="Opis izdelka" value="5">APC Nadomestna baterijska kartuša #6 - UPS baterija - Lead-acid</specification>

</attributes>

</product>

And some of the Xpath

base/categoryName

/xmlData/product/idents/ident[@type='sapid']

/xmlData/product/base/category[@sequence='1']

/xmlData/product/base/category[@sequence='3']

/xmlData/product/stock/quantity/@value

/xmlData/product/pictures/picture[@sequence='6']/@link

Re: XML iteration is slow

veryimportantdude — Mon, 21 Feb 2022 13:53:35 GMT

SAX is not working since it does not read all the data in my xml. as you mentioned with SAX only data is extracted I have lots of cross-references

Re: XML iteration is slow

Anonymous — Mon, 21 Feb 2022 15:10:22 GMT

I have written a quick test job to demonstrate what I mean. You can see it here....

The tFileInputXML_1 is set to use SAX and is configured as below. It is solely used to split the XML into multiple mini "product" xml files.....

Notice how I have configured the fields and that the output schema includes a column called "product" of type Document.

The next component is the tExtractXMLField component. This is configured as below......

It returns all of the data you require (according to your last post), but processes each product sections individually. They are extracted using SAX and then processed individually in memory by the tExtractXMLField component. This uses Dom4j, but it will be storing much smaller chunks of data in memory and therefore should be significantly faster.

FYI I took the XML sample you gave me and multiplied it by 3 and built it within the structure your XPaths seemed to suggest.

Re: XML iteration is slow

veryimportantdude — Tue, 22 Feb 2022 11:06:24 GMT

when I do as your example I get only 0 rows being processed after tExtractXMLfield. I had a simillar problem yesterday when I was exploring some options. Any ideas what am I doing wrong. I set all the settings as you did. I think 🙂

Re: XML iteration is slow

veryimportantdude — Tue, 22 Feb 2022 11:11:44 GMT

Found the error. It was the Loop XPath query for. I was one level too high.

HOpe it works now

Re: XML iteration is slow

Anonymous — Tue, 22 Feb 2022 14:04:24 GMT

You may find some slight differences between what I have done and what you need. That will be if your XML structure is different to what I inferred from what you gave me. Let me know if you struggle with anything further.

Re: XML iteration is slow

veryimportantdude — Wed, 23 Feb 2022 07:19:39 GMT

All good. some minor stuff I had to add but it all works OK. Thank you very much. I though I would have to write a code in python or something to get around it but this did the trick.