It’s not terribly real, but it’s real enough to say “Yes, foreign keys have overhead” and follow that up with “No, you shouldn’t care, just add the keys.” The tests were done in VMware and the hardware in question is mid-2012 Retina MacBook Pro. We’re spending all of our time waiting for other parts of the system. When it comes down to single row performance, the difference is neglible.
I ran the test one last time with a batch size of 1 row, just to see how much difference there was in insert performance. How much do foreign keys affect single row inserts? And, let’s face it, 2 microseconds is a pretty minor price to pay for data integrity. These differences are hardly worth noting. When we remove the foreign key, inserting 5,000 rows takes ~26ms.Īlthough the difference looks big at 5,000 row batch sizes, each insert is taking, on average, 0.0072ms with the foreign key and 0.0052ms without the foreign key. It turns out that no matter what the page size it takes approximately 40ms to insert 5,000 rows with the foreign key in place. Although this isn’t strictly realistic, it’s better than timing a single batch of 1,000,000 inserts.
Sqlite foreign key not enforced code#
The test code inserts 1,000,000 rows in batches of 5,000 rows. Testing SQL Server insert speed with foreign keys The testing happened with a parent table that had 2 rows per page, 4 rows per page, and 8 rows per page. I varied the size of the filler column across multiple test runs, just to make sure I wasn’t biasing the test by using an artificially large parent table. The parent table has an int primary key and a fixed width filler column.
I figured that you were going to do the work, so I might as well do it. Estimates varied from “barely any” to “as much as 50% slower”. Do foreign keys hurt the performance of inserts? Okay, we all know that foreign keys do require some work, but the crazy people of the internet have wildly varying guesses as to just how much work is involved.
0 kommentar(er)
