SQL性能-更好地插入和引发异常或检查是否存在? [英] SQL Performance - Better to Insert and Raise Exception or Check exists?

问题描述

我正在考虑对我的代码中特别繁重的部分进行优化. 它的任务是将统计数据插入表中.其他程序正在严重破坏此数据.否则，我会考虑使用SQL大容量插入等.

I'm considering an optimisation in a particularly heavy part of my code. It's task is to insert statistical data into a table. This data is being hit a fair amount by other programs. Otherwise I would consider using SQL Bulk inserts etc.

所以我的问题是...

So my question is...

是否可以尝试插入一些数据，知道它可能(不太经常)为重复的行抛出SqlException吗?

异常的性能影响是否比插入之前检查每一行还要糟糕?

Is the performance hit of an exception worse than checking each row prior to insertion?

推荐答案

首先，我的建议是正确性而不是速度方面的错误.当您完成项目并进行分析时，您丢失了重要的时间，因此在插入行之前检查行是否存在，只有然后对其进行优化.

First, my advice is to err on the side of correctness, not speed. When you finish your project and profiling shows that you lose significant time checking that rows exist before inserting them, only then optimize it.

第二，我认为如果所有RDBMS中都存在重复项，则存在用于插入和跳过的语法，因此，这首先不应该成为问题.我尽量避免将异常作为常规应用程序流程的一部分，而将其留给真正的例外情况.也就是说，不要指望数据库中的异常来解决代码中的逻辑.在您的端(代码)上保持尽可能多的一致性，并让数据库异常仅指示真正的错误.

Second, I think there's syntax for inserting and skipping if there are duplicates in all RDBMS, so this shouldn't be a problem in the first place. I try to avoid exceptions as part of the normal application flow and leave them for truly exceptional cases. That is, don't count on exceptions in the DB to work around logic in your code. Maintain as much consistency on your end (code), and let DB exceptions indicate only true bugs.

这篇关于SQL性能-更好地插入和引发异常或检查是否存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！