Poor performance of SQL view

SimonWSimonW Member Posts: 77
edited 2009-12-10 in SQL Performance
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

Comments

  • kinekine Member Posts: 12,562
    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...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • SimonWSimonW Member Posts: 77
    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
  • kinekine Member Posts: 12,562
    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...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • garakgarak Member Posts: 3,263
    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?
    Do you make it right, it works too!
  • MauddibMauddib Member Posts: 269
    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.
  • SimonWSimonW Member Posts: 77
    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.
  • MauddibMauddib Member Posts: 269
    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
  • SimonWSimonW Member Posts: 77
    I've found this statement "OPTIMIZE FOR" - might be worth looking into.
  • krikikriki Member, Moderator Posts: 9,086
    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/? :wink:
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • MauddibMauddib Member Posts: 269
    Appears to not work in Views either. Im beginning to think the only thing that works in a view is SELECT, FROM and WHERE :)
Sign In or Register to comment.