Queries made with ODBC are really slow...

Moderator: NorbertKrupa

Post Reply
User avatar
usli06
Intermediate
Intermediate
Posts: 93
Joined: Wed Jan 25, 2012 4:53 am

Queries made with ODBC are really slow...

Post by usli06 » Tue Feb 28, 2012 3:09 am

Hello,

We are having a performance issue when running queries via an ODBC connection. When we run the queries in vsql they perform ok. Any idea why this may be happening?

Thanx!

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Queries made with ODBC are really slow...

Post by JimKnicely » Wed Feb 29, 2012 2:14 pm

Hi there,

Make sure your ODBC connection's transaction isolation level is the same as your Vertica database server. The default isolation level for an ODBC connection is "Serializable" whereas the default isolation level for the Vertica database is "Read Committed". Try changing your ODBC setting to match the database (if they're not already the same).

For Windows you'll need to go to the ODBC Data Source Administrator. Make sure the "Read Committed" radio button is selected. (Depending on your Windows/driver version, this field may be a drop down).
  • Image
For Unix/Linux you'll need to edit/modify your odbc.ini file so that a parameter called TxnReadCommited is part of the DSN entry. Set the parameter equal to 1 which is the transaction isolation mode READ COMMITTED.

Here is an example DSN entry from my odbc.ini file:

Code: Select all

[vertica_prod01]
Driver = Vertica01
Servername = 10.255.100.37
Database = vertica_prd01
Port = 5433
UserName = cognos
Password = thisisnotthepassword
TxnReadCommitted = 1
ConnSettings = SET SEARCH_PATH TO "$user", cognos, public, v_catalog, v_monitor, v_internal
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “ODBC”