Kbase P134143: MS SQL Server DataServer is not using the first index on the table when a simple "for each" query is
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/08/2008 |
|
Status: Unverified
SYMPTOM(s):
MS SQL Server DataServer is not using the first index on the table when a simple "for each" query is run against SQL Server database
FACT(s) (Environment):
Progress/OE database uses first index on the table when a simple "for each" query is run against it
Windows
Progress 9.1x
OpenEdge 10.x
CAUSE:
This is working as designed. Progress/OpenEdge does not have any control over the optimizer of the foreign database. What you ask is what you get. If you want to get the record set in a specific order, you need use "BY" clause or "USE-INDEX" on the 4GL/ABL query.
FIX:
Use "BY" or "USE-INDEX" on the query.
You can also use QUERY-TUNNING parameter "NO-JOIN-BY-SQLDB" for specific queries which will be then joined and sorted on the client side instead of server side. By default it is JOIN-BY-SQLDB. Use of this parameter may provide you with the similar behavior as the progress/OpenEdge Database, that is, use of the first index on the table. HOwever, this will cause the DataServer to parse one SQL query for each of the tables on the join in the 4GL/ABL queries, therefore, will cost performance. There is a client startup parameter "-nojoinbysqldb" as well which is used with "-Dsrv" switch. Using this startup parameter is not suggested as it will affect all the join queries to be performed on the client for the entire session.