Item List opening very slow

Kishore_rokkam2009
Kishore_rokkam2009 Member Posts: 4
edited 2011-04-06 in NAV/Navision Classic Client
Hi to all,

After upgradation from Nav 4.0 sp1 to 2009 sp1 with ls retail addon ,If i tryed to open the Item List its taking the too much time , Pls find the attched file having the client monitor screen shot with marked. Can u people sovled that marked area showing time details and parameter of sql statement and sql Index...kindly have any soluion....pls reply me....

Comments

  • ufuk
    ufuk Member Posts: 514
    Are fields and code of that list same before and after upgrade?
    Ufuk Asci
    Pargesoft
  • Kishore_rokkam2009
    Kishore_rokkam2009 Member Posts: 4
    No,some new features are added in 2009
  • ufuk
    ufuk Member Posts: 514
    CM shows nested loops and aggregate functions so I think you suppose to check performance in standart form and then compare it with your custom form starting with the onaftergetrecord trigger and flowfield modifications.
    Ufuk Asci
    Pargesoft
  • Belias
    Belias Member Posts: 2,998
    can you post here the entire query run by sql?what are the arguments of the WHERE clause of your select?
    i guess that you have a problem on fast forward/dynamic cursor.
    Before the FFWD were used, now, with your release, nav is probably using the dynamic one: what's the build no. of your nav version?how many items do you have in the database?
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Alex_Chow
    Alex_Chow Member Posts: 5,063
    Try taking out all the flow fields from the item list and see if performance improves.
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • stryk
    stryk Member Posts: 645
    Belias wrote:
    can you post here the entire query run by sql?what are the arguments of the WHERE clause of your select?
    i guess that you have a problem on fast forward/dynamic cursor.
    Before the FFWD were used, now, with your release, nav is probably using the dynamic one: what's the build no. of your nav version?how many items do you have in the database?
    The "Curse of the Cursor" only affects C/AL Code queries, not calls from a Form (means: in recent NAV versions queries from "Forms" use "Fast Forward Cursors") ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • stryk
    stryk Member Posts: 645
    Alex Chow wrote:
    Try taking out all the flow fields from the item list and see if performance improves.
    Indeed!
    The major change with the upgrade was the replacement of SIFT with VSIFT, thus it is very likely that some FlowFields perform differently.

    If you record this using "SQL Profiler" (e.g. filter on "Reads" >= 1000) you should see the affected queries ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Belias
    Belias Member Posts: 2,998
    i remembed nav2009 sp1 (first build) uses dynamic cursor for browse (on lists), too.. :-k
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • stryk
    stryk Member Posts: 645
    Belias wrote:
    i remembed nav2009 sp1 (first build) uses dynamic cursor for browse (on lists), too.. :-k
    That's true - the FFC on Forms is only used by the most recent versions ... (I don't know from which buils numbers on)
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Belias
    Belias Member Posts: 2,998
    then, let's wait to know for the build no. (or better, Kishore_rokkam2009, look at your build no. and search google/bing/partnersource for what cursor is used for browsing lists, then let us know the result :whistle: )
    Thanks!
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Kishore_rokkam2009
    Kishore_rokkam2009 Member Posts: 4
    How we replace of SIFT with VSIFT ?


    stryk wrote:
    Alex Chow wrote:
    Try taking out all the flow fields from the item list and see if performance improves.
    Indeed!
    The major change with the upgrade was the replacement of SIFT with VSIFT, thus it is very likely that some FlowFields perform differently.

    If you record this using "SQL Profiler" (e.g. filter on "Reads" >= 1000) you should see the affected queries ...
  • Belias
    Belias Member Posts: 2,998
    you can't just shift between sift with vsift!
    it is the new version of nav that dropped the (old) sifts and repaced them with vsifts...but i think you don't know what are sifts/vsifts, if you're asking this question...you should read some documentation about them because sumindex technology is a key feature.
    By the way, What is your nav build no.?
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Mally
    Mally Member Posts: 128
    Nav 2009 SP1 Build No. is 29626
  • Belias
    Belias Member Posts: 2,998
    Once upon a time, i had a sheet with the build nos. of nav and their cursor type. Unfortunately I've missed it, but i can say that your version is the first one, so i'd try to upgrade to a newer bulild no, or better to nav 2009R2.
    If i'll find that file, i'll post it here
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Belias
    Belias Member Posts: 2,998
    Found it! it's outdated, but you have the very first build of SP1, thus the dynamic cursor is probably your problem.
    It seems MS was a little confused about this cursors in the past ;) , but they finally made up their mind :mrgreen:
    Let us know the results!
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog

Categories