Hi all,
I have another problem after migration from BDE to UniDAC. In BDE I can use the following lines without problems:
MyTable.Open;
if MyTable.Locate ('PKID', '12345', []) then ...
The Table is very small (~ 50.000 records) and PKID is the Primary Key. In BDE these two lines take less than 1 second.
When I Changed MyTable from TTable to TUniTable the Locate-Method uses 30 seconds. Then I found in this forum, that it is good to use the IndexFieldNames-Property of TUniTable. So I changed the code:
MyUniTable.IndexFieldNames := 'PKID';
MyUniTable.Open;
if MyUniTable.Locate ('PKID', '12345', []) then ...
Now Locate runs very fast, but MyUniTable.Open takes more than 30 seconds.
Then I deleted IndexFieldNames and I tried a filter:
MyUniTable.Filter := 'PKID=' + '''' + '12345' + '''';
MyUniTable.Filtered := true;
MyUniTable.Open;
if MyUniTable.Locate ('PKID', '12345', []) then ...
It's the same: MyUniTable.Open takes more than 30 seconds. I don't want to use a filter. It was only a test, because in the BDE the Filter is good way to speed up the TTable.Open method. But it has no effect on TUniTable.
I tried this with InterBase and Oracle. InterBase is a little bit faster, but not fast enough.
I checked DBMonitor 2.14, but there is only on line: "select * from MYTABLE" for the Open-Command. The Locate-Command or the Filter is not shown in the DBMonitor. Why not?
My Question is: What can I do to open a TUniTable very fast? And how can I locate one record using the Primary Key of the table?
Update 09.07.2009 17:50. Now I tried this:
MyUniTable.Open;
MyUniTable.FilterSQL := 'PKID=' + '''' + '12345' + '''';
if MyUniTable.Locate ('PKID', '12345', []) then ...
That is with InterBase very fast. But this can't be the solution. I cannot write for every line with "Locate" a line with "FilterSQL".
What can I do?
Thanks in Advance for any help.
Kind regards,
Gerd Brinkmann
invent GmbH
TUniTable.Open or TUniTable.Locate too slow?
When you call the Locate method, the TUniTable component fetches all records from the table until it finds the the required record.
If you set the FetchAll specific option to True, all records are fetched when you open the table. So the Locate method works fast but the Open method works slow.
The IndexFieldNames and Filter properties are applied on the client side. If you set the IndexFieldNames, all records are fetched on Open.
You can use the FilterSQL property to make Locate faster. Value of this property is added to the WHERE clause of an SQL statement. So only records that match filter are fetched. Open and Locate work fast in this case.
If you set the FetchAll specific option to True, all records are fetched when you open the table. So the Locate method works fast but the Open method works slow.
The IndexFieldNames and Filter properties are applied on the client side. If you set the IndexFieldNames, all records are fetched on Open.
You can use the FilterSQL property to make Locate faster. Value of this property is added to the WHERE clause of an SQL statement. So only records that match filter are fetched. Open and Locate work fast in this case.
Hello Plash,
thanks for your answer, but it's not a really good idea.
We make Software with GUI. The user see one record in a form. Then he gives an id to change the record. With BDE I can use Locate to check, wether the id exist and to jump to the other record.
Now, I have to send 2 sql-statements: First a query to check the existence and than TUniTable.FilterSql to locate the record. If the id is not available the form has to stay in the present record.
And there is another problem: When I use FilterSQL there is no chance to use First, next, prior or last.
I'm sure, I'm not the only one who developes user-interfaces. Is there any example how those problems are solved?
Thanks again and kind regards,
Gerd Brinkmann
invent GmbH
thanks for your answer, but it's not a really good idea.
We make Software with GUI. The user see one record in a form. Then he gives an id to change the record. With BDE I can use Locate to check, wether the id exist and to jump to the other record.
Now, I have to send 2 sql-statements: First a query to check the existence and than TUniTable.FilterSql to locate the record. If the id is not available the form has to stay in the present record.
And there is another problem: When I use FilterSQL there is no chance to use First, next, prior or last.
I'm sure, I'm not the only one who developes user-interfaces. Is there any example how those problems are solved?
Thanks again and kind regards,
Gerd Brinkmann
invent GmbH
Unfortunately TUniTable cannot emulate scrollable cursor like BDE TTable does.
BDE TTable probably uses indexes and executes many SELECT statements with different conditions in the WHERE clause to navigate the table data. TUniTable executes only one SELECT statement, it fetches data sequentially, and stores it in memory.
You can use only one query when working with TUniTable. Set FilterSQL before opening the table. If you set it after you open the table, TUniTable is closed and reopened with another SELECT statement (e. g. TUniTable is opened twice). Use the RecordCount property to check if the record exists. If RecordCount > 0, the record exists.
You can emulate the First, Next, Prior, and Last methods by using FilterSQL. For example, the following can be used for the Next method:
FilterSQL := 'ID > :CurrentId AND ROWNUM <= 1';
BDE TTable probably uses indexes and executes many SELECT statements with different conditions in the WHERE clause to navigate the table data. TUniTable executes only one SELECT statement, it fetches data sequentially, and stores it in memory.
You can use only one query when working with TUniTable. Set FilterSQL before opening the table. If you set it after you open the table, TUniTable is closed and reopened with another SELECT statement (e. g. TUniTable is opened twice). Use the RecordCount property to check if the record exists. If RecordCount > 0, the record exists.
You can emulate the First, Next, Prior, and Last methods by using FilterSQL. For example, the following can be used for the Next method:
FilterSQL := 'ID > :CurrentId AND ROWNUM <= 1';