CX Supervisor with SQL Server

[Juan 0]

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.

[moosty 0]

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.

[Daniil 0]

Juan, have you overcome this problem?

[Berti Baker 7]

Hi,

I agree, SQL Server Find should be quick with say less than 2 million records, and for a large database solution always work having a professional data expert design the tables and keys.

Just to add CX-Supervisor DOES allow direct SQL text to be used if required. To do this programmatically look at the DBExecute with command parameter "SQL" (example in the installed Database Demo).

Regards,
BB