Hi,
We’re running BaanIVc4 using the level 2 driver with MS SQL Server 2000.
We've been noticing that in some sessions the performance was degrading abnormally.
We did a little survey and found this strange behavior:
When running the session tdsls3200m000 using the SQL Server Profiler we notice that function tfacr005 in a tfacr200 query the system was querying the data using the index 7 and in the script the query was wrote using index 2.
This was even stranger because the optimizer was turned off and running the query in the sql server against the optimizer it suggests the use of index 9.
Then we turned the optimizer on in Baan (adding NOIDXHINT in the sqlstorage file) and the problem got weirder because Baan kept querying the data using index 7 although the script stated index 2 and the optimizer suggestion was index 9.
We did the tests with porting sets 6.1c.07.07 and 6.1c.07.10.
Does anyone have an explanation for this?
Thank you

What does the query look like in Baan?

Dave

Originally it was like this:
select tfacr200.balc, tfacr200.dued from tfacr200
where tfacr200._compnr = :tfgld001.ncmp
and tfacr200._index2
inrange { :tmp.cuno, :min.ttyp, :min.ninv, :min.line, "", :min.docn, :min.lino }
and { :tmp.cuno, :max.ttyp, :max.ninv, :max.line, "", :max.docn, :max.lino }
and tfacr200.dued <= :tmp.date and tfacr200.balc > 0.001
as set with 1 rows

I changed it to:
select tfacr200.cuno, tfacr200.balc, tfacr200.dued from tfacr200
where tfacr200._compnr = :tfgld001.ncmp
and tfacr200._index2 = {:tmp.cuno}
and tfacr200.tdoc = ""
and tfacr200.dued <= :tmp.date and tfacr200.balc > 0.001
as set with 1 rows

and it got a little better but the index problem remained.

ok - a couple of things.

When you use _index2 in a selectivity statement, you're using shorthand, you're not specificing what index to use.

The only way to Force an index in Baan IV 4GL is to do an "order by <index name>"

also, it looks like indexes 7 and up are custom, what columns do they include?

Dave

Thanks. We changed the select clause and added the order by statement and it worked fine.
The indexes 9 to 13 are from the portuguese localizations. The index 7 is dued, cuno, index1 and 9 is cuno, dued, index1.
What's the point of putting indexx in a where clause? As far as I can tell the programmer has no control on this?
What is the purpose of NOIDXHINT statement in the sql_storage file? As far as I can tell it has absolutely no effect on queries.
I wonder now how many selects are like these.

Glad it worked!

the index in the where clause is just a shorthand (i want to match all of the columns in this index...) it's the same as doing
table.cuno = a
table.sern = b
and so on..

You may be formatting your mssql_storage incorrectly, those files are very picky, and if you've got an extra space or a colon or comma in the wrong spot it might not fly.

Otherwise, it may be a bug - call support! =)