Need to know the performance improvement on ODBC stage

[rajudx]

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

[ray.wurlod]

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.

[chulett]

Also note that if it's anything like Oracle, the APPEND will serialize the output. Not really an issue with zero records but something to check.

[rajudx]

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.

[chulett]

Do you have a SQL Server DBA there you could ask? They should be able to explain the inner workings to you.