I have a scenario of having an oracle database on one server and sql server database on other server but belong to same domain. I have Navison 4.0 sp3 database in sql server 2000. My aim is to fetch data from oracle database. Now i have sql views to fetch data from oracle through link server which works fine and it is fast. But when i connect these views to navision by table property linkedobject and run the table i face alot of delay. My question is how can make this fast? any ideas?
When you write
SELECT something FROM linkedtable WHERE bla bla bla
in SQL Server Management Studio, request is proccesed by SQL Server, and it IS fast. As soon as you try to do the same in NAV, query actually is processed at NAV Client, it fetches ALL data and filters it there.
If WHERE clause results in 5% of all rows, result is slow. So NAV is built, actually it doesn’t use the power of SQL Server at all.
With every next NAV version things get a little better, but using SQL at full blow needs complete redesign of NAV and will happen only then, when Microsoft says goodbye to Native database. This is promised after some 2-3 next NAV versions, read years…