在执行长时间运行的数据库任务时处理 Web 服务超时 [英] Handling Web Service Timeouts While Performing Long-Running Database Tasks

问题描述

我们其中一款产品的架构是典型的 3 层解决方案:

The architecture of one of our products is a typical 3-tier solution:

• C# 客户端
• WCF 网络服务
• SQL Server 数据库

客户端从网络服务请求信息.Web 服务访问数据库以获取信息并将其返回给客户端.

The client requests information from the web service. The web service hits the database for the information and returns it to the client.

问题来了.其中一些查询可能需要很长时间，而且我们不知道哪些查询会很慢.我们知道有些请求通常比其他请求慢，但是如果有足够的数据，即使是最简单的请求也会很慢.有时对大量数据使用查询或运行报告.查询只能在庞大的数据量减慢查询速度之前进行优化.

Here's the problem. Some of these queries can take a long, long time, and we don't know up-front which ones will be slow. We know some that are often slower than others, but even the simplest requests can be slow given enough data. Sometimes uses query or run reports on large amounts of data. The queries can be optimized only so far before the sheer volume of data slows them down.

如果数据库中的查询达到 SQL Server 中的最大查询超时，则数据库查询终止，Web 服务向客户端返回错误.这是明白的.我们可以处理这些错误.

If a query in the database hits the maximum query timeout in SQL server, the database query terminates, and the web service returns an error to the client. This is understood. We can handle these errors.

客户端正在等待 Web 服务调用完成.如果数据库调用需要很长时间，客户端可能会在调用 Web 服务时超时.客户端放弃，但数据库请求继续处理.此时，客户端与数据库不同步.数据库调用可能会也可能不会成功.可能有错误.客户永远不会知道.在某些情况下，我们不希望我们的用户发起另一个请求，这可能会导致在前一个请求已完成的情况下处于无效状态.

The client is waiting for the web service call to complete. If the database call takes a long time, the client may timeout on its call to the web service. The client gives up, but the database request continues processing. At this point, the client is out-of-synch with the database. The database call may or may not succeed. There may have been an error. The client will never know. In some cases, we don't want our users initiating another request that may result in an invalid state given the completion of the previous request.

我很想知道其他人是如何处理这个问题的.您使用了哪些策略来防止 Web 服务超时影响数据库调用?

I'm curious to see how others have handled this problem. What strategies have you used to prevent web service timeouts from affecting database calls?

我能想到的最好的想法是在某处创建一个实际的数据库层——在 Web 服务内部，附加到消息队列——某物.将每个查询卸载到另一个进程似乎过度.(再说一次，我们并不总是知道给定的请求是快还是慢.)

The best ideas I can come up with involve making an actual database layer somewhere-- inside the web service, attached to a message queue-- something. Offloading every single query to another process seems excessive. (Then again, we don't always know if a given request will be fast or slow.)

如果我们能将发出 HTTP 请求的行为与启动和运行数据库进程的行为分开，那就太好了.我在之前的一家公司看到过使用自定义服务器完成此操作，但它使用的是直接套接字通信，我宁愿避免用某些自定义应用程序替换 Web 服务.

It would be great if we could separate the act of making an HTTP request from the act of initiating and running a database process. I've seen this done with a custom server at a previous company, but it was using straight socket communication, and I'd rather avoid replacing the web service with some custom application.

请注意，鉴于我们处理的数据量，我们已经完成了查询优化.查询优化、索引等，只在数据量大的时候带你走这么多.有时事情只是需要很长时间.

Note that given the amount of data we deal with, we are all over query optimization. Query optimization, indexes, etc., only takes you so far when the volume of data is high. Sometimes things just take a long time.

推荐答案

我们最近使用的解决方案之一是将庞大的数据库进程分解为单独的并行操作.每个操作都要小得多，并且设计得尽可能高效.客户端启动操作，生成几个线程，并并行执行任何它们可以执行的操作.

One of the solutions we've used lately is to break apart huge database processes into separate parallel operations. Each operation is much smaller and designed to be as efficient as possible. The clients initiate the operations, spawn a few threads, and do whatever they can in parallel.

例如，我们将一些庞大的流程分解为一系列步骤，例如开始、处理 1 个工作块、完成和收集报告数据.Process Work 步骤可以并行运行，但在 Start 步骤完成之前它们无法启动.Finish 步骤需要等待所有 Process Work 步骤完成.

For example, we've broken apart some huge proceses into a series of steps like Start, Process 1 Chunk of Work, Finish, and Gather Report Data. The Process Work steps can run in parallel, but they can't start until the Start step completes. The Finish step needs to wait for all Process Work steps to complete.

由于客户在控制流程，因此客户可以准确报告其所在步骤的进度.

Since the client is controlling the process, the client can report progress on exactly which step it is on.

这篇关于在执行长时间运行的数据库任务时处理 Web 服务超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！