bad performance with sybase

One Star

bad performance with sybase

Hello !
I made a small job, in witch i create a table, then i extract the data from an other table and with a tMap, i put it in the first table.
It seems to work, but it is very slow ! About 30 000 rows in 40 minutes ! The table i work on have about 410 000 rows, so it can be long...
I don't know where i go wrong... Is it because i work with sybase IQ and TOS is made for Sybase ASE ?
Help please !
CM
Moderator

Re: bad performance with sybase

Hello,
Sybase IQ is a very special database storing rows in lines.
This enable to have interesting performance when making select especially.
The default access for sybase IQ (and recommanded by sybase) is the ODBC. The native client or JDBC Access is for Sybase ASE and run with IQ in a compatibility mode => low performance.
You should use ODBC connection by default with sybase IQ.
Sybase IQ ODBC driver as also hidden options that you should change the values (row length etc.). Please refer to Sybase Documentation for further information.
As information managed divide by a 4 the extraction ODBC time by tuning this kind of parameters.
regards,
Benjamin
One Star

Re: bad performance with sybase

Thanks for your answer.
I don't understand : I must change my database connection in TOS, or configure Sybase IQ ?
I try to change the DB type from sybase to GenericODBC, but i don't know how to configure the connection...
regards,
CM
Moderator

Re: bad performance with sybase

You have to use ODBC connection with Talend.
Then you have to configure the ODBC settings in the ODBC declaration.
I do not have Access to a sybase IQ DB right now, but you have to deals with network packet size.
As fare as I can remember you have to set 3 variables in the advanced part of the ODBC driver.
regards,
Benjamin
One Star

Re: bad performance with sybase

So, I put my table in Metadata into "GenericODBC" type, i enter my login and password, and the Datasource name (that's the one in my control panel, ODBC source in windows, am i wrong ?)
I test the connection, it works !
But when i launch my job, it doesn't work, i have this error :
Exception in component tDBOutput_1
java.sql.SQLException: Source de données introuvable et nom de pilote non spécifié
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at test.contact_0_1.contact.tDBInput_1Process(contact.java:260)
at test.contact_0_1.contact.runJobInTOS(contact.java:869)
at test.contact_0_1.contact.main(contact.java:787)
I try almost everything, but now i am desperate ! ;+)
Can someone help me ?
One Star

Re: bad performance with sybase

Sybase IQ uses column based storage vs the typical row based storage of almost all other DW and RDMS procucts.
That said, how IQ stores the data really doesn't affect load times. The problem you have is that you are inserting rows one at a time.
Sybase IQ is a pure DW product not a tweeked RDMS. It is not designed for high volume OLTP type transactions, ie single row insert/update/delete activies.
To get the performance you need there are two fairly easy ways to get the data into IQ quickly.
1. Use the Sybase Bulk Load or Load Table feature. Create a text file from your source table data and a file that descibes the data structure, see Sybase IQ Documentation. Then execute a sql statement to load the data.
2. Sybase IQ is actually composed of two products Sybase SQL Anywhere and IQ. The SQL Anywhere part manages the DW metadata, think masterdb in ASE. The IQ part manages the actual data storage and the DSS query resolution. The SQL Anywhere part is the Catalog Store. You can create tables there and load data into them as you would a regular RDMS with the throughput you would expect. You can also wipe out your IQ database if you mess-up. Query performance isn't very good for data stored in the Catalog Store compared with the performance of queries resolved from the IQ data store. The way around it is to initially load data into the Catalog Store then move the data into the regular IQ data store by doing a 'insert <IQ data store table name> select * from <catalog store table name>' then delete the catalog store table. Or a safer methodology, if you have a SQL Anywhere or ASE database to use, is to load the data, row at a time, into a temporary table in either of them, then by defining the SQL Anywhere or ASE server as a remote server in IQ you can grab the data out of these remote tables using 'insert <IQ data store table name> location <remote server, dbname> select * from <remote temp table name>'. Either method will get the data into IQ quickly.
One Star

Re: bad performance with sybase

From Sybase IQ 15.0 onwards you can load data directly into the server from client. So the ETL can take advatage of this provided they take the BULK LOADER capability ( load table commad).
A faster alternative is you can direcly load data into the Sybase IQ using insert into location servername command provided you have a connection between the servers. I am doing this and load table command for copying data from Sybase ASE .
But for other database flavours, you can buy the enterprise connect from Sybase

Shamim