I'm working with a database and I want to start using LINQ To SQL with it. The database doesn't have any FKs inside of it right now for performance reasons. We are inserting millions of rows at a time to the DB which is why there aren't any FKs.
我正在使用数据库,我想开始使用LINQ To SQL。由于性能原因,数据库现在没有任何FK。我们一次向数据库插入数百万行,这就是为什么没有任何FK。
So I'm thinking I'm going to add nonenforced FKs to the database to describe the relationships between the tables for my LINQ To SQL but I don't want there to be a performance hit by adding nonenforced foreign keys.
所以我想我要在数据库中添加非强制FK来描述我的LINQ To SQL表之间的关系,但我不希望通过添加非强制外键来降低性能。
Does anyone know what the effect of this might be?
有谁知道这可能会产生什么影响?
Update: I'm using LINQ-To-SQL for the nonperformance intesive stuff. 80% of the data access is through stored procs on production. But for writing unit tests and other non performance critical tasks, LINQ-To-SQL makes data access really easy.
更新:我正在使用LINQ-To-SQL来处理非性能的东西。 80%的数据访问是通过生产中的存储过程进行的。但是,对于编写单元测试和其他非性能关键任务,LINQ-To-SQL使数据访问变得非常容易。
Update: Here is how you add a nonenforced FK
更新:以下是添加非强制FK的方法
ALTER TABLE [dbo].[ACI] WITH NOCHECK ADD CONSTRAINT [FK_ACI_CustomerInformation] FOREIGN KEY([ACIOI]) REFERENCES [dbo].[CustomerInformation] ([ACI_OI]) NOT FOR REPLICATION GO
ALTER TABLE [dbo]。[ACI] WITH NOCHECK ADD CONSTRAINT [FK_ACI_CustomerInformation] FOREIGN KEY([ACIOI])REFERENCES [dbo]。[CustomerInformation]([ACI_OI])NOT FOR REPLICATION GO
ALTER TABLE [dbo].[ACI] NOCHECK CONSTRAINT [FK_ACI_CustomerInformation] GO
ALTER TABLE [dbo]。[ACI] NOCHECK CONSTRAINT [FK_ACI_CustomerInformation] GO
4 个解决方案
#1
The answer can be different for different environments (data/logs on same drive, tempdb on same drive, lots of cache vs little, etc) so the best way to find this out is to benchmark. Create two identical databases, one with fk's and one without. Do your normal million-row-load into each database, and measure your transactions per second. That way you'll know for sure in your own environment.
对于不同的环境(同一驱动器上的数据/日志,同一驱动器上的tempdb,大量缓存与少量等),答案可能不同,因此找到这个的最佳方法是进行基准测试。创建两个相同的数据库,一个是fk,另一个是没有。将正常的百万行加载到每个数据库中,并测量每秒的事务数。这样你就可以在自己的环境中确定。
#2
Foreign keys will create non-clustered indexes in your table, which will improve performance of joins on foreign keys.
外键将在表中创建非聚簇索引,这将提高外键连接的性能。
Extra indexes will decrease the performance of your insert/update/delete/merge statements and will increase table sizes.
额外的索引会降低insert / update / delete / merge语句的性能,并会增加表的大小。
http://msdn.microsoft.com/en-us/library/ms191195.aspx
Even when created with NOT FOR REPLICATION the indexes are still present and SQL Server will need to maintain them.
即使使用NOT FOR REPLICATION创建索引仍然存在,SQL Server将需要维护它们。
In your case I would either: - use foreign keys and take performance hit or - not use foreign keys in production (goodbye data integrity) and run my tests against a copy of production database for which I would create foreign keys.
在你的情况下,我会: - 使用外键并获得性能命中或 - 不在生产中使用外键(再见数据完整性)并对我将创建外键的生产数据库副本运行我的测试。
#3
It may have some impact, especially at those volumes.
However I would test this on a similiar system first, so you can measure the impact, if any.
它可能会产生一些影响,特别是在那些卷上。但是我会首先在一个类似的系统上测试它,所以你可以测量它的影响,如果有的话。
To be honest though, I would probably use hand written stored procedures for this, so you can optimize them as required, instead of using LINQ to SQL.
说实话,我可能会使用手写的存储过程,因此您可以根据需要优化它们,而不是使用LINQ to SQL。
#4
I realize this is an old question, but I want to comment on how bad a practice it is to create a FK that is not enforced on existing data. If in fact there is a need for a foreign key, you need to fix any bad data before adding the foreign key (which should have been added at design time) not try to ignore it. All you are doing is masking your very serious data integrity problem by refusing to notice it and do something about it. There is the occasional need to do this due to changed requirements, but it should not be considered as a first choice of techniques when adding a foreign key to a table that has data. Finding and fixing the bad data should be.
我意识到这是一个老问题,但我想评论创建一个未对现有数据强制执行的FK的实践有多糟糕。如果实际上需要外键,则需要在添加外键(应该在设计时添加)之前修复任何不良数据,而不是试图忽略它。您所做的只是通过拒绝注意它并对其执行某些操作来屏蔽您非常严重的数据完整性问题。由于需求已更改,偶尔需要执行此操作,但在将外键添加到具有数据的表时,不应将其视为首选技术。找到并修复坏数据应该是。
Data that has no relationship to the PK is useless. If I had a order table with a customer id that no longer existed in the customer table, how would I know who ordered the product? Of course this is why the FKs should have been enforced from the beginning whether you did million row inserts or not. I do multi-million row inserts through SSIS on a daily basis to many many tables that have foreign keys, to use this as a reason for not setting them up in the first place indicates a lack of understanding of database design. Sacrificing your data integrity to speed is ALWAYS a poor idea. Without data integrity, your database is unreliable and therfore useless.
与PK无关的数据是无用的。如果我的客户表中不再存在客户ID的订单表,我怎么知道谁订购了产品?当然,这就是为什么FK应该从一开始就强制执行,无论你是否进行了数百万行插入。我每天通过SSIS对许多具有外键的表进行数百万行插入,使用它作为不首先设置它们的原因表明对数据库设计缺乏了解。牺牲数据完整性来加快速度总是一个糟糕的主意。没有数据完整性,您的数据库是不可靠的,因此无用。
#1
The answer can be different for different environments (data/logs on same drive, tempdb on same drive, lots of cache vs little, etc) so the best way to find this out is to benchmark. Create two identical databases, one with fk's and one without. Do your normal million-row-load into each database, and measure your transactions per second. That way you'll know for sure in your own environment.
对于不同的环境(同一驱动器上的数据/日志,同一驱动器上的tempdb,大量缓存与少量等),答案可能不同,因此找到这个的最佳方法是进行基准测试。创建两个相同的数据库,一个是fk,另一个是没有。将正常的百万行加载到每个数据库中,并测量每秒的事务数。这样你就可以在自己的环境中确定。
#2
Foreign keys will create non-clustered indexes in your table, which will improve performance of joins on foreign keys.
外键将在表中创建非聚簇索引,这将提高外键连接的性能。
Extra indexes will decrease the performance of your insert/update/delete/merge statements and will increase table sizes.
额外的索引会降低insert / update / delete / merge语句的性能,并会增加表的大小。
http://msdn.microsoft.com/en-us/library/ms191195.aspx
Even when created with NOT FOR REPLICATION the indexes are still present and SQL Server will need to maintain them.
即使使用NOT FOR REPLICATION创建索引仍然存在,SQL Server将需要维护它们。
In your case I would either: - use foreign keys and take performance hit or - not use foreign keys in production (goodbye data integrity) and run my tests against a copy of production database for which I would create foreign keys.
在你的情况下,我会: - 使用外键并获得性能命中或 - 不在生产中使用外键(再见数据完整性)并对我将创建外键的生产数据库副本运行我的测试。
#3
It may have some impact, especially at those volumes.
However I would test this on a similiar system first, so you can measure the impact, if any.
它可能会产生一些影响,特别是在那些卷上。但是我会首先在一个类似的系统上测试它,所以你可以测量它的影响,如果有的话。
To be honest though, I would probably use hand written stored procedures for this, so you can optimize them as required, instead of using LINQ to SQL.
说实话,我可能会使用手写的存储过程,因此您可以根据需要优化它们,而不是使用LINQ to SQL。
#4
I realize this is an old question, but I want to comment on how bad a practice it is to create a FK that is not enforced on existing data. If in fact there is a need for a foreign key, you need to fix any bad data before adding the foreign key (which should have been added at design time) not try to ignore it. All you are doing is masking your very serious data integrity problem by refusing to notice it and do something about it. There is the occasional need to do this due to changed requirements, but it should not be considered as a first choice of techniques when adding a foreign key to a table that has data. Finding and fixing the bad data should be.
我意识到这是一个老问题,但我想评论创建一个未对现有数据强制执行的FK的实践有多糟糕。如果实际上需要外键,则需要在添加外键(应该在设计时添加)之前修复任何不良数据,而不是试图忽略它。您所做的只是通过拒绝注意它并对其执行某些操作来屏蔽您非常严重的数据完整性问题。由于需求已更改,偶尔需要执行此操作,但在将外键添加到具有数据的表时,不应将其视为首选技术。找到并修复坏数据应该是。
Data that has no relationship to the PK is useless. If I had a order table with a customer id that no longer existed in the customer table, how would I know who ordered the product? Of course this is why the FKs should have been enforced from the beginning whether you did million row inserts or not. I do multi-million row inserts through SSIS on a daily basis to many many tables that have foreign keys, to use this as a reason for not setting them up in the first place indicates a lack of understanding of database design. Sacrificing your data integrity to speed is ALWAYS a poor idea. Without data integrity, your database is unreliable and therfore useless.
与PK无关的数据是无用的。如果我的客户表中不再存在客户ID的订单表,我怎么知道谁订购了产品?当然,这就是为什么FK应该从一开始就强制执行,无论你是否进行了数百万行插入。我每天通过SSIS对许多具有外键的表进行数百万行插入,使用它作为不首先设置它们的原因表明对数据库设计缺乏了解。牺牲数据完整性来加快速度总是一个糟糕的主意。没有数据完整性,您的数据库是不可靠的,因此无用。