Is it possible to show the records from an ADOQuery whilst opening it?

Question

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.

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

The select takes about 7 min to terminate the execution.

I'm using Delphi 2007.

Analogize

docs.embarcadero.com/products/rad_studio/delphiAndcpp2009/… – Ardys

How many records does the query fetch from the server? – Gatekeeper

Why the connection isn't in the same datamodule of the others components? – Athanasia

Have you tried with a TClientDataset and PackedRecords ? (not sure thats the correct property name I dont have delphi at hand here ). Also is there no way to optimize the query so it does not takes 7 minutes ? – Arkose

@Johan, I have already tried this, but it didn't work out... – Analogize

@Gatekeeper About 2500 records – Analogize

@Arkose I did find a way to optimize it, but it still takes about 5 minutes to get all the records. And yes, I also used a TClientDataSet but when I tried to debug my application, it didn't execute any of the related events. – Analogize

Well, the first thing to do is to find out why your query is executing so slowly - even over a slow network, it should take nothing like 7 minutes to execute, or even seven seconds. If the server is on the same machine, it should be virtually instantaneous. So, I suggest you add to your question, at the minimum, the Sql of your query, the exact code you are using to execute the query and the code of all event handlers your AdoQuery has. Without those, I doubt anybody will be able to help you. – Gatekeeper

does the query runs just as slow in mssql management studio ? If so than show us your query. – Arkose

Answer 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):

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.

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.

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.

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

Upbraid

The problem is, the OP never gave any explanation of why his query was running so slowly (7 minutes to retrieve 2500 rows). – Gatekeeper

Agree, but the specific question is how to progressively render the data in the DataGrid without waiting for the whole thing to run. If OP asked "why is my query so slow", perhaps that would be a more appropriate question for us to answer :D – Upbraid

But it is a Delphi q and your answer doesn't address that. a Delphi ADO query is inherently capable of returning results asynchronously and in any case can retrieve rows in a background thread and periodically update their display in the gui, if the app is coded to do so. – Gatekeeper

Yes, but what you are not factoring is that SQL server doesn't inherently provide this capability. It will go away, run the query and once finished, return results... So if SQL server takes 7 mins to respond, then Delphi isn't going to have anything to async render. – Upbraid

"SQL server doesn't inherently provide this capability." Really? Then how do you account for the behaviour of SSMS that the OP wants to emulate and what is said in this article: social.msdn.microsoft.com/Forums/sqlserver/en-US/… – Gatekeeper