One of datastage job is taking 55 mins for completing with zero records. Job is designed source as odbc and target also odbc sql server database.
In target odbc sql server stage write method defined as "Write" and write mode defined as "Append" .
When I changed property from "Write " to "Upsert" then same job is taking 6 seconds for processing zero records and is there any performance degrade because of using "Append" property on ODBE stage? We have similar other jobs with append but those jobs are taking very less time when processing zero records and the only difference is the job which is taking 55 mins that table having 100+ millions of records other than which taking less time those are having 5 to 10 millions of records. Please let me know if "append " write property will work based on table size in ODBC stage.
Thanks,
Raju
Need to know the performance improvement on ODBC stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
It will work, but under the covers the system has to position to the end of the physical structure in which data rows are stored. This will take some time, but 55 minutes does seem a lot.
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.
For finding the root cause we did some POC in other environment with less records (80 lacs).Same job has been executed in 2 mins and target table having 2 millions of records.We are thinking "append" property is taking some time for initiating insert statement when job is running.
Not sure how internally "append" property will work on SQL server database.i.e kind of checking page size, or any kind of temp table will create.
Thanks,
Raju.
Not sure how internally "append" property will work on SQL server database.i.e kind of checking page size, or any kind of temp table will create.
Thanks,
Raju.
Ran