odbc connectivity with Data services 4.2

[former_member214617]

Hi guys

i have very general question to ask...

presently i have dataflow which has one source table connects to query transform and further query transform connects with target table.. query transform has no filter, no order, no group by ... it is simple select * from source table ......... company where the source tables resides have given us their odbc driver to connect and extract same data... but it takes very long time to copy at our end.. i admit that data is more than 100 million (takes more than 4 hrs) records.. but could you please help me to debug what can be done to increase the performance and how can i check why it takes very long time...

ODBC connection set up in administrative tools->ODBC application.. is there any other way to set up the odbc....

when i run same query on the tool provided by them to connect with LIVE system.. it takes half of the time taken by data services to copy..

I have tried changing the dataflow properties between in-memory/ pageable with degree of parallelism.

what can i check on my end if anything wrong going at my end... before i tell anything to source company to see...

I would be very greatful if anyone can help me to understand same.

regards

[Former Member]

Hi,

Your not clear on something here.

Your were saying it is taking more time and at the same time your talking about ODBC connectivity.

However, as it is direct dump of table from one DB to another DB, DS will push this operation to the database completely.

So, you can increase commit size(Rows per commit) and number of loaders in the target table options.

This will definitely increase the performance.

Please let us know if this works.

Thank you.

Chiranjeevi

[Former Member]

Hi,

If its simple dump to target table try using Bulk Loader option that will be faster. ODBC tend to be slower, one thing you could try is use SQL native client to connect which is recommended by SAP.

Arun

[former_member214617]

Hi Arun

thanks for the reply.. could you please tell me more about sql native client what do you mean by that ?

regards

[Former Member]

Hi,

If your database is SQL server, install a SQL native client on the DS machine to connect to the DB. You don't need a ODBC for connection of DS with DB.

Check this, assuming your DB is SQL.

Arun

[former_member214617]

Hi Arun

Thanks for reply... sorry to tell your that i don't have access of source database.. it is from the vendor itself we got odbc drivers to connect source database.. there is no other way of selecting the data from source database..

that is why i asked forum expert view if there is any other way of using odbc drivers to see if anything can help to enhance the performance..

regards

[Former Member]

Hi,

ODBC performance tuning would be out of realm for DS development. You may have to go through some of the docs associated with it. Some thing like this - http://www.progress.com/~/media/DataDirect/Documents/ODBC/Tutorial/odbcperformance052004.pdf

Arun