是否可以在打开ADOQuery时显示记录？

I have an ADOQuery linked to a DBGrid by a DataSource.
The ADOQuery and the DataSource are in a DataModule and the connection is in another form.

我有一个由DataSource链接到DBGrid的ADOQuery。 ADOQuery和DataSource位于DataModule中,连接采用另一种形式。

Is there any way to make my application show rows while the query is fetching the records?
Like MSSQL Management Studio.

在查询获取记录时,有没有办法让我的应用程序显示行?像MSSQL Management Studio一样。

The select takes about 7 min to terminate the execution.

选择大约需要7分钟来终止执行。

I'm using Delphi 2007.

我正在使用Delphi 2007。

1 个解决方案

#1

A difficult challenge. If I need to do massive queries, I normally break the query into chunks. I then create a stored procedure that takes parameters @ChunkNumber, @ChunkSize and @TotalChunks. So you would only run the query for records from (@ChunkNumber-1)@ChunkSize+ 1 to @ChunkNumber@ChunkSize. In your Delphi code, simply run a loop like this (PSeudo Code):

一个艰难的挑战。如果我需要进行大量查询,我通常会将查询分成块。然后我创建一个存储过程,该过程接受参数@ChunkNumber,@ ChunkSize和@TotalChunks。因此,您只能运行查询(@ ChunkNumber-1)@ ChunkSize + 1到@ ChunkNumber @ ChunkSize的记录。在您的Delphi代码中,只需运行这样的循环(PSeudo代码):

for(Chunk = 1 to TotalChunks)
{
       DataTableResults = sp_SomePrecedure @ChunkNumber = @Chunk, 
                           @ChunkSize = ChunkSize
       RenderTableToClient(DataTableResults)
}

In this way, lets say you have 10,000 records, chunk size is 100. So you will have 100 SP calls. So you could render each chunk received from the SP, so the user is able to see the table updating.

这样,假设你有10,000条记录,块大小为100.所以你将有100个SP调用。因此,您可以渲染从SP接收的每个块,以便用户能够看到表更新。

Limitations are if the query running needs to run all records in one hit first. E.g. a Group By. SQL server uses OFFSET so you can combine to get something useful.

限制是指运行的查询需要首先在一次命中中运行所有记录。例如。分组依据。 SQL Server使用OFFSET,因此您可以组合以获得有用的东西。

I have queries that run about 800K records take about 10 mins to run which I do this with. But what I do is chunk up the source tables and then run queries, e.g. if one table users has 1M records and you want to return a query which shows the total pages accessed per hour, you could chunk the users up and run the query for each chunk only.

我有运行大约800K记录的查询需要大约10分钟才能运行,我这样做。但是,我所做的是将源表分块,然后运行查询,例如如果一个表用户具有1M记录并且您想要返回显示每小时访问的总页数的查询,则可以将用户组合起来并仅为每个块运行查询。

Sorry I dont have specific code examples but hope this suggestion leads you in a positive direction.

对不起,我没有具体的代码示例,但希望这个建议引导您朝着积极的方向发展。

#1

for(Chunk = 1 to TotalChunks)
{
       DataTableResults = sp_SomePrecedure @ChunkNumber = @Chunk, 
                           @ChunkSize = ChunkSize
       RenderTableToClient(DataTableResults)
}