What is the difference between inserting duplicate rows and inserting new rows

Question

When I use voltdb (an in-memory database), I found a strange phenomenon: Inserting duplicate raws (500ms) takes more time than inserting new columns (10 ms ),and the table has been partitioned.In my knowledge, inserting a duplicate data should return faster because there is no need to do a real insert action.

Answer 1

It looks like what you are measuring is the response time back to the client, which may not reflect the execution time. It may depend on what the client does with success results vs. error results, or it may just be random network latency, or how many other transactions were queued ahead of your requests when you sent them.

To measure the actual time it takes to insert a new record or to attempt to insert a duplicate record (and for it to fail and return a unique constraint violation), you may need to isolate and repeat enough transactions to get a good measurement from the built-in statistics.

Supposing you have a partitioned table with a primary key. Here is how I would measure this:

1. Call "exec @Statistics PROCEDUREPROFILE 1;", but you can ignore these initial results.
2. Insert 50-100 unique records into your table by calling the TABLENAME.insert procedure.
3. Call "exec @Statistics PROCEDUREPROFILE 1;" and look at the avg_execution_time column for the TABLENAME.insert procedure. This average should be just for the unique inserts you did between calls to @Statistics.
4. Insert 50-100 duplicate records using the TABLENAME.insert procedure.
5. Call "exec @Statistics PROCEDUREPROFILE 1;" and look at the avg_execution_time column for the TABLENAME.insert procedure. This average should be just for the duplicate inserts you did between calls to @Statistics.

I haven't tested unique inserts vs. insert constraint violations for speed myself. In general, inserts are fast, regardless of outcome, but I suspect your instinct is correct and the failed duplicate inserts may execute faster since it has to check either way, but if it finds a duplicate there is no need to insert.

Disclaimer: I work for VoltDB.