Sql updating large number of rows

05 Mar

For example, Neteeza uses a “logless” implementation and so does many no SQL database systems and HADOOP/HIVE.

My SQL with the My ISAM engine also allows non logged operations.

There are of course cases where you will hit the “wall”, but those are largely mitigated in MPP systems or other sharded deployments that have more than one transaction log.

Third, there are cases where UPDATE statements are actually faster than (row logged) INSERT statements.

Third, it is perfectly conceivable that you are running a warehouse database that does NOT need to serialize DML operations or may even write entire blocks directly to the database instead of the transaction log.

Such systems simply do not have the above bottleneck.

sql updating large number of rows-70sql updating large number of rows-70sql updating large number of rows-63sql updating large number of rows-61

Second, we have seen that even when UPDATE is fully row logged, the transaction log “wall” is very far away on proper hardware and not much of a concern to 99% of all the installations out there.

Myth: INSERT of row logged data is faster than UPDATE of row logged data Reality: Let us first settle one thing which I will type on its own line and in red to make it easy to remember: An in-place, row logged, UPDATE operation on a non compressed page is than doing a row logged INSERT of the same data. Because the INSERT operation has to allocate new physical structures in the index, while the UPDATE can simply reuse database pages without having to allocate more space.

And here are the numbers to prove it where I am running INSERT vs.

UPDATE of large dataset in SQL Server (smaller is faster): True: if your UPDATE statement has to do the INSERT/DELETE trick, it will likely be slower.

But if you are NOT changing the row size and you get the in-place UPDATE, it might just be FASTER to run an UPDATE than an insert.