Data paging control

Hi,
I want to improve the performance in client side. DA has any paging control in dataset? For example, set the default return 5000 records per time. If user click “More” button, then return next 5000 or click “All” button to return all records.

1 Like

Hi,

you can use TOP and SKIP keywords in DASQL like

SELECT TOP 12 SKIP 13 * FROM table1

see more about DASQL syntax at the Supported SQL Syntax article

Hi,
Your advice can’t apply to stored procedure. Hasn’t any DA client components to handle the paging? Data is stored in DA server and then handle by DA client. That will be reduce the traffics between DA Server and SQL DB server. Is my idea correct?

Hi,

you can use table.MaxRecords property to specify how much records should be fetched from server-side.

I’ve attached old DA v4 sample that demostrates what you want to reach: Fetch.zip (44.4 KB)

Hi,
Perfect. Thanks so much.

Hi,
I tried to put “daPaged” into my clientdatamodule and change DAMemDataTable’s RemoteDataAdapter to “daPaged”. I’m using the following code. But it doesn’t to doing the paging. If without paging, it needs to take around 4 seconds in local SQL DB. The result has around 90K records. What am I missing? I’m also set “dtDisableFetchForClonedTables” to FALSE.

ClientDataModule.v_Tx_Mpo.MaxRecords := 10;
DASQL2 := ‘select * from v_Tx_Mpo order by MpoNo desc’;
ClientDataModule.ExecSQL(‘APP’,SysUser_No, ClientDataModule.v_Tx_Mpo, DASQL2, nil);

Hi,

why you can’t open sql like

RDA.FillWithDASql(table, 'select top 10 skip 0 * from v_Tx_Mpo order by MpoNo desc');
RDA.FillWithDASql(table, 'select top 10 skip 10 * from v_Tx_Mpo order by MpoNo desc');
RDA.FillWithDASql(table, 'select top 10 skip 20 * from v_Tx_Mpo order by MpoNo desc');

?

1 Like

Hi,
My statement is preparing the data for LookupComboBox. If using your SQL, then I need to handle the SQL statement every time when scroll the bottom of dataset.

The criteria condition has a lot of LookupComboBox for user choose.

Hi,

are you wanting to put 90K records to LookupComboBox?
as for me - wrong solution…

Hi,
I want to assign 1K records first. If user scroll down, then it adds the other 1K records in LookupComboBox. My current application is load all and it just only waits around 2 seconds

Hi,

btw, you can open tables asynchronously via RDA.BeginFill.
in this case, you can do anything else w/o waiting.

Hi,
I found your old post about asynchronous call. But I still don’t understand how to implement into my coding. Any example for my reference?

Hi,

you can specify callback method that will be called when data will be received from server.
if callback isn’t specified, it will call EndFill by default.

just replace

RDA.Fill([table]);

with

RDA.BeginFill(nil, nil,[table]);

Hi,
I tried to using following code that is very slow. I wait more than 5 minutes without response that can’t access anything. It like system hang.

Clientdatamodule.ChangeConnection(‘APP’, SysUser_No);
ClientDataModule.RemoteDataAdapter.BeginFill(nil, nil, [ClientDataModule.v_Tx_Mpo]);

Hi,

try to use table.DisableControls/table.EnableControls;
Delphi controls dislike to add a lot of records w/o using DisableControls/EnableControls.

25190.zip (34.3 KB)

Hi,
I tried to apply your code. The result is the same. Open the form is normal. After 1 second, it like system hang too.

Hi,

My testcase works correctly?
try to load not all 90k records in single call.
you can load next portions of records in callback method in the loop.

Hi,
Your testcase works without mass volume data. But apply to my data, it slows. I tried to doing the paging in SQL Server. If set 5000 per page, it is a little bit fast. But compare to load all records, it just slows 3 seconds. I think your first sample “Fetch.zip” should be better. But I don’t know why it doesn’t work in my coding. It should be something wrong in my steps.

Hi,

of course background task has lower priority that main thread… try to load initial 100 or 1k records and fill the rest in async mode.
I don’t think that your UI needs all 90k records instantly after program is started.

Hi,
Because user need to have the fast lookup when type the part of information. This can help user to improve the operation. Suppose “Fetch.zip” sample should fulfill the situation. When end of page, it will reload the next page.
If can’t, I will the other workaround solution to solve this issue. Thanks!