SQL Indexes in Navision Tables

Hi… I’m working with a SQL Navision installtion, 3.6 executable with 3.1 objects. I’m developing a VB app that pulls inventory information from Warehouse Ledger Entry and Warehouse Document Line (total inventory and allocations by Item No.). The performance of my SQL queries is lousy due to the extremly large number of records to be summed. I’m thinking of adding a SQL index to on each table on Item No. I would do this through SQL since obviously SQL doesn’t see the existing Navision indexes. Is this OK or something to be avoided? Will Navision have problems as a result? Initial testing on a small local database showed no problems… Thanks for any ideas. Chandler Bryan ccbryanDOG@Yahoo.com (Remove the pet to email me)

Hi Chandler, If you read the Navision SQL manual you will find something that might help you with your problem. The idea is that when you create keys with SIF in NAvision on SQL they get stored in another table. It is hard to explain it here so just read that and it might help. If not at least you will know more about how Navision works :slight_smile: Cristi Nicola

When defining a key on a table in Navision, you have the option of creating a SQL index for this key or not.

SQL can’see’ your Navision keys, because Navision creates regular SQL indexes, provided the above-mentioned property is ‘Yes’. Alternatively, you can create indexes in SQL (in Enterprise Manager or whatever) and they will cause no problem in Navision during run-time. If you design a table in Navision with external indexes created (or other constraint objects) there might be a problem, usually when deleting fields, but it depends what you create. Basically any SQL objects you manage external to Navision you should script them so that you are able to delete and recreate them if the need arises.