ODAC (V9.5.15) study notes (20) Big Data Volume acquisition processing

Odac the efficiency of getting data is high, there are several ways in which you want to get the first page of data faster in a Web program:

1, in the database for paging processing;

2, get all the data, just quickly return to the first page of data.

The first scenario on the application server resource consumption is minimal, the database consumption is slightly larger, the customer needs to be flexible filtering of the whole data, find, statistics is not enough, in addition to the time-consuming SQL query is not as fast as the second scenario, the pressure on the database is also larger, and need to write programs to complete. Under Delphi I consider using the second scenario, especially when using UNIGUI+ODAC. The second scenario corresponds to a slightly larger memory pressure on the server and requires a quick fetch of the first page of data, which is an experiment:

1.240,000 records in a single table

2, using a one-time access to all records, and placed in the server memory, by the Unigui grid automatic paging processing.

3, not one-time access to all records, you need to quickly get the first page of data, The fetchrows setting of the Toraquery component is consistent with the weboptions.pagesize of the Tunidbgrid component (not required, just that it is more efficient to display the first page), and then by starting a thread in the background via Toraquery component Fetchall genus Set to True to get all the data. When the amount of data in the Toraquery changes, scrolling any record in the Tunidbgrid component triggers an automatic update of the number of records and pagination, so there is no need to refresh the grid after getting all the data.

The code is as follows:

procedureTmainform.unibutton11click (sender:tobject);varD:dword;begin  //TimingD: =GetTickCount; //The number of records fetched per packet, with the recommended consistency of each page of the gridUniMainModule.OraQuery7.FetchRows: =UniDBGrid7.WebOptions.PageSize; //whether to get it onceUniMainModule.OraQuery7.FetchAll: =unicheckbox7.checked; //Open the table, and if it is not a one-time fetch, the number of records in the first packet is obtainedUniMainModule.OraQuery7.Open; //Take timeUnilabel17.caption: = Format ('%d Ms', [GetTickCount-d]); //turn on thread fetching  if  notUnicheckbox7.checked Thentfetchthread.create (unimainmodule.oraquery7);End;

Unicheckbox7.checked determines whether a one-time access option is used, and the experiment shows:

1, one-time access to data mode, showing that the first page cost about 3000ms, memory consumption of about 180M, after the data set off the memory is reduced to about 10M, indicating that the memory release is very clean.

2, not a one-time access to data mode, showing that the first page cost about 20ms, the background reading data does not affect the front-end data display, scrolling and other operations, the final memory consumption and closed after the release of the same-time acquisition mode.

The thread code for retrieving data in the background is as follows:

  class (tthread)   Private     fdataset:  toraquery;    Public    procedure Override ;     Constructor Create (adataset:toraquery);   End;

{Tfetchthread}Constructortfetchthread.create (adataset:toraquery);beginFdataset:=Adataset; Freeonterminate:=True; inheritedCreate;End;procedureTfetchthread.execute;begin  inherited; ifAssigned (Fdataset) Then  beginFdataset.fetchall:=True;  while  notFdataset.fetched DoSleep (Ten); Mainform.caption:='Refresh'; End;End;

The effect is basically satisfied

ODAC (V9.5.15) study notes (20) Big Data Volume acquisition processing