Increase Performance Using Set_SQL_Constraint from a Large Table

Assuming you are using a SQL database and a Flex2SQL driver, the Flex2SQL driver has a feature, SQL_Set_Constraint, that automatically adds a constraint to find all operations.

For example: Let's say that I want to show only orders which are after a certain date, such as all orders for 2003 and newer. I can do something like this:

Set_SQL_Constraint of Ordhdr to "and OrderDate >= '20030101'"
SQL_Constraint     of Ordhdr to activate

Now all finds on ordhdr will have this added selection.  It is also very fast because it is processed on the server.

This might be an option to use embedded SQL (however sql will still be faster since you would retrive an entire result set). If you are going to do some non optimized finds add a constraint using Set_SQL_Constraint before starting your find operation. Very little code is needed to get better performance (especially when retrieving few rows from a large table).

One option, for example, to use this (other than to gain better performance when having existing constraints) is when you want to "hide" records. I have many times received requests from customers that a user should only be able to see "your own records", for example if you have an Order entry system each user should only be able to see order he/she has entered.

This might be difficult to implement especially if you have not built the application to do that. If you don't add userid etc. to all indices it might become very slow (User Joe has one order number 10000 and you press find gt on 1 to find next order).

Additionally, it might become complex if your client also wants some users to be able to see all orders. Then it might not be that good to have added userid as first field in index.