Poor performance of SQL view

It is because different access to the data - on SQL the server will return whole dataset, NAV will use cursor to go through the data...

All depends on how the view is constructed, if it could be somehow simplified (some joins could be done better through sub-selects etc. to not read so many data), you could add indexes to the view (creating indexed view), it is just what comes to my mind...

I have done something similar and I have a problem.

I have tested all this with a really simply view. Just to see how it all works.

Basically:

1) I have a field on the vendor table
2) I select all records of type Order or Invoice from the purchase line table WHERE
3) "Buy from vendor no." is in Vendor table WHERE
4) The field on my vendor table is blank

Seems very simple. I ran it on my test system and its lovely. So lovely that reading a table in company1 from company2 is faster this way than using a CHANGECOMPANY and applying the same filters.

I ran it on my LIVE system and it crawled.

After much searching I found that for someone reason SQL is selecting index $13 in test and $14 in live (when reading the Purchase Line Table) Even if I clear caches and all this appears to remain.

SO HERE IS MY QUESTION:

In the select statement I added a "WITH INDEX $13" type command and it worked lovely. However when I try to move this into my VIEW I am told that WITH INDEX can not be used in Views.

Am I doing something wrong, is there another option, or can I simply not use this select statement, which normally works, inside the code of a view?

Here is essentially the code I am using:

USE [navtestsql]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[PO Global View] With SCHEMABINDING
AS

select
 'company1' AS "Company",
 "Document Type" AS "Document Type",
 "Document No_" AS "Document No_",
 "Line No_" AS "Line No_",
 "Expected Receipt Date" AS "Expected Receipt Date",
 "No_" AS "Item No_",
 "Outstanding Quantity" AS "Remaining Quantity",
 "Location Code" AS "Location Code"
from [dbo].[company1$Purchase Line]
--WITH (INDEX ([$14]))
where
 "Document Type" IN (1, 5) and
 "Type" = 2 and
 "Outstanding Quantity" <> 0 and
 "Buy-from Vendor No_" in (
   select "No_" from [dbo].[company1$Vendor]
   where "Internal Vendor" = 0
 )

You can see where the WITH INDEX is, which works as a normal SELECT but not in a VIEW.

Hrmmm is this the Simon I used to know I can not tell.

Yea there are likely reasons why the bad stuff is happening and I am looking into that too. I hope to find it. However the main question I have here is how to do a WITH INDEX in the SQL statement. I just can not seem to work it and I would like to solve that one in parallel to the main issue too. Not just because it will also solve the problem, but because its a question I do not have the answer to and I hate those

Gavin