Item List opening very slow

Kishore_rokkam2009

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

ufuk

Are fields and code of that list same before and after upgrade?

Kishore_rokkam2009

No,some new features are added in 2009

ufuk

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.

Belias

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?

Alex_Chow

Try taking out all the flow fields from the item list and see if performance improves.

stryk

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

stryk

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

i remembed nav2009 sp1 (first build) uses dynamic cursor for browse (on lists), too.. :-k

stryk

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)

Belias

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!

Kishore_rokkam2009

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

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

Mally

Nav 2009 SP1 Build No. is 29626

Belias

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

Belias

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
Let us know the results!