Micro Focus Studio Enterprise Edition Test Server > Programming > Data Access > Micro Focus Database Connectors Version 2.0 for Windows > Database Connectors Reference > Configuration Variables > A_MSSQL_LIMIT_DROPDOWN

A_MSSQL_LIMIT_DROPDOWN

This configuration option is closely related to A_MSSQL_USE_DROPDOWN_QUERIES.

When a sequence of START and READ NEXT/PREVIOUS operations are performed by an application, Database Connectors/MSSQL generates a sequence of queries to return the set of records matching the application's request. To improve performance, the interface generates a sequence of "drop down" queries based upon the key of reference's key segments going from the most specific subset using the most number of segments to the most general using the least number of segments. This functionality is turned on by setting the configuration variable A_MSSQL_USE_DROPDOWN_QUERIES to TRUE.

For example if a key is described by:

	03 MY-ALTKEY. 
	   05 MY-ALTKEY-SEG1        PIC X(2). 
	   05 MY-ALTKEY-SEG2        PIC X(2). 
	   05 MY-ALTKEY-SEG3        PIC X(2).

Then a START followed by a sequence of READ NEXT operations might generate the selection criteria of:

WHERE MY-ALTKEY-SEG1 = :w0 AND MY-ALTKEY-SEG2 = :w1 AND 
MY-ALTKEY-SEG3 >= :w3 
WHERE MY-ALTKEY-SEG1 = :w0 AND MY-ALTKEY-SEG2 > :w1  
WHERE MY-ALTKEY-SEG1 > :w0

This can improve performance because the target for each query is kept to a minimal size. If a set of records is not required, the database does not need to spend the time building the working set. When a "DROP DOWN" does occur however, the subsequent working set can require a large amount of time to process because of the potential magnitude of records. Normally there is not a way for a COBOL application to instruct the interface to stop processing when it has finished with the records based on a given key segment.

To address this, use A_MSSQL_LIMIT_DROPDOWN configuration variable.

This variable allows an application to direct the interface not to perform "drop down" query generation and instead return "end of file" when the records matching the current query have been exhausted.

This variable may be set to one of the following options:

`OFF`	This is the current default. The interface performs "drop down" queries.
`PARTIAL`	If the record positioning was performed by a START with a SIZE clause such that the initial positioning was performed using fewer than the total number of columns in the key, the process stops after all records matching the START columns have been exhausted.
`FULL`	If the record positioning was performed by a START without a SIZE clause, the process stops after all records matching the START columns have been exhausted.
`ALL`	Regardless of what form of START was used for the initial positioning, the process stops after all records matching the START columns have been exhausted.

This variable is performed at the time of each start, so the application may change its value.