0

I got a proprietary software (something like a DMS system) to process the documents in file system. The software provide very limited API to manipulate the contents of the documents in DMS but it allows to call command line while processing the document. I plan to write a python program to query the database and extract the corresponding database record of the document to enrich the content in DMS.

However, when the software process each single document, it will call the python program once. If there are millions of documents being processed, it will call the python program millions times. Is it effective to connect and close the database connection every time it process a document.? Is there any better approach to retrieve the record in database?

Sample Code of the python program

import MySQLdb
import sys

filename = str(sys.argv[1])

db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
cursor = db.cursor()
cursor.execute("SELECT * from example_table where filename='" + filename + "'")
data = cursor.fetchone()
db.close()
sys.stdout.write("Return is "+ str(data))
  • 3
    Oh boy, use prepared statements. – Alexander Jul 30 '17 at 17:28
1

For a high volume of requests, creating a new connection for each request is not likely to be efficient. Additionally, starting a new Python process for each request isn't terribly fast either: on my system, just starting CPython without doing anything takes 60ms to 10ms depending on caching and Python version. In contrast: starting a process in Bash, Perl, or a native program is about 10× faster.

Whether you can do better depends entirely on the interface assumed by your system. E.g. if the system can provide multiple filenames at once, you could do:

connect_db()
prepare_statement()
for filename in sys.argv[1:]:
  data = execute_statement(filename)
  print_result(data)
close_db()

But note that the number of command line parameters and the total size of command line parameters may be fairly limited.

Ideally, your script would read the filenames from STDIN, e.g. as one filename per line. This would allow your script to process all files within a single process.

If you cannot change the interface used by the system (i.e. invoke arbitrary executable with the filename as sole command line argument), then it may be faster to run your Python script as a TCP server on localhost. You can then write a simple wrapper program that sends the filename as a TCP request to the server and prints out the response. If the wrapper is not written in Python, the whole request may be over before a Python interpreter could completely start up.

Client pseudocode:

socket = connect()
write(socket, sys.argv[1])
flush(socket)
response = read(socket)
print(response)

Server pseudocode:

connect_db()
prepare_statement()

server = listen()

while True:
  client = accept(server)
  filename = read(client)

  data = execute_statement(filename)

  write(client, data)
  close(client)

close(server)

close_db()

Since the client is an extremely simple program, it is feasible to optimize start-up time by writing the client in C.

If you truly have a million documents that need to be batch-processed at once, even small optimizations like not using Python or processing multiple files per process may add up to many hours saved. But in general: first make sure that you have a performance problem, then benchmark the problematic solution, then try something and benchmark the attempted solution against the original implementation. Optimization is easy to get wrong without hard data.

3

You could write a daemon program that stays running in the background, and the daemon program then keeps the database connection open. The main program will then connect to the daemon program, preferably without SSL so that the connection will be as lightweight as possible. If the daemon is running on the same machine, encryption would be pointless.

You will need as lightweight as possible interface between the main program and the daemon.

You could have some kind of timeout shutdown mechanism in the daemon. E.g. if no requests have been processed in the last 10 seconds, shut down the daemon.

Obviously, you'll need some kind of mechanism to automatically start the daemon if it's not running.

Before doing this, however, do make sure your database connection is the bottleneck. Don't optimize prematurely!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.