In SQL Server how is…
Sp:
CREATE PROCEDURE insertToTable
@field1 VARCHAR(256), @field2 varchar(256), @field3 varchar(256)
AS
BEGIN
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM my_table WHERE field1 = @field1)
INSERT INTO my_table
(field1, field2, field3)
VALUES (@field1, @field2, @field3);
ELSE
THROW 50000, 'xxxxxx', 1;
END
GO
Table:
CREATE TABLE my_table (
field1 VARCHAR(256) NOT NULL,
field2 VARCHAR(256) NOT NULL,
field3 VARCHAR(256) NOT NULL
);
CREATE INDEX idx_field1 ON my_table(field1);
the above faster than the below?
Sp:
CREATE PROCEDURE insertToTable
@field1 VARCHAR(256), @field2 varchar(256), @field3 varchar(256)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO my_table
(field1, field2, field3)
VALUES (@field1, @field2, @field3);
GO
Table:
CREATE TABLE my_table (
field1 VARCHAR(256) NOT NULL,
field2 VARCHAR(256) NOT NULL,
field3 VARCHAR(256) NOT NULL
);
CREATE UNIQUE INDEX idx_field1 ON my_table(field1);
Sample input:
field1: F56yCgZ9AEm9aFpTyjwhERtqNeglYEow field2: BD84CE2A514316164B7448C804B178AD8F6F597E8EC6F25F4D6E36287259C65F67E7206E82A4F8EFD2389C0821C0C70E8278DC5F166D220356B5A15A091A6C17$0 field3: A18E9049117A77E6A4D41C6CA3FFDEA65D842BF1F57705405B4E66969531D93D
The input is generated on the fly by the web application and using prepared statements. I use Jmeter to generate requests to my web app.
With UNIQUE
index, insert performance degrades after 100K inserts and gets worse.
With NON UNIQUE
index and a manual check with IF NOT EXISTS SELECT
, performance is constant even with millions of records inserted.
The values are unique enough that there never is a duplicate generated. Even after a few million values inserted.
Best Answer
FINAL UPDATE:
It's the INSERT that's really slowing things down.
When a Unique index is in place, with every new record you add SQL has to check if the value already exists. As the table grows, the amount of cross-referencing increases. A non-unique index will require no cross-referencing so performance is constant.
Unique indexes are usually faster for SELECT statements but that comes at a cost when updating a table.
Below is why the SELECT can sometimes be slower on a Unique Index
I've partially recreated your situation to the point where I think it is down to combination of Parameter sniffing and SQL prefers using the NON-UNIQUE index on a HEAP.
Set up 2 test tables, one of them is a heap (just like your table).
Review the footprint of the Indexes, on the HEAP the UNIQUE index has a smaller footprint than the NON-UNIQUE index. (perhaps the pages of the NON-UNIQUE index contain extra - possibly useful - information)(note: after running the above code multiple times the page count doesn't differ, probably due to caching, alter the "GO 30000" to rectify the issue.)
Now query the tables with literals and variables.
For some reason SQL prefers the NON UNIQUE index on a HEAP when performing SEEK operations.
Here's what I think is going on. When the Non-Unique index has more Pages then the corresponding Histogram in the STATS has more STEPS, run below code.
The additional STEPS create a more granular view of the underlying Index, so the Optimiser (knowing that the EQ_ROWS is always 1) is getting a better Cardinality Estimate from the Non-Unique index.