分页的选择，对数据库或Web应用程序 [英] Paging choice, on database or in the web application

问题描述

我期待在重新调整我们网站的报告页面要快，我约上我应该怎么实现分页栅栏。我们的数据库很大，150万条记录。我们的大多数报告需要复杂的来自可达5〜10个表数据，因此每个人都可以有5个或6个连接和一些内选择。很显然，他们不是快速查询。

I am looking at reworking our website's reporting pages to be faster, and I am on the fence about how I should implement paging. Our database is large, > 150 million records. Most of our reports require complicated data that comes from up to 5 to 10 tables so each may have 5 or 6 joins and some inner selects. Obviously, they are not fast queries.

要实现分页在数据库方面，对于每个Web请求，我需要在数据库中查询当前页面（比如10000 100）中的行，但我也不得不再次查询数据库获取总数可能的行。这样一来，我基本上运行整个查询两次，因为查询得到的记录总数仍然需要做的所有的联接和内选择确定总的。

To implement paging on the database side, for each web request, I need to query the database for the rows for the current page (say 100 of 10,000), but I also have to query the database again to get the total number of possible rows. As a result, I am essentially running the whole query twice because the query to get the total number of records will still need to do all of the joins and inner selects to determine the total.

这岂不是更好地在会话中运行查询一次，返回所有结果，它缓存，和页面它使用网络code？我知我最初拉着更多的数据，但我只能在运行，可以采取30查询 - 60秒不是一次两次

Would it not be better to run the query once, return all results, cache it in session, and page it using web code? I known I am initially pulling more data, but I am only running a query that can take 30 - 60 seconds once instead of twice.

这是一种技术一般的问题，但如果它的事项，我使用.NET 4.0和Oracle 11g。

This is kind of a technology generic question, but in case it matters, I am using .net 4.0 and Oracle 11g.

推荐答案

你能得到的行数，同时为您的分页行，这样的分析：

you could get the number of rows at the same time as your paged rows with analytics like this:

SELECT [cols], nb_rows
  FROM (SELECT [cols], nb_rows, rownum r
          FROM (SELECT [cols], count(*) over() nb_rows
                  FROM [your_query])
         WHERE rownum <= :M)
 WHERE r >= :N

这将确保你只运行查询一次，就不会那么紧张你的网络带宽。

This would make sure you only run the query once and will be less stressful to your network bandwidth.

有关进一步的分析，请参阅分页查询速度在甲骨文。

For further analysis, see Speed of paged queries in Oracle.

缓存整个查询的结果可能是有意义的，如果：

Caching the result of the whole query might make sense if:

• 在用户经常向前/向后的结果集，而不需要刷新数据
• 网络带宽和可用的存储器（利用侧）足以（最有可能的，这将是唯一可能的，如果同时使用的用户数保持小）

这篇关于分页的选择，对数据库或Web应用程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！