Setting A_MSSQL_USE_DROPDOWN_QUERIES to TRUE causes selects sent to the database to be of the drop-down variety, instead of a single large query.
For example, if you have a file with three fields in the primary key, (keyseg1, keyseg2, keyseg3), and your COBOL program does a START, the following query is sent to the database:
select (columns) from (table) where ((keyseg1 = value1 and keyseg2 = value2 and keyseg3 > value3) or (keyseg1 = value1 and keyseg2 > value2) or (keyseg1 > value1)) order by keyseg1, keyseg2, keyseg3
If you use drop-down queries, the following collection of queries is sent instead:
select (columns) from (table) where (keyseg1 = value1 and keyseg2 = value2 and keyseg3 > value3) order by keyseg1, keyseg2, keyseg3
followed by:
select (columns) from (table) where (keyseg1 = value1 and keyseg2 > value2) order by keyseg1, keyseg2, keyseg3
and:
select (columns) from (table) where (keyseg1 > value1) order by keyseg1, keyseg2, keyseg3
There are advantages and disadvantages to each method. If you use the A4GL_WHERE_CONSTRAINT variable, you should probably set A_MSSQL_USE_DROPDOWN_QUERIES to FALSE, because the WHERE constraint limits the result set sufficiently that the larger query is more efficient. For additional information, see the section The WHERE Constraint in the chapter Performance and Troubleshooting.
If you usually START files and read to the end, you should set A_MSSQL_USE_DROPDOWN_QUERIES to FALSE, because a fewer number of queries need to be sent to the database. On the other hand, if you START files and stop reading after some condition, but have not used the WHERE constraint, you may get more efficient access by setting this variable to TRUE and using the drop-down style of query. In either case, run some tests to see which value makes the most sense for your application.
This variable is accessed only during a positioning operation, so you can set it at different times for different tables.
The default value is FALSE.
Example
A_MSSQL_USE_DROPDOWN_QUERIES TRUE