一个大型数据库查询相对于许多小型数据库查询有什么优势
我继承了该应用程序，它的作用是从 4 个视图（其中包含 xml 文件）以 1000 条记录为单位获取数据，然后将它们写在 xml 文件中，所有这些都由具有 9 种不同可能性的类型参数分割。这意味着在最坏的情况下，每 1000 个该类型/视图组合将有 36 个与数据库的连接。
真实数据将存在 90.000 行，在本例中，需要 900 - 936 次从数据库中获取最多 1000 行。
现在我想知道将所有数据读入应用程序并让应用程序使用它来写入 900 多个文件会带来什么优势。
1000 行约为 800MB，90.000 行约为 81GB 正在传输的数据。
如果我们一次读完所有代码，则必须重写代码，尽管这更有意义，但这是一次性工作。 90.000 行之后，我们将不再使用此代码。花费 2、3 个小时重写代码来减少连接数量是否值得？
I inherited the app and what it does is get data from 4 views with an (xml file in it) in chunks of 1000 records then writes them down in an xml file all this split up by a type parameter that has 9 different possibilities. That means in a worst case there will be 36 connections to the database for each 1000 of that type/view combination.
The real data will exist of 90.000 lines and in this case 900 - 936 times fetching up to 1000 lines from database.
Now I am wondering what advantages it would give to read all data into the app and make the app work with this to write the 900+ files.
1000 lines is about 800MB, 90.000 lines is approx 81GB of data being transferred.
The code would have to be rewritten if we read it all at once and although it would make more sense this is a one time job. After the 90.000 lines, we will never use this code again. Is it worth it to spend 2, 3 hours to rewrite code that works to reduce the amount of connections this way?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
如果它是一次性的事情那么为什么要花精力去优化它呢？答：不。
不过，让我补充一下，回答您的一般性问题：大查询相对于大量小查询有什么优势：可能没有。如果你运行一个巨大的查询，你就会给中间件留下很多魔力，它可能会也可能不会很好地工作。
虽然同时拥有 36 个连接也不是最佳选择，但它可能比运行可返回 80 GB 数据的查询要好。理想的解决方案（如果您必须多次使用此代码）是重写它以分块获取数据，但不要同时打开大量连接。
If it's a one-time thing then why spend any effort at all optimizing it? Answer: no.
Let me add, though, in answer to your general question of what advantage does a big query have over lots of small ones: probably none. If you run a huge query you are leaving a lot of magic up to the middleware, it may or may not work well.
While having 36 simultaneous connections isn't optimal either, its probably better than running a query that could return 80 gigabytes of data. The ideal solution (if you had to use this code more than once) would be to rewrite it to get data in chunks but not leave lots of connections open simultaneously.
代码已经可以工作了吗？如果确实如此，那么我就不会花时间重写它。您会面临在代码中引入错误的风险。由于您将使用一次并且再也不会使用它，因此似乎不值得付出努力。
Does the code work already? If it does, then I wouldn't spend time rewriting it. You run in to the risk of introducing bugs in the code. Since you will use this once and never use it again, it doesn't seem like it is worth the effort.
如果我们谈论 SQL Server，大型查询（单个批次）相对于许多小型查询（请注意与您提出的问题相反的含义）的最大缺点是只能有一个查询每批次计划。
If we are talking SQL Server, the biggest disadvantage of a large query (a single batch) over many small ones (note the opposite sense to the question you are asking) is that there can only be one query plan per batch.
如果这是一次性工作我会拒绝。很多时候我做了一些我通常不会做的事情（光标），但只是因为它是一次性的工作。
问问自己，花 2 到 3 小时在已经有用但永远不会再使用的东西上是否有意义。但显然还有其他因素需要考虑。这会让您的生产数据库锁定 2-3 小时吗？
如果没有灾难性的副作用，我会建议使用你拥有的东西。
If it's a one time job I'd say no. Many times I have done things that i normally wouldn't (cursors) but ONLY because it was a one time job.
Ask yourself it it makes sense to spend 2 to 3 hours on something that already works and you will never use again. There are obviously other factors to take into account though. Like will this lock up your production database for 2-3 hours?
If there are no disastrous side effects I'd say use what you have.