Slow view in linked SQL server

Please help ! Problem: When opening a table based on a view, based on a table in a remote server performence is very poor, it will take up to 25 sec to open the table in Attain. If the view or the external table is opened with the enterprise manager it will take approx 3 sec. Setup: Win 2K, SQL 2K sp3. Navision Attain 3.10 The View on the SQL running Attain is made with a simple “Select * from external_server.external_database.dbo.external_database” The table contains approx 10000 records. I have tried this operation with only 10 records in the external table, and here the performance was OK. Could this be an index / key problem, the primary key of the SQL and Attain table are the same. Best regards Ole

if i have understood your question you are trying to assess a table in navision using the object designer from a remote client. Are u using a navision client through leased line. If yes, Navision doesnot support this architecture. You need to use WTS or Citrix.

I should have been more specific, sorry I am running 2 seperate SQL-servers, one server is running our Navision database, the other is running a database used by a web-application. I wish to access tables on the server running the web database from within Navision. First I have created a linked server and then a view on the server running Navision, this view contains a select-statement like this “Select * from external_server.external_database.dbo.external_database” Then I have creaed a navision Table and set the property Linked object to true. Now i can open the table in Navision, and see the data in the table on the server running the web database, but the performance is very poor. best regards Ole

Hi, maybe it is because Navision is always adding the ‘ORDER BY’ option to the select statement.

We used the same architecture to show some data form Oracle DB in Navision SQL. And yes it was slow regarding the number of records we had (<1000).

I think Lars is right, it seems to be the way Navision generate the select-statement that is the problem. I will try to find another way to access the data. /Ole

Moved to SQL forum.