How to speed up Refresh

norwegen60 · Post by **norwegen60** » Wed 26 Oct 2011 17:10

Dear all,

I`m working with Delphi XE, UniDac 4.0.2, MsSQL 2005 and a special lookupcombo. This Lookup is linked with a TUniQuery

Code: Select all

SELECT ID, SerNr, Typ, Dat
FROM Ser_Nr
ORDER BY SerNr, Dat

The Table has about 800.000 records. The Query I edit the datas is linked with the Option MasterSource to the above query. This construct is working well. No difference if I delete the MasterSource.
To work always with the current datas I have to make a resfresh of the lookup query f.e. after an insert. Better I make a refresh every time if I enter the Lookup as also other workingstation could insert datas.
The problem is, that such a refresh needs at my customer 16 sec. At my PC with local DB it needs 2,5 sec.
Is there any chance to speed up the refresh. Is there no intelligence, which makes an refresh only if datas has changend and also refresh only the changed datas to minimice the traffic on the network?

Some settings:

Fetchall = true (no different with false)
also the other options on default
No IndexFieldNames (much more slower with Index..)

best regards
Gerd

AndreyZ · Post by **AndreyZ** » Thu 27 Oct 2011 12:09

Hello,

To quickly retrieve to the client side changes applied to the server by other clients, you can use the TUniQuery.RefreshQuick method. The main difference between the RefreshQuick and Refresh methods is that RefreshQuick does not transfer all data to the client like the Refresh method does. The only rows that were added or modified from the moment of the last refresh are returned to a client. The necessity of data inquiry for each row is defined by the TIMESTAMP field. So the RefreshQuick method requires query to include a unique key fields and a TIMESTAMP field. But note that if RefreshQuick is called for a dataset which is ordered on the server (query includes the ORDER BY clause), dataset records ordering can be violated because not all records will be retrieved by this method. You can use local ordering (the IndexFieldNames property) to solve this problem. In this case the first query opening will be not so fast with IndexFieldNames, but refreshing will be performed only for records that were changed.

norwegen60 · Post by **norwegen60** » Sat 29 Oct 2011 08:47

Many thanks for your feedback. But your proposal didn´t solved the problem. I made following tests with the table with about 800.000 records. One possibilty direct with the table and following SQL

Code: Select all

SELECT ID, SerNr, Typ, Dat
FROM Ser_Nr
ORDER BY SerNr, Dat

and another over a view to the above fields and this SQL

Code: Select all

SELECT * FROM viewSer_Nr
ORDER BY SerNr, Dat

and I got following times in sec :

.................................table................................ view
........................with............without..........with..............without
.......................local IndexfieldName.........local IndexfieldName
Open.................38,5..........13,6..............36,7.............1,2
refresh..............55,6...........13,5.............54,4.............1,2
RefreshQuick.....27,9.............0,1.............28,2.............endless
last.....................0,0............0,0...............0,0.............11,4
Refresh.............55,1...........13,6.............54,5.............13,2

With last I jumped to the end of the table and made again a refresh because I saw, that with a view there is a difference in time.

The refreshquick was done with CheckDeleted = false. With true the times are much more longer.

You see with local ordering the time are much more longer. And I need the datas ordered.

You see any other chance

Best regards
Gerd

AndreyZ · Post by **AndreyZ** » Mon 31 Oct 2011 13:47

You can try creating an index that contains a TIMESTAMP column and check if it speeds up the refresh operation. Unfortunately, there is no other way to speed up the refresh operation. The speed of data fetching to a client greatly depends on your server capacity and network speed.