Python add JPype to Jaydebeapi connection if 2 driver files?

Question

Below is my code to connect to Teradata through a JDBC connection using Jaydebeapi. When i run the query using the razorsql GUI, it takes only 15 seconds. When i run it through the code below, it took over 20 minutes just to run query1.

Is there anything wrong with the Jaydebeapi or can i make it faster by optimizing my query/using Jpype?

    #-*- coding: utf-8 -*-
    import jaydebeapi
    import jpype
    import pandas as pd
    import numpy as np
    import collections

    query_dict=collections.OrderedDict()


    connection = jaydebeapi.connect('com.teradata.jdbc.TeraDriver', ['my_db_name','my_username','my_password'], ['/Applications/drivers/tdgssconfig.jar','/Applications/drivers/terajdbc4.jar'],)     

    cur = connection.cursor()

    query_name_list=['query1','query2']

    query1= """select ......"""
    query2= """ select ....."""

    for i in query_list:
      query_dict[i]=locals()[i]

    print query_dict.keys()

    for index in range(len(query_list)):
      tera_query=query_dict.values()[index]

    cur.execute(tera_query)
    print "executing ... "

    result=cur.fetchall() 
    print "fetching results ... "

score 0 · Answer 1 · edited May 23 '17 at 12:23

0

I've already posted about some performance considerations.

Here again:

...fetching of large result sets with the JPype implementation causes some JNI calls for every single cell value which causes lot's of overhead. ...

Minimize the size of your resultset. Do aggregations using SQL functions.
Give the newest implementation of JPype1 a try. There have been some performance improvements.
Switch your runtime to Jython (JayDeBeApi works on Jython as well)
Implement the db queries and data extraction directly in Java and call the logic using JPype but with a interface not returning a large data set.
Try to improve JPype and JayDeBeApi code

BTW: The subject ".. if 2 driver files" is a bit misleading. The number of driver files is definitely not related to performance issues.

edited May 23 '17 at 12:23

Community

1
1

answered Nov 11 '15 at 07:57

bastian

1,122
11
23

How do I use jpype in my code? And not sure what you mean by switching to jython? – jxn Nov 11 '15 at 08:11
You don't have to use JPype directly. jaydebeapi uses it for you to do all the java db driver stuff. But you could participate in the JPype open source project to enhance it's performance so Jaydebeapi would benefit from your improvements. – bastian Nov 12 '15 at 10:19
Jython is a Python implementation running on the Java virtual machine. Jaydebeapi runs with both: Python and Jython. Jaydebeapi usually has much better performance on Jython as Jython has much better Java integration and JPype is not involved. – bastian Nov 12 '15 at 10:21

Python add JPype to Jaydebeapi connection if 2 driver files?

1 Answers1