Connection: Access too SLOOOOW

      Connection: Access too SLOOOOW


      I work with ETL 3.2 CE and I have defined connection to Access.
      Lines from log file (..tomcat/stdout_yyyymmdd.log) describing connection process follows:

      Source Code

      1. 2014-03-05 23:30:00,159 INFO [extract pUD_2013] ( - Starting execution of extract pUD_2013
      2. 2014-03-05 23:30:00,159 INFO [extract pUD_2013] ( - Execution Parameters: #logLimit:100 ; #failOnError:true
      3. 2014-03-05 23:30:00,160 INFO [extract pUD_2013] ( - Starting source execution: pUD_2013
      4. 2014-03-05 23:30:00,161 INFO [extract pUD_2013] ( - Data retrieval from extract pUD_2013
      5. 2014-03-05 23:31:03,363 INFO [extract pUD_2013] ( - Lines read from extract pUD_2013: 20
      6. 2014-03-05 23:31:03,364 INFO [extract pUD_2013] ( - Finished retrieving of source pUD_2013.
      7. 2014-03-05 23:31:03,563 INFO [extract pUD_2013] ( - Finished execution of extract pUD_2013 with status: Completed successfully

      More than one minute (63 seconds!) to read 20 lines from a table (simple SQL command: SELECT, total 8.000 records).
      It could not be compared to standard ODBC connection which would retrieve data within a moment!

      Question is:
      why is Access connection in ETL3.2 CE so slow?

      Any suggestions? Hints? Ideas?
      Any experience welcomed!
      Small step forward:

      I found a configuration of drivers used for data connection.
      Look at ..\Jedox\Palo Suite\tomcat\webapps\etlserver\config\standard\component.xml
      where jdbc drivers are specified.
      sun.jdbc.odbc.JdbcOdbcDriver is used for MS Access connections.

      The way how to overcome slow connection would be to test different driver.

      Part of the components.xml follows:

      Source Code

      1. <component name="GenericDB" jdbc="" driver="" class="com.jedox.etl.components.connection.GenericDBConnection"/>
      2. <component name="File" jdbc="file" class="com.jedox.etl.components.connection.FileConnection"/>
      3. <component name="Postgresql" jdbc="postgresql" driver="org.postgresql.Driver" class="com.jedox.etl.components.connection.RelationalConnection"/>
      4. <component name="Mysql" jdbc="mysql" driver="com.mysql.jdbc.Driver" class="com.jedox.etl.components.connection.MySQLConnection"/>
      5. <component name="Sapdb" jdbc="sapdb" driver="" class="com.jedox.etl.components.connection.RelationalConnection"/>
      6. <component name="Directory" jdbc="directory" class="com.jedox.etl.components.connection.DirectoryConnection" />
      7. <component name="Db2" jdbc="db2" driver="" class="com.jedox.etl.components.connection.RelationalConnection"/>
      8. <component name="Oracle" jdbc="oracle:thin" driver="oracle.jdbc.driver.OracleDriver" class="com.jedox.etl.components.connection.OracleConnection"/>
      9. <component name="Sqlserver" jdbc="sqlserver" driver="" class="com.jedox.etl.components.connection.SQLServerConnection"/>
      10. <component name="Odbc" jdbc="odbc" driver="sun.jdbc.odbc.JdbcOdbcDriver" class="com.jedox.etl.components.connection.ODBCConnection">
      11. <!-- Windows Locale possible values: "", "en", "de", "fr", ..., etc. Empty value ("") means no localization will be applied. -->
      12. <parameter name="WindowsLocale"></parameter>
      13. <parameter name="Backend">none</parameter>
      14. </component>
      15. <component name="Access" jdbc="odbc" driver="sun.jdbc.odbc.JdbcOdbcDriver" class="com.jedox.etl.components.connection.AccessConnection">
      16. <parameter name="OdbcDriver">MicroSoft Access Driver (*.mdb)</parameter>
      17. <parameter name="WindowsLocale"></parameter>
      18. </component>
      Hi rmachu,

      actually yes this would be a possibilty. Try to check whether a jdbc Type 4 driver exists for Access.

      If the access db is no datasource that you query on a regular basis (for instance to get historical values) then I would advise to generatee csv files and integrate them this way

      Post hoc, non est propter hoc