Navision SQL tables ...

Business Central/NAV Technical Forum
1

Hello,

I just restored our native Navision database backup to a MS SQL server to begin testing using SQL server over the native file database. Initial testing shows it runs faster for most activities.

In looking over the Navision SQL tables that were created in the MS SQL database, I see the names I would expect to see, but there are also some where the name is just the object number and not the name; plus multiple tables for the same table object in Navision.

Example:

dbo.[companyname_$37$3]
dbo.[companyname_$37$1]
dbo.[companyname_$Sales Line]

Table 37 is the Sales Line table. Why would it be split across 3 entries in SQL ? Is this normal when using MS SQL server for a Navision back-end? In the object browser in Navision, there is only one table with ID 37, and its called Sales Line.

Thanks for any input.

Eric

2

Those are tables used by NAV on SQL Server to simulate SIFT. They store bucket values of sumindexfields.

3

In general, SQL runs slower then Navision-DB if you didn’t optimize the application for SQL (=reviewing indexes, SIFT-indexes, …).
The extra tables you found are one of the reasons it is slower. This because for a SIFT-field, SQL has to do extra writing to maintain the SIFT on SQL. So instead of writing 1 record, you might be writing 10’s of records on SQL.
Search the forum or Mibuso for more info on this. Remember, though, that this optimiziation is something to be done by a specialist.

4

Remember, though, that this optimiziation is something to be done by a specialist.

Indeed. You could find those specialists - at least one - in this user group … [;)]

5

Check out www.sqlperform.com

I attended a course about SQL optimization in the beginning of last year and learned some very usefull tricks etc. Just by playing around with SIFTs and gaining a proper understanding of how SQL and Navision work together can really make big improvements on the performance.