Insert hanging against Data warehouse

ray.wurlod · Post by **ray.wurlod** » Tue Mar 18, 2008 3:34 pm

Sometmes you have to prove things to DBAs and don't have a baseball bat handy.

chulett · Post by **chulett** » Tue Mar 18, 2008 3:50 pm

And sometimes it's worth it to go get a dang baseball bat.

rmcclure · Post by **rmcclure** » Wed Mar 19, 2008 8:32 am

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.

rmcclure · Post by **rmcclure** » Wed Mar 19, 2008 8:41 am

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.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 19, 2008 5:07 pm

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.

rmcclure · Post by **rmcclure** » Mon Mar 24, 2008 12:37 pm

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.