Page 2 of 2
Posted: Tue Mar 18, 2008 3:34 pm
by ray.wurlod
Sometmes you have to prove things to DBAs and don't have a baseball bat handy.
Posted: Tue Mar 18, 2008 3:50 pm
by chulett
And sometimes it's worth it to go get a dang baseball bat.
Posted: Wed Mar 19, 2008 8:32 am
by rmcclure
chulett wrote: the next suggestion is to pursue the 'split the jobs' suggestion and see if that changes the behaviour. Land the file in the first job to a flat file and then load from there.
I split the job into 2 parts part one pulls from the AS/400 and writes to a sequential file. The second job pulls from the sequential file and write to SQl server. As suspected the pull from the AS/400 takes forever, while the write to SQL takes seconds.
I'm waiting for the AS/400 group to get back to me.
Posted: Wed Mar 19, 2008 8:41 am
by rmcclure
chulett wrote:Nothing wrong with either size, though they do seem small. Had to hit the road, but yes the next suggestion is to pursue the 'split the jobs' suggestion and see if that changes the behaviour. Land the file in the first job to a flat file and then load from there.
As suggested I split the job into 2 parts. The pull from the AS/400 takes forever and the push to SQL server takes seconds. It would seem the problem in on the AS/400 side of things.
I am waiting for the AS/400 group to get back to me.
Posted: Wed Mar 19, 2008 5:07 pm
by ray.wurlod
It's probably the connection to AS/400. Recall that every file on the AS/400 can be a "table", so when the connection starts and it asks the AS/400 for a list of tables, this can be huge. You can (should) configure the client software (or ODBC DSN definition) with a file name filter.
Posted: Mon Mar 24, 2008 12:37 pm
by rmcclure
We have found a work around for this issue.
After trying difference test we narrowed the problem to the user defined SQL statement.
It was:
Select .....
Where Date1 > LastRunDate
Union
Select.....
Where Date2 > LastRunDate
If we ran the select before the "UNION" it complteed in 16 seconds.
If we ran the select after the "UNION" it completed in 4 minutes 30 seconds.
If we ran the two including the UNION it ran for an hour and 20 minutes.
If we used a "Date1 > LastRunDate or Date2 > LastRunDate" we killed the job after 1 hour and 20 minutes because it was obviously no faster.
Our work around was to do it using 2 jobs.
Job 1 executes the first half of the SQL statement doing a truncate then insert. Job 2 then executes the second half of the SQL statement doing an update/insert on the table. The 2 jobs together complete in around 6 minutes.