Tag Info

New answers tagged

0

Use the OUTPUT clause of the INSERT statement to capture the identity values created in data. Then use that to write to relation. create table #data ( id int primary key , value int not null); if not exists (select id from data where value = 1337) insert into data (value) OUTPUT INSERTED.id, INSERTED.value INTO #data ...


0

Using the INSERT (SELECT)...ON DUPLICATE KEY UPDATE should permit you to visit these rows and update them with original values. Also you're not correct about REPLACE. This is the mechanism used by pt-table-sync to correct data drift. The documentation details a REPLACE == DELETE AND INSERT but that's not done within the scope of AUTO_INC. mysql> create ...


7

Are there any reasons for periodic/cyclic slow-down of insert performance? Yes. check point events. With a write intensive workload, big RAM server, as you describe, a large number of 'dirty' pages accumulate in memory. At the predetermined checkpoint interval all these dirty pages get written to disk, causing a spike of IO requests. This in turn slows ...


2

There is a situation in your current setup that would/could cause some slowdown related to an auto-incrementing key (IDENTITY, GETDATE(), NEWSEQUENTIALID()): under high-concurrency INSERT operations, there can be contention related to placing rows on the same page. This is called a "hotspot" and is one of the few drawbacks to auto-incrementing values as ...


7

SQL Server does not "rebalance the tree" as a periodic event. I have last heard this term in the context of Oracle. All that SQL Server does it increase the tree height when necessary. This is an event that happens only a few times in the entire existence of a B-tree. In a DML heavy workload there can be many small tree adjustments called page splits. These ...



Top 50 recent answers are included