Insert hanging against Data warehouse
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.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'm waiting for the AS/400 group to get back to me.
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.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.
I am waiting for the AS/400 group to get back to me.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.