DSXchange

Hello,

I have around 100 jobs, all simple extract jobs (no filter) to pull data from SQL Server (cloud instance) using ODBC connector. It is a requirement to have a encrypted connection & we are doing this using recommended ODBC driver and settings in DSN.

The problem is that jobs are taking pretty long to establish the connection (on a avg. more than a minute). The whole batch which used to finish in under an hour, is now taking almost 4-5 hours.

I have following ques:
1) Any options to improve job connection setup time ?

2) Is there a way to establish connection once and then pull data for multiple tables? I checked ODBC connector ; it doesnt allow multiple output links, so I cannot read more than one table in one connection.
Is it possible to achieve it any other way?


Thanks for your time.

Just curious, what was different back when it used to take under an hour? What all has changed since then?

Previous runtimes are without encryption, i.e with encryption turned off.

Have you enabled partitioned reads in order to speed up your extract?

JDBC also offers encryption in flight. You might want to see how to set that up and calculate the speed of it.

I haven't enabled partitioned reads, because that comes into play after the connection has been setup to source.

Currently this source connection is what is taking up most time. I want to improve this. Currently it takes about a min to establish the connection to source (with encryption on), which was in seconds earlier (no encryption).
With 100 jobs, this is becoming a pain point.

So the connection time is the only issue here or is data moving though the job in an encrypted state also slowing things down? I'm assuming both are at play here after trying to reconcile how 100 jobs each taking a minute longer to connect is increasing total runtime of them all by over 240 minutes rather than something closer to 100.

Craig,

I agree. The data movement in the job is slow too.
However I want to point out that there are some jobs where connection time is around 2 - 3 minutes. Hence the total connection time is slightly more than 100 mins.

Any ideas/ suggestions to improve these timings?

Would it be possible for you to post your odbc.ini Settings for this Connection?