ASP。2008年NET/SQL性能问题

We've developed a system with a search screen that looks a little something like this:

我们开发了一个搜索屏幕的系统看起来有点像这样:

http://demo1.nsourceservices.com/images/logos/*1.png

http://demo1.nsourceservices.com/images/logos/*1.png

As you can see, there is some fairly serious search functionality. You can use any combination of statuses, channels, languages, campaign types, and then narrow it down by name and so on as well.

正如您所看到的，有一些相当重要的搜索功能。您可以使用状态、通道、语言、活动类型的任何组合，然后按名称等进行缩小。

Then, once you've searched and the leads pop up at the bottom, you can sort the headers.

然后，一旦你搜索过了，而引线在底部弹出，你可以对标题进行排序。

The query uses ROWNUM to do a paging scheme, so we only return something like 70 rows at a time.

查询使用ROWNUM执行分页方案，因此每次只返回大约70行。

The Problem

这个问题

Even though we're only returning 70 rows, an awful lot of IO and sorting is going on. This makes sense of course.

虽然我们只返回70行，但是仍然有大量的IO和排序。这当然说得通。

This has always caused some minor spikes to the Disk Queue. It started slowing down more when we hit 3 million leads, and now that we're getting closer to 5, the Disk Queue pegs for up to a second or two straight sometimes.

这总是会导致磁盘队列出现一些小的峰值。当我们达到300万次领先时，它的速度开始减慢，现在我们接近5次，磁盘队列有时会连续一到两秒。

That would actually still be workable, but this system has another area with a time-sensitive process, lets say for simplicity that it's a web service, that needs to serve up responses very quickly or it will cause a timeout on the other end. The Disk Queue spikes are causing that part to bog down, which is causing timeouts downstream. The end result is actually dropped phone calls in our automated VoiceXML-based IVR, and that's very bad for us.

这实际上仍然是可行的，但是这个系统有另一个具有时间敏感的过程的区域，为了简单起见，我们说它是一个web服务，它需要非常快速地提供响应，否则会导致另一端超时。磁盘队列峰值导致该部分陷入停滞，从而导致下游超时。最终的结果是在基于自动语音输出的IVR中丢失电话，这对我们来说非常糟糕。

What We've Tried

我们已经试过

We've tried:

我们已经试过:

• Maintenance tasks that reduce the number of leads in the system to the bare minimum.
• 将系统中的引线数量减少到最少的维护任务。
• Added the obvious indexes to help.
• 添加了明显的索引帮助。
• Ran the index tuning wizard in profiler and applied most of its suggestions. One of them was going to more or less reproduce the entire table inside an index so I tweaked it by hand to do a bit less than that.
• 在profiler中运行索引调优向导，并应用了它的大部分建议。其中一个将或多或少地复制索引中的整个表，所以我手动调整它，以做得更少一点。
• Added more RAM to the server. It was a little low but now it always has something like 8 gigs idle, and the SQL server is configured to use no more than 8 gigs, however it never uses more than 2 or 3. I found that odd. Why isn't it just putting the whole table in RAM? It's only 5 million leads and there's plenty of room.
• 向服务器添加更多的RAM。它有点低，但是现在它总是有8个gigs空闲，SQL服务器被配置为不超过8个gigs，但是它从不超过2或3个gigs。我发现很奇怪。为什么不把整个桌子都放在内存里呢?只有500万线索，还有很大的空间。
• Poured over query execution plans. I can see that at this point the indexes seem to be mostly doing their job -- about 90% of the work is happening during the sorting stage.
• 将查询执行计划填满。我可以看到，此时索引似乎主要在做它们的工作——大约90%的工作都发生在排序阶段。
• Considered partitioning the Leads table out to a different physical drive, but we don't have the resources for that, and it seems like it shouldn't be necessary.
• 考虑过将引线表划分为不同的物理驱动器，但是我们没有相应的资源，似乎不需要这样做。

In Closing...

在关闭……

Part of me feels like the server should be able to handle this. Five million records is not so many given the power of that server, which is a decent quad core with 16 gigs of ram. However, I can see how the sorting part is causing millions of rows to be touched just to return a handful.

我觉得服务器应该能够处理这个问题。考虑到服务器的强大功能，500万条记录并不算多，这是一个不错的四轴核心，有16 gb的ram。但是，我可以看到排序部分是如何导致数百万行被触摸的，只是返回一些。

So what have you done in situations like this? My instinct is that we should maybe slash some functionality, but if there's a way to keep this intact that will save me a war with the business unit.

在这种情况下你做了什么?我的直觉是，我们或许应该削减一些功能，但如果有办法保持这个功能不变，我就可以避免与业务部门的战争。

Thanks in advance!

提前谢谢!

3 个解决方案

#1

Database bottlenecks can frequently be improved by improving your SQL queries. Without knowing what those look like, consider creating an operational data store or a data warehouse that you populate on a scheduled basis.

通过改进SQL查询，可以经常提高数据库瓶颈。在不知道它们是什么样子的情况下，可以考虑创建一个可操作的数据存储库或您按计划填充的数据仓库。

Sometimes flattening out your complex relational databases is the way to go. It can make queries run significantly faster, and make it a lot easier to optimize your queries, since the model is very flat. That may also make it easier to determine if you need to scale your database server up or out. A capacity and growth analysis may help to make that call.

有时候，将复杂的关系数据库扁平化才是正确的选择。它可以使查询运行得更快，并且使优化查询变得更容易，因为模型非常扁平。这也可能使您更容易确定是否需要扩展数据库服务器。能力和增长分析可能有助于这一呼吁。

Transactional/highly normalized databases are not usually as scalable as an ODS or data warehouse.

事务性/高度规范化的数据库通常不像ODS或数据仓库那样具有可扩展性。

Edit: Your ORM may have optimizations as well that it may support, that may be worth looking into, rather than just looking into how to optimize the queries that it's sending to your database. Perhaps bypassing your ORM altogether for the reports could be one way to have full control over your queries in order to gain better performance.

编辑:您的ORM可能还支持优化，这可能值得研究，而不是仅仅研究如何优化它发送到数据库的查询。为了获得更好的性能，也许完全绕过ORM以获得报告可能是一种完全控制查询的方法。

#2

Consider how your ORM is creating the queries. If you're having poor search performance perhaps you could try using stored procedures to return your results and, if necessary, multiple stored procedures specifically tailored to which search criteria are in use.

考虑ORM是如何创建查询的。如果您的搜索性能很差，那么您可以尝试使用存储过程返回结果，如果需要，还可以尝试根据使用的搜索条件定制多个存储过程。

#3

1. determine which ad-hoc queries will most likely be run or limit the search criteria with stored procedures.. can you summarize data?.. treat this
   app like a data warehouse.
2. 确定最可能运行的临时查询或使用存储过程限制搜索条件。你能总结数据?。把这个应用程序当作数据仓库。
3. create indexes on each column involved in the search to avoid table scans.
4. 在搜索中涉及的每个列上创建索引，以避免表扫描。
5. create fragments on expressions.
6. 在表情上创建片段。
7. periodically reorg the data and update statistics as more leads are loaded.
8. 定期重新组织数据并更新统计数据，因为会加载更多的引线。
9. put the temporary files created by queries (result sets) in ramdisk.
10. 将查询(结果集)创建的临时文件放在ramdisk中。
11. consider migrating to a high-performance RDBMS engine like Informix OnLine.
12. 考虑迁移到像Informix在线这样的高性能RDBMS引擎。
13. Initiate another thread to start displaying N rows from the result set while the query
    continues to execute.
14. 启动另一个线程，在查询继续执行时从结果集中显示N行。