Devart Forums

I have a project where I'm installing the same data to a SQL database and a Paradox database. The records in any given table may already exist, in which case they must be edited, or they may not exit, in which case they must be appended. To know which, the TTable.FindKey method is used for Paradox and the TMSTable.Locate method is used for SQL.

I knew SQL wasn't optimized to work with individual rows, but the speed difference is so enormous that I think I'm doing something wrong, but I have not been able to determing what it is. On our SQL database my utility takes 15 hours to do the same job that it does on our Paradox database in just 4 minutes

The TMSTable.locate method is being used to find rows based on columns that are used for the primary index, so I don't know why the slow response. Doesn't the TMSTable.locate method make use of existing indexes

Indexes are stored at the server but Locate executes at client side. So indexes cannot be used for Locate in client-server databases. Probably, you should perform your task on the server side in StoredProc or just as
DML.

Thanks for the info. However, my utility is running on the server box. Is there no way for the TSMTable component to make use of existing indexes? If not, is there a way to create a local index that TSMTable.Locate could use?

> However, my utility is running on the server box.

For client-server applications it is not enough. You should execute this task with DML statements. For example, compound SELECT statement.

> Is there no way for the TSMTable component to make use of existing
> indexes?

Not.

> If not, is there a way to create a local index that TSMTable.Locate could
> use?

By SDAC methods you cannot do it.

The inability of SDAC to use indexes greatly limits their usefulness for working with SQL tables of any significant size. Such a serious limitation should be more prominently covered in the documentation and on-line details. I expect the same limitation applies to the MySQL, and Oracle components.

> I expect the same limitation applies to the MySQL, and Oracle components.

You are right. It is a restriction of client-server architecture not depending on the type of used server.