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.
ODBC connector taking v.long to connect to source
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 24
- Joined: Mon Apr 24, 2006 3:34 am
- Location: USA
-
- Participant
- Posts: 24
- Joined: Mon Apr 24, 2006 3:34 am
- Location: USA
-
- Participant
- Posts: 24
- Joined: Mon Apr 24, 2006 3:34 am
- Location: USA
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.
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
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 24
- Joined: Mon Apr 24, 2006 3:34 am
- Location: USA
Would it be possible for you to post your odbc.ini Settings for this Connection?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>