SQL 2005 Performance Issue

Comments

• Options

  Mauddib Member Posts: 269

  2007-11-08

  KB943858 I beleive.

  0
• Options

  Martin_Brülisauer Member Posts: 10

  2007-12-12

  bbrown wrote:

  Experiencing random system hangs when scrolling thru list forms.
  
  Example:
  
  1. Open Customer Card and press F5 to open Customer List. Scroll to buttom of list then start to scroll up.
  
  Any thoughts? :-k

  
  The cause of the problem is easy to explain. The solution is difficult
  in NAV.
  
  Explanation:
  NAV does SELECTS in both orders, ascending and descending.
  Form 21, Customer Card -> Ctrl-F5 -> Form 25, Customer Ledger Entry
  
  Example if you scroll down:

  ---

  SELECT * FROM "CRONUS (Schweiz) AG$Cust_ Ledger Entry" WHERE
  (("Customer No_"='10000')) AND "Customer No_"='10000' AND
  "Posting Date"={ts '2007-12-31 00:00:00.000'} AND
  "Currency Code"='' AND "Entry No_">=2480
  ORDER BY "Customer No_","Posting Date"
  
  Example if you scroll up in the same form:

  ---

  SELECT * FROM "CRONUS (Schweiz) AG$Cust_ Ledger Entry" WHERE
  (("Customer No_"='10000')) AND "Customer No_"='10000' AND
  "Posting Date"={ts '2008-01-21 00:00:00.000'} AND
  "Currency Code"='' AND "Entry No_"<2700
  ORDER BY "Customer No_" DESC,"Posting Date" DESC
  
  If your Customer Ledger Entry table contains some millions of records
  your sqlserver will be overloaded with disk IO because NAV only creates
  ascending indices and you do not have any possibility to create a
  descending index.
  
  Solution:
  Find you key in management studio and create the corresponding
  descending key for each key the user wants to use and each company
  in your database. The above example would be solved creating the
  sql index
  

  CREATE UNIQUE NONCLUSTERED INDEX [Reverse$1] 
  ON [dbo].[CRONUS (Schweiz) AG$Cust_ Ledger Entry] 
  (
  	[Customer No_] DESC,
  	[Posting Date] DESC,
  	[Entry No_] DESC
  )
  WITH (
    PAD_INDEX  = OFF, 
    STATISTICS_NORECOMPUTE  = OFF, 
    SORT_IN_TEMPDB = ON, 
    IGNORE_DUP_KEY = OFF, 
    DROP_EXISTING = OFF, 
    ONLINE = OFF, 
    ALLOW_ROW_LOCKS  = ON, 
    ALLOW_PAGE_LOCKS  = ON)
  

  
  To generate DESC queries without creating the corresponding sql index
  is a bad kernel design within NAV. The design team would find these
  errors if they tested using real load databases and no only fancy
  CRONUS stuff.

  "There are only 10 types of people in the
  world: those who understand binary, and
  those who don't!"

  0
• Options

  bbrown Member Posts: 3,268

  2007-12-12

  :shock:

  There are no bugs - only undocumented features.

  0
• Options

  Sorcerer Member Posts: 107

  2007-12-17

  for anyone who wants to update to sql sp2 cumulative update 4 and uses a (x64) cluster:
  
  http://support.microsoft.com/default.aspx?scid=kb;EN-US;943525
  
  otherwise the sql agent doesn't work any more...

  0
• Options

  Martin_Brülisauer Member Posts: 10

  2008-01-30

  This issue has been solved by the Microsoft Dynamics NAV engineering.
  Get the "Platform Roll-up Update for Microsoft Dynamics NAV 5.0
  (Build Number 25359) (KB943858).
  
  After the update you won't see any DESC sql queries. The usual
  application issues still exist (e.g. FIND(+/-) without SETCURRENTKEY()).

  "There are only 10 types of people in the
  world: those who understand binary, and
  those who don't!"

  0
• Options

  Marije_Brummel Member, Moderators Design Patterns Posts: 4,262

  2008-01-30

  Build 25359 contains a blocking bug.
  
  Use 25653 instead.

  Marije Brummel | Freelance Business Central Specialist

  0
• Options

  DeputyDogh Member Posts: 7

  2008-07-28

  bbrown wrote:

  Experiencing random system hangs when scrolling thru list forms.
  
  Example:
  
  1. Open Customer Card and press F5 to open Customer List. Scroll to buttom of list then start to scroll up. Sometimes it works fine, others it will take 30 seconds+ to move up 1 customer.
  
  Also similar behavior in other list forms with larger record sets.
  
  Any thoughts? :-k

  
  
  Are you experiencing the same with the "Item Card" and "Item List" ?
  I've experienced the same problem there and have fixed this issue by simply deactivating the BLOB field. (I didn't need it, and if i did, i would place it in another table)

  0