The FK performance argument, which it seems you debunked, is often used as a reason not to have DRI in the database. ORM guys are but one example…preaching that all logic should be in other tiers and the database should be a dumb persistence mechanism. Probably the real reason folks say that DRI at the data tier is 50% slower is because they have a vested interest in removing all logic from the data tier. And this logic seems better handled in your ETL tool. Seems like the better use case is to squirrel away DRI failures somewhere and deal with them later vs failing the whole batch. So I’m not sure what Vertica’s paradigm is saving you. In my experience the Vertica pattern I see most often is 1)load the data 2)run a dummy query to see the key violations 3)fix key violations. Supposedly the reason is data loading performance would suffer. Vertica for instance does not enforce FKs during data loading but instead reports key violations at query time. I’ve learned a lot about SQL Server by studying how other database managers do things. More interesting is why people would NOT want to have DRI at the data tier. Perhaps the misconception stems from the fact that people aren’t properly indexing the keys or pick a suboptimal datatype. Estimates varied from “barely any” to “as much as 50% slower” “but the crazy people of the internet have wildly varying guesses as to just how much work is involved. Kendra says: Wanna know if your foreign keys are ‘trusted’? Check out our script here. In the real world, we’ve got a lot more than inserts happening, but it’s worth quantifying the cost of a foreign key lookup and realizing that it’s worth having foreign keys. 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. 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”. I just tested these SQLite foreign key examples on my system, using SQLite version 3.4.0, and they all work fine.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. INSERT INTO customers VALUES (null, 'FOOBAR', '200 Foo Way', 'Louisville', 'KY', '40207') INSERT INTO customers VALUES (null, 'ACME, INC.', '101 Main Street', 'Anchorage', 'AK', '99501') INSERT INTO salespeople VALUES (null, 'Barney', 'Rubble', 10.0) INSERT INTO salespeople VALUES (null, 'Fred', 'Flinstone', 10.0) If you'd like to test this SQLite foreign key example in your own SQLite database, here's some sample data for each of these tables: Next, define a SQLite table that has two foreign keys, one that relates a new orders table back to the customers table, and a second foreign key that relates the orders table back to the salespeople table:įOREIGN KEY(customer_id) REFERENCES customers(id),įOREIGN KEY(salesperson_id) REFERENCES salespeople(id)Īs you can see, the SQLite foreign key syntax is very similar to other databases. To show how this works, first define two database tables that don’t have any foreign keys: Here’s a quick SQLite foreign key example. The SQLite database does support foreign keys, and its foreign key syntax is similar to other databases. SQLite foreign keys FAQ: Can you show me how to define foreign keys in a SQLite database table design?
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |