why teiid jdbc performance is so slower than odbc

[lxjsj]

when I use teiid JDBC driver to get a whole table data, is it much more slower than using python by ODBC, is there any explanation?

[shawkins]

You are likely making somewhat different comparisons.  The odbc layer for the same operations has more overhead than JDBC - on the server it is implemented over our in-vm JDBC connection and uses the pg text data protocol.

[rareddy]

In previous testings we seen this at worst at 5% overhead over direct JDBC. So there must be some anomaly in the testing or Python library

[lxjsj]

this is the Teiid client log

09:39:44 [DEBUG ExecutorRunner-pool-3-thread-6 Y.processResultSet] Fetched Rows: 164443 Columns: 8 Exec: 76.253 Fetch: 96.784 sec

[van.halbert]

What testing tool are you using?

[shawkins]

I had assumed you were comparing an odbc client connection to Teiid vs a jdbc connection to Teiid, but that's not explicit.  Please define your full scenarios:

For Teiid:

- As Van is asking for, what app using the Teiid Client

- Teiid Client Version (and are you using a socket or in vm connection)

- What query are you issuing, and does Teiid perform any processing (you can provide the query plan)

- What source are you accessing and what client driver are you using in EAP or embedded.

And then define your comparison:

- what app is using the odbc client

- what version of python odbc are you using

- are you hitting Teiid or going directly to the source

- and are you issuing the same source query

[lxjsj]

By jdbc

1. We build Teiid Server 8.8.1

2. connect to Teiid by kettle table input, use Teiid 8.8.1 jdbc driver

3. select all data from one table(contains 160k records)

By python

import psycopg2

conn = psycopg2.connect("dbname=public user=myuser password=mypassword host=myhost port=5432")

[shawkins]

So you are comparing kettle over Teiid JDBC to a some custom logic in python using psycopg2 directly against postgresql?  Did you verify the query plan? You'll want to make your test scenarios as similar as possible - for example compare kettle using the postgresql JDBC client to access postgresql directly, to access Teiid's ODBC, and vs. kettle using Teiid JDBC.