New To TMSTable

swright · Post by **swright** » Wed 23 Dec 2009 12:39

Hello,
We have an existing suite of Delphi 7 applications using a Pervasive Database, It uses Pervasive PDAC TTable replacement to access the DB.

A new customer has Insisted the product must now use MSSQL as its database so I have taken a quick look at TMSTable but have found some issues.

1) If I attempt to open a table "Ordered" (Using IndexFieldNames) it appears to hang while it appears to query the entire dataset even though there is an index on the fields. #

This is a big problem as some of the tables can contain tens of millions of records

2) Does TMSTable support SetRange or an equivelent.

3) If not an query components have to be used is it possible to refresh an individual row, or a range of rows in an existing result set.

thanks.

Dimon · Post by **Dimon** » Thu 24 Dec 2009 08:26

1. To solve this problem use the TMSTable.OrderFields property to build ORDER BY clause of SQL statements. Also you can use the FetchAll property to increase perfomance on opening table.

2. SDAC does not support Range.

3. Use the RefreshRecord method to actualize field values for the current record.

swright · Post by **swright** » Thu 24 Dec 2009 12:25

Hello,
I have just re-tested.

FetchAll = False
FetchRows=5
OrderFields= idLot { primary key }

Change Active to true and I get an hour glass and it does not seem to ever complete.

I can run queries on the table ok.

Any Ideas?

Dimon · Post by **Dimon** » Thu 24 Dec 2009 14:38

Does clearing the OrderFields property solve the problem?

swright · Post by **swright** » Sat 26 Dec 2009 23:40

If both IndexFieldNames and OrderFields are blank then it works, obviously not ordered though.

Dimon · Post by **Dimon** » Mon 28 Dec 2009 09:08

In this case the delay is arised because you use the ORDER BY statement in the SQL query. In this case SQL Server returns result with delay and we can't influence this.

swright · Post by **swright** » Tue 29 Dec 2009 10:02

I am unable to see the SQL that TMSTable uses.

However i can execute

SELECT TOP 10 * FROM dbo.TRANSACTIONS ORDER BY idLot

and it returns immediatley.

I now also have another issue.

I was asked to evaluate this project a few months ago and was called off due to other issues. Now I am back to look at it the trial period has just expired and I am unable to test any further. can this be extended?

Dimon · Post by **Dimon** » Wed 30 Dec 2009 08:53

Set the TMSTable.Debug property to True to display executing statement and all its parameters' values.
Also you can use the TMSQuery componentto execute your own query, like

Code: Select all

SELECT TOP 10 * FROM dbo.TRANSACTIONS ORDER BY idLot

swright wrote:I was asked to evaluate this project a few months ago and was called off due to other issues. Now I am back to look at it the trial period has just expired and I am unable to test any further. can this be extended?

We can send you another SDAC trial build with extended trial period. If you need it, contact us by e-mail at dmitryg*devart*com.

swright · Post by **swright** » Wed 30 Dec 2009 11:20

Thanks for the help and the extended Trial.

The table I am testing with has 1.1million records (simulation of approx 2 years of data in a real DB).

If I use the orderfields property it takes in excess of ten minutes to complete with fetchall=FALSE

In reality I am never going to present a dataset that large in a grid etc. But I do currently use multiple TTable type components to Insert/Change/Delete various rows (within a transaction). And will have to Rework/Rewrite this for MSSQL.
Can this only be achieved by using the "FilterSQL" to add a where clause, and would I need to add another TMSTable if I wanted to locate and update a record outside of the Filter?
Or Should It be done a different way?

regards

Dimon · Post by **Dimon** » Wed 30 Dec 2009 12:58

If you are using TMSTable and not TMSQuery, then you should use the FilterSQL property to add a where clause. In this you can locate and update only records that meet filter’s condition.

swright · Post by **swright** » Wed 30 Dec 2009 14:14

I have done a little more testing and it seems that using fetchall=false and orderfields seems to work on smaller tables (eg 22000 records) but when I attempt it on a larger table (1000000 records +) it always fetches all.

Orderfields in all cases matches an index.

Dimon · Post by **Dimon** » Wed 30 Dec 2009 15:08

Please check if you can reproduce the problem using TMSQuery with FetchAll = False and SQL equal to
'SELECT TOP 10 * FROM tablename ORDER BY idname'
for a table with 1000000 records.

swright · Post by **swright** » Wed 30 Dec 2009 16:27

Replaced TMSTable with TMSQuery

Have it linked to datasource and datasource linked to TDBGrid.

Set FetchAll = false
SQL = SELECT TOP 10 * FROM TRANSACTIONS ORDER BY idLot

It returns the 10 records immediate

Set FetchAll = false
SQL = SELECT TOP 100000 FROM TRANSACTIONS ORDER BY idLot

Returns immediate and thumb is at bottom of grid at 25 records then
repositions as it fetches more.

Set FetchAll = false
SQL = SELECT TOP 200000 FROM TRANSACTIONS ORDER BY idLot

waits until query completes (approx 10 min).

Set FetchAll = false
SQL = SELECT * FROM TRANSACTIONS ORDER BY idLot

waits until query completes (approx 10 min).

Dimon · Post by **Dimon** » Wed 30 Dec 2009 16:46

swright wrote:Set FetchAll = false
SQL = SELECT TOP 10 * FROM TRANSACTIONS ORDER BY idLot

It returns the 10 records immediate

To solve the problem use TMSQuery.

swright · Post by **swright** » Wed 30 Dec 2009 16:59

the 10 records was just an example.

originally I was looking at all records, as a ttable replacement.

It appears that if the result set is too large then it is processed differently.