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
truncate and write transaction scope
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm
.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
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm
In ODBC enterprise stage there is no option for transaction size ,it only has insert array size,DRS has the option for transaction size.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.
Are there any advantages of DRS over ODBC or ODBC over DRS in terms of speed.
Thanks
Arif
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.
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm
ThanksDSguru2B 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.
Your posts have been very helpful
Arif