Poor performance of SQL view

SimonW

I've created a view which joins Item, Item Variant and Stock keeping unit tables.
When running it from SQL 2005, the performance seems ok but from the object designer you can almost see each record being fetched.

Has anyone else experienced the same issue?

(System is based on NAV 5 sp1)

Thanks

Simon

kine

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...

SimonW

Thanks for the reply.

Does that mean there is nothing I can do to influence performance - I was wondering if I should have a simple primary key?

Simon

kine

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...

garak

how is your view designed? Subselects? joins? Union? Are there indexes defined? About how many datas we are talking here?
exist for the fields that you "join" from the 3 other tables there indexes so that the datas can be found fast?

Mauddib

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.

SimonW

I've been reading up on this - it might be the statistics are out of date and as a result it's choosing a bad execution plan.

Also, I've spotted that NAV 2009 sp1 seems much better with views.

Mauddib

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

SimonW

I've found this statement "OPTIMIZE FOR" - might be worth looking into.

kriki

SimonW wrote:

I've found this statement "OPTIMIZE FOR" - might be worth looking into.

This one : http://mibuso.com/blogs/kriki/2009/08/02/select-option-optimize-for-unknown/?

Mauddib

Appears to not work in Views either. Im beginning to think the only thing that works in a view is SELECT, FROM and WHERE