Optimize and accelerate bash script to transform XML to SQLITE

Question

I have this script in which I read an XML and I pass it to CSV and at the end of the script I transform it into SQLITE

#!/bin/bash rm -f -r rshost rscname rsctype ttstamp tservice tformat trdata trdata2 cat $1 | grep Telegram | sed -e 's/"/ /g' | awk '{ print $9 }' | cut -c27-30 > trdata cat $1 | grep Telegram | sed -e 's/"/ /g' | awk '{ print $3 }'| cut -c1-19 > ttstamp a=`cat $1 | grep RecordStart | head -1 | sed -e 's/"/ /g'| awk '{ print $15 }'` b=`cat $1 | grep RecordStart | head -1 | sed -e 's/"/ /g' | awk '{print $12 }' | sed -e 's/;/ /g' | sed -e 's/=/ /g' | awk '{print $4 }'` c=`cat $1 | grep RecordStart | head -1 | sed -e 's/"/ /g' | awk '{print $9,$10 }'` touch rsctype rshost rscname kk=`wc -l trdata | awk '{ print $1 }'` for i in `seq 1 $kk` do echo $a >> rsctype echo $b >> rshost echo $c >> rscname done cat $1 | grep Telegram | sed -e 's/"/ /g' | awk '{ print $5 }' > tservice cat $1 | grep Telegram | sed -e 's/"/ /g' | awk '{ print $7 }' > tformat cat $1 | grep Telegram | sed -e 's/"/ /g' | awk '{ print $9 }' | cut -c41-44 > trdata2 cat $1 | grep Telegram | sed -e 's/"/ /g' | awk '{ print $9 }' | cut -c31-34 > grhost awk -Wposix '{printf("%d\n","0x" $1)}' trdata > trdata3 awk -Wposix '{printf("%d\n","0x" $1)}' trdata2 > trdata4 sed -i "s%^0%0/%g" grhost cat grhost | cut -c1-3 > grhost2 sed -i "s%.\{4\}%/%g" grhost pr -mts, grhost2 grhost > grhostfinal sed -i "s/,//g" grhostfinal cat grhostfinal | cut -c1-4 > grhostfinal1 cat grhostfinal | cut -c5 > grhostfinal2 awk -Wposix '{printf("%d\n","0x" $1)}' grhostfinal2 > grhostfinal3 pr -mts, grhostfinal1 grhostfinal3 > grhostfinal4 sed -i "s/,//g" grhostfinal4 pr -mts, rshost ttstamp rsctype tservice tformat trdata4 trdata3 rscname grhostfinal4 > conjunto.csv sed -i "s|^|,|g" conjunto.csv sqlite3 test2.sqlite "select fecha from testxml4;" > data.csv cat data.csv | sort | uniq > data2.csv for k in `cat data2.csv` do grep "$k" conjunto.csv >> quitar done diff quitar conjunto.csv | grep ">" | sed 's/^> //g' > diferencia.csv echo `sqlite3 test2.sqlite < testxml` python csv2sqlite.py diferencia.csv test2.sqlite testxml4 rm -f -r rshost rscname rsctype ttstamp tservice tformat trdata trdata2 trdata3 trdata4 grhost2 grhost grhostfinal3 grhostfinal1 grhostfinal2 grhostfinal grhostfinal4 a b c data.csv conjunto.csv data2.csv quitar 

I have this XML (The data is a private)

 <CommunicationLog xmlns="http://knx.org/xml/telegrams/01"> <RecordStart Timestamp="" Mode="" Host="" ConnectionName="" ConnectionOptions="" ConnectorType="" MediumType="" /> <Telegram Timestamp="" Service="" FrameFormat="" RawData="" /> <Telegram Timestamp="" Service="" FrameFormat="" RawData="" /> <RecordStop Timestamp="" /> <RecordStart Timestamp="" Mode="" Host="" ConnectionName="" ConnectionOptions="" ConnectorType="" MediumType="" /> <Telegram Timestamp="" Service="" FrameFormat="" RawData="" /> <Telegram Timestamp="" Service="" FrameFormat="" RawData="" /> <RecordStop Timestamp="" /> </CommunicationLog> 

Once analyzed the data, I take them to a CSV and with the Python program csv2sqlite.py

python csv2sqlite.py CSVFILE.csv DB.sqlite TABLESQLITE 

My question is how can I make this script faster and more efficient, since it takes a long time to analyze all the data.

Answer 1

Take-home message: pipelines in Bash are slow (compared to process substitution); loops in Bash are slow; Bash are slow.

Without a setup script and dummy data to test run it, I don't really understand exactly what your script is trying to achieve at each step, so I can only suggest the following improvements:

Avoid unnecessary pipelines

• A lot of your code does cat $1 | grep Telegram | sed -e 's/"/ /g', which can be simplified to sed '/Telegram/!d; s/"/ /g' "$1", so you may want to save the result somewhere and extract from it when needed.
• awk '{ print $9 }' | cut -c27-30 can be combined into awk '{print substr($9, 27, 4)}'.
• In the command substitution that gets assigned to b, you have sed -e 's/;/ /g' | sed -e 's/=/ /g' which should really just be sed -e 's/;/ /g' -e 's/=/ /g' or even better just sed 's/[;=]/ /g'. You don't need the -e option if you're not combining expressions.
• kk=`wc -l trdata | awk '{ print $1 }'`: kk=`wc -l < trdata` can do the job just fine.

• cat grhostfinal | cut -c1-4 > grhostfinal1 is not as efficient as cut -c1-4 < grhostfinal > grhostfinal1

• sqlite3 test2.sqlite "select fecha from testxml4;" > data.csv cat data.csv | sort | uniq > data2.csv 

  is probably best done entirely in SQL:

  sqlite3 test2.sqlite "SELECT DISTINCT fecha FROM testxml4 ORDER BY fecha;" > data.csv 

Avoid loops if possible, or optimize for each iteration of the loop

• for i in `seq 1 $kk` do echo $a >> rsctype done 

  is much slower than

  printf "$a\n%.0s" `seq 1 $kk` >> rsctype 

  for small $kk, and much slower than

  yes "$a" | head -n "$kk" >> rsctype 

  for large $kk. See https://superuser.com/questions/86340/linux-command-to-repeat-a-string-n-times. (Not sure if you actually want to repeat the same strings, but that's what your code does.)

• for k in `cat data2.csv` do grep "$k" conjunto.csv >> quitar done diff quitar conjunto.csv | grep ">" | sed 's/^> //g' > diferencia.csv 

  looks like it could be done with just one diff? Maybe (haven't tested it):

  diff --changed-group-format='%>' --unchanged-group-format='' data2.csv conjunto.csv\ > diferencia.csv 

Non-performance-related notes

• You should wrap your cleanup command in a trap and put it at the beginning of the script so that it will always be executed unless the program is terminated by a SIGKILL:

  trap 'rm -f \ rs{host,cname,ctype} \ ttstamp tservice tformat trdata{,2,3,4} \ grhost{,2,final{,{1..4}}} \ data{,2}.csv conjunto.csv \ {a..c} quitar' \ 'EXIT' 

• Please don't rm -r if you're not deleting directories. This is a dangerous command if you're not careful. I used brace expansion to shorten the list of input files I need to type, but I'm not sure if you really need to create that many temporary files.

• You don't need to touch the files. Redirection will create the files if they don't exist.

I can probably offer more suggestions if I could try out the script. I'll add to this answer if I think of anything, but this should be enough for now.