Indexes in TUniTable

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Ivan_Carpio89
Posts: 29
Joined: Mon 10 Dec 2018 23:49

Indexes in TUniTable

Post by Ivan_Carpio89 » Fri 14 Dec 2018 00:43

Hello,

I see that the property "TUniTable.Indexes" does not exist. Does this property have another name, or does the use of indexes in the TUniTable not exist?

I am interested in using the UniDAC components, but so far I have not found how to make the following code:

Code: Select all

FDTable1.IndexName:= FDTable1.IndexDefs.Items[0].Name;
if not FDTable1.FindKey(['001', 'F']) then
    raise exception.Create('Row not found!');
   
Any suggestions?

Thanks in advance.

frickler
Posts: 11
Joined: Wed 04 Apr 2018 08:30

Re: Indexes in TUniTable

Post by frickler » Fri 14 Dec 2018 07:53

Use "indexFieldNames" instead of "IndexName", as described in the documentation:
https://www.devart.com/unidac/docs/deva ... dnames.htm

Ivan_Carpio89
Posts: 29
Joined: Mon 10 Dec 2018 23:49

Re: Indexes in TUniTable

Post by Ivan_Carpio89 » Fri 14 Dec 2018 17:09

And the FindKey(), looks for the row, based on the fields indicated in "IndexFieldNames"?

The code in which we work, more than 50% is focused on working with the TFDTable's, browsing the records based on the indexes that have the physical tables in the Database, are the same as the TFDTable's, that is, for perform:

-FindKey
-FindNearest
-SetRange
-Etc.

Stellar
Devart Team
Posts: 305
Joined: Tue 03 Oct 2017 11:00

Re: Indexes in TUniTable

Post by Stellar » Mon 17 Dec 2018 15:00

TUniQuery and TUniTable do not have the IndexName property, because modern DBMSs automatically use server indexes when executing an SQL statement on the server side. The IndexFieldNames property is used for getting or setting up the list of fields, which a set of records is sorted by on the client side. Also, sorting a dataset by setting the IndexFieldNames property increases performance of records search in a dataset.

The sample of using the LocateEx method for moving the cursor to a specific record instead of calling the FindKey and FindNearest methods:

Code: Select all

UniQuery1.SQL.Text := 'SELECT DeptNO, DName, LOC FROM Dept';
UniQuery1.KeyFields := 'DeptNO';
UniQuery1.Open;

//FindKey
UniQuery1.LocateEx(UniQuery1.KeyFields, VarArrayOf([8]), []);

//FindNearest
UniQuery1.LocateEx(UniQuery1.KeyFields, VarArrayOf([21]), [lxNearest]);
The sample of using the SetRange method in UniDAC for setting the scope and applying it to a a dataset:

Code: Select all

UniQuery1.SQL.Text := 'SELECT DeptNO, DName, LOC FROM Dept';
UniQuery1.IndexFieldNames := 'DeptNO';
UniQuery1.Open;
UniQuery1.SetRange([8], [12]);
More information about FindKey:
https://www.devart.com/sdac/docs/devart ... ect[]).htm

More information about FindNearest:
https://www.devart.com/sdac/docs/devart ... ect[]).htm

More information about SetRange:
https://www.devart.com/sdac/docs/devart ... olean).htm

Ivan_Carpio89
Posts: 29
Joined: Mon 10 Dec 2018 23:49

Re: Indexes in TUniTable

Post by Ivan_Carpio89 » Wed 19 Dec 2018 18:07

Thank you very much for your support.

Regards.

karunia_widyastuti
Posts: 3
Joined: Tue 16 Jul 2019 08:40

Re: Indexes in TUniTable

Post by karunia_widyastuti » Tue 16 Jul 2019 08:56

Hellooo..

Need help urgent please..
I have big data, 10 years old data..

When i want to set filter in TUniTable it's getting very slow when the form is created.. it's took 20 seconds..
But when i try with the previous apps whic using TTable and the same code, it's fast..

Could you help me to speed up this problem?

