Guys,

We have a different situation here. We have BaaN V running on SQL Server. One of the baan session was running very slow and then we invesitgated using SQL trace, stats and execution plans. We found that the SQL server is using a different index (Say Index_1) all the time while in the Select statement in baaN session it is index_2. So it is not using the index we wanted and mentioned in the baan session. We could not figure out why SQL Server is not using the index that BaaN session is suggesting. Both the indexes index_1 and index_2 are properly maintained. What else do we need to check? In there any database driver file or something that needs to checked? Please suggest. Any help is appreciated.

Thanks a lot.

Baantechy.

just because you're saying:

where <table>._index1 = {value1, value2, value3}

doesn't mean that Baan will send a hint to use that index.

To be sure you should use an "hint use index1" in Baan 5.

If you're doing that and it's still not working then you should look into
your mssql_storage file and db_resource to see if you have anything
set strangely.

Dave

If you only used the index in a phrase included in your where clause, Baan will break down the index, look at the fields, and if there is a lower number index with the same fields, plus anyother fields in the where clause, it will use the lower number index. If you want to specifically stat which index to use, you must use an order by statement.

If you have used the order by, please post you SQL so we may take a look.

Following is the code:

select table1.*
from table1
|where table1._index1 = {:i.val1, :i.val2}
|and table1.val3 = :val3
|and table1.val4 = :val4
|order by table1._index1
where table1._index2 ={:i.val1, :i.val2, :val3, :val4}
order by table1._index2

Commented lines are the old code, which was running slow. So we created another index2 and changed the code. But it is still slow and the SQL Server tracing shows that it is using index1. I don't know whether Baan or SQL Server is interpreting differently internally.
Please help.

Thanks.

Having the actual SQL would give a better idea of what you are trying to accomplish. If you do not want to post it, you can send a PM.

Posting your problem once will do...