3 times slower than node-vertica

[yuval-mntr]

I performed a test on the same query (30 million rows)
python seem to take roughly 3 times longer than node

There are no operation except execution and fetch, so the slowing factor has to be in the vertica-python lib

[bradleyhurley]

Just to clarify @yuval-mntr are you referring to the node-vertica project or running vsql from a node?

[yuval-mntr]

Hi
Yes that's correct
Same machine, node-vertica performs much faster executing exact same query on the exact same DB with same user.

Fetching code is line by line on both:
for row in cur.iterate():

and just append row to a list []

[bradleyhurley]

Your test may be flawed. What is the performance like when you use:
cur.fetchall()
The speed of iterating the result set and appending it to a list might be the bottleneck.

[yuval-mntr]

Hey, I started off with fetchall, fetchmany but they were too slow.
If I understand the lib implementation code correctly, fetchall uses cur.iterate
def fetchall(self): return list(self.iterate())
I do exactly the same in node.js, fetch line by line in a callback, and append to js array which takes 3 times faster.
I tried every possible way with vertica-python, all slow
I'm quite convinced the bottleneck is in the code behind
Thanks anyway

[cirgue]

I encounter the same issue. Does anyone know of a cause/fix?

[AndrewJudson]

We've seen a similar issue with pyodbc being much faster than this library. I'll look into this more when I have time, but in the meantime I would be interested if someone figures out the cause of this.

[alexjikim]

im sure there are other issues, but last time i checked the number of objects being created on a per row basis was a fair amount of the slowness. eg. the library currently unpacks bytes into a string first, before casting to another python object.

also, since this lib was published vertica has published an official native python client:
https://my.vertica.com/docs/8.1.x/HTML/python_client/tutorial.html

based only a brief test, it was much faster at fetching large numbers of rows. However it didn't run on mac osx and the package wasn't available on pypi.

[dennisobrien]

I have more questions than answers.

It looks like cursor.fetchall and cursor.execute have very different code paths. Is it possible that fetchall results in many more requests to the server compared to execute? I noticed that Pandas read_sql calls execute and not fetchall. This leads me to suspect execute is faster. (Edit: Ignore this nonsense.)

Does vertica_python support compression over the wire? I have heard that the Vertica ODBC and JDBC drivers do support this. For very large result sets where the execution time is network I/O bound, compression could make a big difference. But this is really just speculation. Here's a recent discussion that mentions the Vertica drivers and compression.
https://forum.vertica.com/discussion/comment/239763/#Comment_239763

cheers,
Dennis

[dennisobrien]

I just tested a query with pyodbc and the same query with vertica_python and looked at the traffic using wireshark. With vertica_python I can see the plain text of the result set returned from the server (ssl was not enabled) but with pyodbc the traffic is not in the clear, suggesting it is compressed.

The initial message from the client to the server is slightly different in these two cases, and the overall traffic patterns are very different. This all makes sense since they are different drivers, of course. I'm wondering if one of the bytes in the initial handshake is the client or server indicating it support compression.

And about the python driver release by Vertica, it's nice they are supporting the Python eco-system, but I haven't given it much time since it is Python 2.7 only.

[sitingren]

Hi folks,

Vertica support got feedback from a customer that they performed a test on three clients (vertica_python, pyodbc, and vertica_db_client). There is a noticeable performance difference between vertica_python and the other two clients. From our interaction, we found that if clients run on physical machines, three clients take nearly same amount of time to execute and fetch on the same query. But if clients run on virtual machines, vertica_python has 2~3 times performance decline.

This seems like a network issue in vertica_python, if you have the same experience (performance difference between virtual environments and physical ones), please leave a comment here. We'd also appreciated it if any network expert can help to find out the real problem.

Thank you!

[nicolaerosia]

can someone share their tests? I want to reproduce