TUnitable.IndexFieldNames:= 'SLSNO;TRANSTYPE';
TUnitable.Active := false;
TUnitable.Filtered := false;
TUnitable.Filter := 'SLSNO=''' + DMDataPenjualan.SLSJUAL + ''' and TRANSTYPE=''F''';
TUnitable.Filtered := True;
TUnitable.Active := True; --> It's very slow when i debugging in this step..

I used the same code in my previous apps which are using TTable, and Delphi 7 it's fast.. but why when I am upgrading it into Delphi XE5 and TUniTable it's become slow???

Regards,

Karunia

Stellar
Devart Team
Posts: 305
Joined: Tue 03 Oct 2017 11:00

Re: Indexes in TUniTable

Post by Stellar » Wed 17 Jul 2019 12:54

In your example the server sends all table records to the client, and then a filter is applied to the dataset data.
If the table contains a lot of records, this approach will take a lot of time. For the server to only transfer the necessary data, you can set up a filter for the FilterSQL property. For example:

Code: Select all

UniTable1.FilterSQL := 'SLSNO=''' + DMDataPenjualan.SLSJUAL + ''' and TRANSTYPE=''F''';
UniTable1.Active := True;

karunia_widyastuti
Posts: 3
Joined: Tue 16 Jul 2019 08:40

Re: Indexes in TUniTable

Post by karunia_widyastuti » Fri 19 Jul 2019 10:26

Dear Stellar,

Is that mean that I just use this Syntax :

UniTable1.FilterSQL := 'SLSNO=''' + DMDataPenjualan.SLSJUAL + ''' and TRANSTYPE=''F''';
UniTable1.Active := True;

without using :

TUnitable.Filtered := false;
TUnitable.Filter := 'SLSNO=''' + DMDataPenjualan.SLSJUAL + ''' and TRANSTYPE=''F''';
TUnitable.Filtered := True;

in my code??

So it will be faster?

Stellar
Devart Team
Posts: 305
Joined: Tue 03 Oct 2017 11:00

Re: Indexes in TUniTable

Post by Stellar » Mon 22 Jul 2019 12:24

You don't need to set an identical condition for the properties FilterSQL and Filter. If you use FilterSQL, the filter will be appended to the WHERE clause of the SQL statement, in which case the server will send only the necessary records to the client, e.g.:

Code: Select all

CREATE TABLE DEPT (
	DEPTNO int IDENTITY(1,1) NOT NULL,
	DNAME varchar(20) NULL,
	LOC varchar(20) NULL
)

Code: Select all

UniTable1.TableName := 'Dept';
UniTable1.FilterSQL := ' LOC = ''ENG'''
UniTable1.Open;
The query which is going to be executed on the server will look like this:

Code: Select all

SELECT * FROM Dept WHERE LOC = 'ENG' 
The server will only retrieve records that match the condition LOC = 'ENG' from the table.

If you set a filter condition in the Filter property, the query will look like this:

Code: Select all

SELECT * FROM Dept
In this case, all records of a table will be passed to the client by server for later filtering on the client side.
Data transferring over the network takes a lot of time, so the use of FilterSQL has the advantage of speed for large tables.
You can also filter the retrieved records using the Filter property.

More on FilterSQL:
https://www.devart.com/sdac/docs/devart ... tersql.htm

karunia_widyastuti
Posts: 3
Joined: Tue 16 Jul 2019 08:40

Re: Indexes in TUniTable

Post by karunia_widyastuti » Wed 31 Jul 2019 06:29

Dear Stellar,

Thank you.. it's work..

Btw i have another question... is there any other way to make the pointer go to last record beside TUniTable.Last ??
Because when i do that syntax ( TUniTable.Last ) it's so slow...

Regards,

Karunia

Stellar
Devart Team
Posts: 305
Joined: Tue 03 Oct 2017 11:00

Re: Indexes in TUniTable

Post by Stellar » Thu 01 Aug 2019 15:19

If all records have been loaded to the dataset, UniDAC should quickly fetch the last record. If the records from the dataset are used in visual components, you need to disable the controls updating before invoking the Last method, e.g.:

Code: Select all

//  Query.SpecificOptions.Values['FetchAll'] := 'True';

  UniQuery1.DisableControls;
  try
    UniQuery1.Last;
  finally
    UniQuery1.EnableControls;
  end;

Post Reply