在查询结束之前开始查看查询结果 [英] Begin Viewing Query Results Before Query Ends

问题描述

让我们说我查询一个有500K行的表.我想开始查看保存结果集的提取缓冲区中的任何行，即使查询尚未完成.我想滚动通过提取缓冲区.如果我向前滚动得太远，我想显示一条消息，例如:在缓冲区缓冲区中最后排了一行.查询尚未完成".

• 在查询继续建立结果集的同时，可以使用fgets()读取读取缓冲区来完成此操作吗?这样做意味着多线程*

Oracle，Informix，MySQL或其他RDBMS是否可以提供FIRST ROWS提示指令以外的类似功能?

整个想法是能够在长查询完成之前开始查看行，同时显示可立即查看多少行的计数器.

我的建议可能需要对数据库服务器的体系结构进行根本性的更改，例如它们处理内部获取缓冲区的方式.锁定结果集，直到查询完成，等等.我建议的功能非常有用，特别是对于需要很长时间才能完成的查询.为什么要等到整个查询完成后才能开始查看某些结果，而查询继续收集更多结果呢?

解决方案

三个基本限制因素:

1. 查询的执行计划.如果执行计划的末尾有阻塞操作(例如排序或紧急假脱机)，则引擎无法在查询执行的早期返回行.它必须等待所有行都被完全处理，然后它将尽快将数据返回给客户端.时间本身可能会很可观，所以这部分内容可能适用于您所谈论的内容.不过，总的来说，您不能保证查询很快就会有很多可用.

2. 数据库连接库.从数据库返回记录集时，驱动程序可以使用服务器端分页或客户端分页.使用哪个可以并且确实会影响哪些行以及何时返回.客户端分页会强制一次返回整个查询，从而减少了在全部显示之前显示任何数据的机会.仔细使用正确的分页方法对于在查询生命周期中尽早显示数据的机会至关重要. p>

3. 客户端程序使用同步或异步方法.如果仅复制并粘贴一些用于执行查询的Web示例代码，则在查询仍在运行时，您将不太可能处理早期结果-而是该方法将被阻塞，直到所有内容都包含在内，您将一无所获当然，服务器端分页(请参阅第2点)可以缓解这种情况，但是在任何情况下，如果您不专门使用异步方法，您的应用程序将至少在短时间内被阻塞.对于阅读此书的任何人，他们都使用.Net，您可能要查看.Net Framework中的异步操作.

如果您正确地使用了所有这些功能，并使用FAST FIRSTROW技术，则您可能可以完成您正在寻找的某些事情.但是并不能保证.

Lets say I query a table with 500K rows. I would like to begin viewing any rows in the fetch buffer, which holds the result set, even though the query has not yet completed. I would like to scroll thru the fetch buffer. If I scroll too far ahead, I want to display a message like: "REACHED LAST ROW IN FETCH BUFFER.. QUERY HAS NOT YET COMPLETED".

• Could this be accomplished using fgets() to read the fetch buffer while the query continues building the result set? Doing this implies multi-threading*

Can a feature like this, other than the FIRST ROWS hint directive, be provided in Oracle, Informix, MySQL, or other RDBMS?

The whole idea is to have the ability to start viewing rows before a long query completes, while displaying a counter of how many rows are available for immediate viewing.

EDIT: What I'm suggesting may require a fundamental change in a DB server's architecture, as to the way they handle their internal fetch buffers, e.g. locking up the result set until the query has completed, etc. A feature like the one I am suggesting would be very useful, especially for queries which take a long time to complete. Why have to wait until the whole query completes, when you could start viewing some of the results while the query continues to gather more results!

解决方案

There are three basic limiting factors:

1. The execution plan of the query. If the execution plan has a blocking operation at the end (such as a sort or an eager spool), the engine cannot return rows early in the query execution. It must wait until all rows are fully processed, after which it will return the data as fast as possible to the client. The time for this may itself be appreciable, so this part could be applicable to what you're talking about. In general, though, you cannot guarantee that a query will have much available very soon.

2. The database connection library. When returning recordsets from a database, the driver can use server-side paging or client-side paging. Which is used can and does affect which rows will be returned and when. Client-side paging forces the entire query to be returned at once, reducing the opportunity for displaying any data before it is all in. Careful use of the proper paging method is crucial to any chance to display data early in a query's lifetime.

3. The client program's use of synchronous or asynchronous methods. If you simply copy and paste some web example code for executing a query, you will be a bit less likely to be working with early results while the query is still running—instead the method will block and you will get nothing until it is all in. Of course, server-side paging (see point #2) can alleviate this, however in any case your application will be blocked for at least a short time if you do not specifically use an asynchronous method. For anyone reading this who is using .Net, you may want to check out Asynchronous Operations in .Net Framework.

If you get all of these right, and use the FAST FIRSTROW technique, you may be able to do some of what you're looking for. But there is no guarantee.

这篇关于在查询结束之前开始查看查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！