I had an application of CX-Supervisor v1.1 which stores and retrieves data from a database. It's been working with MS Access for a while and it did it relatively fine. Due to new requirements with the data collected we decided to migrate the DB to SQL Server.

After the migration, I found that the command DBExecute with the function "Find" works terribly slow when the number of records in the query is high. Curiously, this doesn't happen with Access. The table es opened and the recorset is read at normal speed, but the search of a particular record is unacceptably slow.

Unfortunately SQL Server doesn't accept variable parameters in the queries so I cannot use the parameters feature provided by CX-Supervisor to filter the queries. On the other hand, as far as I know, the SQL strings cannot be built programmatically built during run time in CX-Supervisor.

I need to make sure a record with a particular value in the key field does not exist in the table before saving a new record with new data. Besides this, I need to retrieve any specific historical record from the database for viewing. In both cases I need to use the DBExecute/Find command.

Any idea on how to overcome this problem?

Thanks in advance for any help.

Sounds like that your table needs an index.



Did you checked that database table is indexed as unique over your key field? If so, you should not be able to insert a new record having the same key, SQL server should return an error. And if table is indexed on fields that you may need to search, searching performance should be improved.