I need more speed

Richard Larson · Post by **Richard Larson** » Thu 09 Jun 2005 19:47

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

Ikar · Post by **Ikar** » Fri 10 Jun 2005 07:10

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.

Richard Larson · Post by **Richard Larson** » Fri 10 Jun 2005 09:59

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?

Ikar · Post by **Ikar** » Fri 10 Jun 2005 12:02

> 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.

Richard Larson · Post by **Richard Larson** » Fri 10 Jun 2005 12:15

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.

Ikar · Post by **Ikar** » Fri 10 Jun 2005 13:01

> 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.