title: Find my bottleneck with calling a command line tool with subprocess?
body: <p>I have a C++ command line executable (not made by me) that takes some parameters and then connects to a server and downloads data from a database and outputs it line by line to the command line, it can also be passed a -o myfile.txt parameter to output the lines to a file.</p>
  
  <p>One particular usage returns ~13million lines of data.</p>
  
  <p>Instead of calling this and outputting to a file I call it with the python subprocess module in order to process the output lines and insert into a local sqlite3 table I make, as this suits my needs much better than a text file.</p>
  
  <p>When I call the command line tool outside of python with -o to output to a file it takes 37 minutes due to the large volume of data needing to be processed and it has to download it over a network from the server with this data.</p>
  
  <p>When I call it in python to process the lines into SQL, it takes me ~90 minutes. Almost 2x as much time.</p>
  
  <p>I imagine my code has a bottleneck then and it can't process the incoming stdout lines as fast as it is receiving them, else it wouldn't have such a delay.</p>
  
  <p>Is there an obvious issue in my code or how can I troubleshoot to find where my bottleneck is?</p>
  
  <p>My code is here: <a href="https://pastebin.com/9DgmPdzj" rel="nofollow noreferrer">https://pastebin.com/9DgmPdzj</a></p>
  
  <p>The InitialiseTable() function and InitialConfigDone variable are used in order to create the table I need to store the data based on the first line of data content, I check if this InitialTable() has been done and if not then it just inserts the data. It does InitialiseTable for the first line then doesn't do it again.</p>
  
  <pre><code>def CaptureIntoSQL(host,table, InitialConfigDone=False):
      """Starts AdminDataCapture and redirects output to a table in a local SQL database"""
  
  
      command ="./MyCommandLineTool_x86-64_rhel6_gcc48_cxx11-vstring_mds -u {0} -p {1} -h {2} -t {3} --static -c".format(username,password,host,table)
  
      table_name ="{0}_Table{1}_{2}".format(host,table,GetDate())
      process = subprocess.Popen(shlex.split(command), stdout=subprocess.PIPE)
  
      while True:
          cmdoutput = process.stdout.readline().decode("utf-8")
          if cmdoutput == '' and process.poll() is not None:
              break
          if cmdoutput:
              data = cmdoutput.split("|") # split the output line into a list
              try:
                  Symbol = data[5]
                  if Symbol != "&lt;Symbol&gt;" and InitialConfigDone == False: # Run InitialiseTable() function to create the sqlite3 table, if it hasn't been done, once it's done once we don't want to do this again
                      try:
                          InitialiseTable(table_name,data)
                          InitialConfigDone = True
                          logger.info("Table created for {0}".format(table_name))
                      except:
                          logger.info("InitialiseTable(table_name,data) failed:\n table_name: {0} \n data: {1}".format(table_name,data))
  
  
                  elif Symbol != "&lt;Symbol&gt;" and InitialConfigDone == True: # If we've already ran InitialiseTable() begin inserting data.
                      Permission = data[3]
                      exchangeCode = data[5].split(".")[-1]
                      if exchangeCode == "":
                          exchangeCode = "."
                      else:
                          pass
                      valuelist = data[7::2]
                      valuelist.insert(0, Permission)
                      valuelist.insert(0, exchangeCode)
                      valuelist.insert(0, Symbol)
                      InsertQuery = "INSERT INTO '{0}' VALUES{1}".format(table_name, vtuple) # my query to insert into my sqlite table
                      try:
                          c.execute(InsertQuery, valuelist)
                      except:
                          pass
  
              except:
                  pass
      conn.commit()
  </code></pre>