Dynamics User Group
Since 1995 - The Microsoft Dynamics Online User Community
Dynamics NAV Performance on SQL - My Two Cents » NAV/SQL Troubleshooter’s Wish-List
NAV/SQL Troubleshooter’s Wish-List

Ho, ho, ho … well, X-Mas is coming soon, time to make a wish-list! In my special case I would like to ask for several things making life with NAV and SQL easier. I'm not asking for "science fiction" features like "non-blocking posting routines" or "different code versions for C/SIDE and SQL"; but small (?) and smart enhancements.

Hope someone northern from here (not necessarily Santa C. from North Pole, someone from Vedbaek would be OK ;c) ) takes care about it …

  1. I want to have full access to all fields from system-table 2000000063 "Key". Especially to the field "SQL Index". And I would like to have access to the "SIFTLevels", too, so that I could define all via C/AL Code.
  2. I want new properties for "Tables" and "Keys" where I could define the SQL Server site "File-Group" where I want to store them.
  3. I want a new C/AL command like "SETINDEX", specifying if I would hint a specific index; not using the "$ndo$dbconfig" thing.
  4. I want a new C/AL command like "SETRECOMPILE" to specify if I would like to force the OPTION(RECOMPILE) for a query; not using the "$ndo$dbconfig" thing.
  5. I want an official list about all available NAV releases, service packs and updates (see Waldo's BLOG for reference).

And all should be neatly wrapped in a package labeled "MS Dynamics NAV 4.00 SP4". Probably that's just a start; to be continued …

What would you wish? Please add!

Posted 11-19-2007 21:06 by Jörg A. Stryk
Filed under: , ,

Comments

kriki wrote re: NAV/SQL Troubleshooter’s Wish-List
on 11-20-2007 11:58

6. In each table and each index a new property to define an index-fill factor for the index. The property in the table is valid for the table if there isn't one for an index in the table.

7. a command to be used with RESET, SETCURRENTKEY, SETRANGE/FILTER in which we define  a fieldlist we want => "SELECT [fieldlist] FROM" and not "SELECT * FROM". This could be also interesting as a property in the objects.

kriki wrote re: NAV/SQL Troubleshooter’s Wish-List
on 11-20-2007 12:03

8. File=>Database=>Tables=>Optimize in Navision: possibility to select a SQL-index to optimize. Possibility to select between indexrebuild/defrag/0-SIFT-cleaning. Also a new field : an indication of fragmentation of an index.

PeterW wrote re: NAV/SQL Troubleshooter’s Wish-List
on 11-20-2007 14:59

9. Specify sumindexfields to be included fields in the index in stead of in seperate SIFT tables

10. Function to temporary turn on and off maintenance of sumindexfields during batch updates/inserts

11. Notion of client-side resultset that can be searched/filtered locally i.s.o. quering the database every time.

kriki wrote re: NAV/SQL Troubleshooter’s Wish-List
on 11-21-2007 7:23

reply to 10. This already exists. Use "Key Groups". In NAV 5.0 there is a command to disable a keygroup and then enable it again.

reply to 11. This is (partly) done by the FINDSET-command.

12. Possibility to NOT use the primary key fields in the "order by" generated by a SETCURRENTKEY.

E.g. T37: SETCURRENTKEY("No.",Type); becomes "Order by No.,Type,Document Type,Document No.,Line No.". I would like to have "Order by No.,Type".

Gedas wrote re: NAV/SQL Troubleshooter’s Wish-List
on 11-23-2007 6:27

Somewhere was written that sumindex tables go away and Index Views come on.

So i guess some of wishes related to this are to be released and new wishes are on the way :)

waldo wrote re: NAV/SQL Troubleshooter’s Wish-List
on 11-23-2007 9:08

13. (As 13 is my lucky number Smile) A possibility to work with large texts.  I know this replaces my "WaldoNavPad", but it's something so common.

waldo wrote re: NAV/SQL Troubleshooter’s Wish-List
on 11-23-2007 9:09

14. Extended DataTypes (something I explained a while ago on my blog... dynamicsuser.net/.../extended-datatypes.aspx ):

Jörg A. Stryk wrote re: NAV/SQL Troubleshooter’s Wish-List
on 11-23-2007 9:26

15. User specific setup of "Cache Record Size", "Lock Timeout", "Always Rowlock"

16. User specific setup for "DEADLOCK_PRIORITY"

Jörg A. Stryk wrote re: NAV/SQL Troubleshooter’s Wish-List
on 11-23-2007 9:28

Reply to "Gedas": I'm not sure if we really want to SIFT Tables being replaced by "Indexed Views" ... ;c)

kriki wrote re: NAV/SQL Troubleshooter’s Wish-List
on 11-23-2007 13:37

reply to 15 and 16.

15+16 Why not a C/AL command we can use when needed? And it remains valid until the next COMMIT or (FIELD)ERROR.

Erik P. Ernst wrote re: NAV/SQL Troubleshooter’s Wish-List
on 11-24-2007 12:59

This post is really great!  

Daniel Ekvall wrote re: NAV/SQL Troubleshooter’s Wish-List
on 11-26-2007 0:54

17.

I would like the possibility of setting up security with defining what tables the user should NOT have access to.  I know I cannot get the years back I have spent on setting up security, but with this feature, I at least would be able to enjoy my senior years a little more :)

And I fully support #3 and #13 !

/Daniel

waldo wrote re: NAV/SQL Troubleshooter’s Wish-List
on 11-26-2007 8:19

18. Versioning

19. Automated Documentation

kriki wrote re: NAV/SQL Troubleshooter’s Wish-List
on 11-28-2007 0:35

Some extra for:

9) a property in which we define included fields (not limited to SIFT-fields) to the index. I know it is only useful for SQL2005 and not SQL2000, but this would be an extra reason to go on SQL2005 :-)

kriki wrote re: NAV/SQL Troubleshooter’s Wish-List
on 12-11-2007 9:23

20) for use with SQL2008. I forgot the name of the technology but the basic is that it is possible to index only records with certain values in the key-fields. E.g. In Vendor Ledger Entry is an index "Open,Due Date". This index is only usefull for records with Open=TRUE.

kriki wrote re: NAV/SQL Troubleshooter’s Wish-List
on 12-12-2007 17:53

21) a property on index to say if has to be ordered ascending (default) or descending.

Gregor wrote re: NAV/SQL Troubleshooter’s Wish-List
on 12-13-2007 11:06

To 5. : This would be great! And no more "Rollup Installer EXEs". I want to know and see what files are replaced... If there would just be a list of files beeing replaced would also be fine.

sven wrote re: NAV/SQL Troubleshooter’s Wish-List
on 10-01-2008 14:32

22: I would like to have a posiblity to start C/AL Batches by using the Server Agent job sheduler!

23: I would like to have someone who says you can use this Runtime with that SQL Build. And someone who says "Hallo there ist new build in for NAV"

Add a Comment

(required)  
(optional)
(required)  
Remember Me?

Copyright Dynamics User Group, 1995-2008, all rights reserved. The Dynamics User Group is not affiliated with Microsoft Corporation.