truncate and write transaction scope

mab_arif16 · Post by **mab_arif16** » Thu Jun 08, 2006 11:10 am

Hi
In ODBC enterprise stage if you use the option truncate and write ,will these two operations be done in one transaction.I mean will the data disappear and reappear i.e will there be a commit after truncate and then data is written.
Is trucate just a faster delete?
Thanks
Arif

DSguru2B · Post by **DSguru2B** » Thu Jun 08, 2006 11:25 am

A delete is a logged operatioin and is at a row level. Truncate is at the table level and is much faster than a delete. When you use truncate and then insert update action, it wipes out the entire table and then inserts. The commit depends upon the transaction size you provide.

mab_arif16 · Post by **mab_arif16** » Thu Jun 08, 2006 11:34 am

DSguru2B wrote:A delete is a logged operatioin and is at a row level. Truncate is at the table level and is much faster than a delete. When you use truncate and then insert update action, it wipes out the entire table and then inserts. The commit depends upon the transaction size you provide.

.
Hi
Does defining isolation level to read commit ensures that the user sees the data only after truncate and write are complete.
What should be the array size if I only want to commit after truncate and loading all the rows are complete.
Thanks
Arif

DSguru2B · Post by **DSguru2B** » Thu Jun 08, 2006 11:41 am

The size should be 0. A 0 transaction size mean that the data will only be committed once the job finsihes successfull. It will roll back the data if the job aborts. But i believe the table will be truncated because it issues the truncate command even before inserting. I have to look into that myself. Never tried it.

mab_arif16 · Post by **mab_arif16** » Thu Jun 08, 2006 11:52 am

DSguru2B wrote:The size should be 0. A 0 transaction size mean that the data will only be committed once the job finsihes successfull. It will roll back the data if the job aborts. But i believe the table will be truncated because it issues the truncate command even before inserting. I have to look into that myself. Never tried it.

In ODBC enterprise stage there is no option for transaction size ,it only has insert array size,DRS has the option for transaction size.
Are there any advantages of DRS over ODBC or ODBC over DRS in terms of speed.
Thanks
Arif

DSguru2B · Post by **DSguru2B** » Thu Jun 08, 2006 11:59 am

Well then you will have to investigate on how to set the commit size in the odbc stage.
As per your second question, the odbc stage uses the generic driver to connect to the databases. Where as the DRS stage uses the native interface. The performance difference with both these stages is relative to the type of databases. I remember seeing a post here that mentioned a huge performance gain in Oracle when switched from odbc to drs.
In SQL server the performance gain isnt much. Same with UDB. It really depends.

mab_arif16 · Post by **mab_arif16** » Thu Jun 08, 2006 12:05 pm

DSguru2B wrote:Well then you will have to investigate on how to set the commit size in the odbc stage.
As per your second question, the odbc stage uses the generic driver to connect to the databases. Where as the DRS stage uses the native interface. The performance difference with both these stages is relative to the type of databases. I remember seeing a post here that mentioned a huge performance gain in Oracle when switched from odbc to drs.
In SQL server the performance gain isnt much. Same with UDB. It really depends.

Thanks
Your posts have been very helpful
Arif