When "FetchAll=False" query.close took so long!

Zoner · Post by **Zoner** » Wed 08 Dec 2010 10:05

If Fetchall is False, the query.close method took some time to complete the job.
If you are connecing to a local database, you may not notice this extra waiting. But if you are connecting to a remote database and if you have a few joins in yor SQL then it becomes a problem.

We tried this with a simple empty application:
placed a TMSConnection, a TMSQuery, a TMSDataSource and a DBGrid
we set the connection to a remote database. the sql is selecting 15 columns from orders,orderlines and customers tables. There are 1500 records in the resultset, but since the fetchall=False only first 50 is loaded at opening.
The query.open method takes nearly 1 second (very normal).
But the query.close method takes nearly 5 seconds.
We tried it with 25.000 records and opening is again 1 second, but closing this time took 17 seconds !

We also tried it with a simple "select * from table" with 300.000 records again with a remote connection. But everything is normal in this case.

Please note that, we do not move inside the grid, we do not call any Locate or Last method. Just open and then close.

Any help is greatly appreciated, thanks.

Dimon · Post by **Dimon** » Wed 08 Dec 2010 13:40

I can not reproduce the problem.
It seems that the problem is connected with the specificity of the OLEDB work.
Please specify the Delphi version and the SDAC version you are using.
Also try opening and closing your queries using ADO components and check if the problem still exists.

Zoner · Post by **Zoner** » Wed 08 Dec 2010 18:16

We are using Delphi 2007 with SDAC version 4.80.0.56.
SQL Server version is SQLExpress 2005.

The problem is related with the Fetchall property.
When we try the tests with FetchAll=True, then query openings take some time but all the query.close methods are like an eye-blink as expected.

Even when FetchAll=False, if you press Ctrl+End and goto last record and then close the query, eveything is again normal.

Another interesting test result:
Orders, Orderlines and Customers tables joined, and there are 25.000 records. 17 seconds closing time as I mentioned before.
When Customers table is removed, everything is normal again.

Sounds like a poor join or an indexing problem.
But let me remind again:
We do not have any performance problems about query openings, something strange happens when closing the query!

Thanks.

Dimon · Post by **Dimon** » Thu 09 Dec 2010 13:52

As I said, this problem is connected with the specificity of the OLEDB work and we can't influence it.

Zoner · Post by **Zoner** » Wed 22 Dec 2010 13:19

This problem is really becoming a headache for us.
And the only solution we could find is changing the CursorType to ctStatic.

1. [FetchAll=False and CursorType=ctStatic]
2. [FetchAll=False and CursorType=ctDefaultResultSet]

With a remote connection what are the pros and cons of those two configurations just when browsing the data?

When it is ctStatic, every single move in the grid turns the ScreenCursor to an SQLHourGlass. Is this normal?

AndreyZ · Post by **AndreyZ** » Thu 23 Dec 2010 15:38

It's better to use the second variant, because server cursors can't work with data that was not fetched completely. To see the difference you can check the MSQuery.RecordCount property after query opening in both variants. To avoid showing the SQLHourGlass cursor you should redefine the StartWaitProc procedure of the MemData unit in the following way:

Code: Select all

procedure MyStartWait;
begin
//
end;

constructor TMainForm.Create(AOwner: TComponent);
begin
  inherited;
  StartWaitProc := MyStartWait;
end;