Navision 2.6E performance problem

aromanocohen

I hope someone can Help me!!!

I'am using Navision Financials 2.6E with Microsoft SQL Server 2000 and I'am experiencing a lot of performance problems and some times Navision stop responding for a long time I'am very shure that it must be locking isues, and I know that the SQL command SET LOCK_TIMEOUT can help me solve the locking problems, but I need to know if Navision can recognize the error generated by SET LOCK_TIMEOUT and make a RollBack to all the transaction in case of long wait.

My second question is if someone know what are the best programing practices in C/AL to minimize the bloquing of tables and resources in the SQL DATABASE?

Thank you very much.

bbrown

Are you sure it is a locking problem and not just poor system performance?

Lock_Timeout must be set at the beginning of each connection to the database. I am not aware of any way to do this in 2.60.

A couple of SQL coding tips:

Use a SETCURRENTKEY statement before accessing a table. Even if you are using the primary key.

Avoid filtering on fields that are not in the current key.

Filter all fields in the key, and filter them in order.

Avoid the use of '<>' in filters, this will cause SQL to do a table scan.

aromanocohen

Thank you very much for the coding tips, I will optimize a lot of code with that. O:)

I have programmed an SQL trigger in a Navision table that sets de LOCK_TIMEOUT each time someone logs in to the database, but I'am not so shure that SQL is setting it in the proper session because I understand that NAVISION also creates an 'sa' session each time a user logs on and I would like to know if NAVISION 2.6 can handle the SQL error 1222.

bbrown

I can't think of a way to return an explicit sql error code caused by a Navision command back to the client.

What are you trying to accomplish with Lock_Timeout. This impacts the user waiting on the lock and does not effect the lock.

What evidence do you have that this is a locking problem? What resources are being locked? What user processes are causing the locks? What user processes are waiting on the locks?

aromanocohen

I know that not all is a locking problem, but some times the clients stop responding for a big period of time, and I need to shutdown the SQL server and start it again to solve the problems and this occurs 2 or 3 times in a day. I also have used the SQL Profiler and it reports that I have deadlocks, so, in this case the SET LOCK_TIMEOUT can work because it will kill the two proceses that are causing the Deadlock.

bbrown

Lock_Timeout is not a solution for deadlocks (it's like cutting of your arm because you have a blister on your finger). Troubleshoot and resolve the cause for the deadlock. Also, since SQL will terminate one of the processes to end the deadlock, why kill the other one? Use traceflag 1024 in SQL to help isolate any deadlocks.

Why do the clients stop responding? Rebooting the server is not a solution. Troubleshoot and resolve the problem, don't react to the symptoms.

A couple of thing to look at on clients stopping to respond:

(no particular order)

1. Is AutoGrow turned on? When SQL autogrows a database file (data or log) it stops responding to all other request until the growth completes. A poorly performing disk system can increase this delay.

2. Transaction log checkpoint. When SQL executes a checkpoint on the transaction log, it hold all other disk request until the checkpoint is completed. Again, a poorly performing disk system can increase this delay.

3. Using poor key selection and filtering in client processes.

aromanocohen

Thank you very much for your comments, I don´t want to abuse from your kidness, but I have a lot of questions and I hope that you can help me.

1-I have started to modify the code introducing the SETCURRENTKEY before each filter that I make, but I have a few tables that have exceeded the number of Keys that I can add (40), so, It helps if I select the most alike key that I can find or it is better not to use a key?

2.-What effect does the LOCKTABLE commands has over the database in SQL, it Locks all the table and no one can read it or modify it until the process is done? I need to avoid it if I can?

3.-I know that I need to programm my processes (Transactions) as short as possible so the resources of the database don´t stay locked too long, but I have a lot of batch proceses that do a lot of things And I dont want to use the COMMIT command because if the transactions interrupts in the middle I'will loose the consistency of the data. What can I do?

4.-I have a few computers on the client side that are very old, but they can run NAVISION, what effect do they have over the performance of the database? relevant or not relevant?


Thank you in advance, and I hope that my question don´t bother you.
O:)

bbrown

1-I have started to modify the code introducing the SETCURRENTKEY before each filter that I make, but I have a few tables that have exceeded the number of Keys that I can add (40), so, It helps if I select the most alike key that I can find or it is better not to use a key?

Reduce the number of keys on your tables. Too many keys can have a negative impact on system prformance, as they must be updated whenever the table's data is changed.

SQL will retrieve data using the key it determines to be the most efficient, this may or may not be the same key specified in your SETCURRENTKEY statement. The SETCURRENTKEY statement defines the order in which data is returned to the client. Getting these two to use the same key improves performance.

2.-What effect does the LOCKTABLE commands has over the database in SQL, it Locks all the table and no one can read it or modify it until the process is done? I need to avoid it if I can?

The LOCKTABLE command in SQL causes all operations on the table to be SERIALIZABLE. It does not lock anything when executed, but the system will lock the resource when it is accessed. SQL will determine the locking level.

3.-I know that I need to programm my processes (Transactions) as short as possible so the resources of the database don´t stay locked too long, but I have a lot of batch proceses that do a lot of things And I dont want to use the COMMIT command because if the transactions interrupts in the middle I'will loose the consistency of the data. What can I do?

Using COMMITs in a long running batch processess can be useful. But you must take care in where you place them, and your application must be able to resume the process from the last commit. An example would be a batch process that imports a large number of sales orders. You could commit after a few and then sleep the process.

4.-I have a few computers on the client side that are very old, but they can run NAVISION, what effect do they have over the performance of the database? relevant or not relevant?

Every aspect of your system impacts performance (ome more than others). Navision does utilize the workstation's resources in several areas. A slow computer will have an impact on that user and may impact other users if it is holding onto resources required by the other users. Compared to the cost of lost productivity, new workstations are inexpensive. Get on a program to replace these over time.

kriki

bbrown wrote:

A couple of SQL coding tips:

Use a SETCURRENTKEY statement before accessing a table. Even if you are using the primary key.

Avoid filtering on fields that are not in the current key.

Filter all fields in the key, and filter them in order.

These are all ok for the Navision-DB. SQL decides for itself which index to use. Although he SETCURRENTKEY-thing is a VERY GOOD programming practice even on SQL.

aromanocohen wrote:

T4.-I have a few computers on the client side that are very old, but they can run NAVISION, what effect do they have over the performance of the database? relevant or not relevant?

Kick those out of your office and put new ones. You can have all the hardware you want, but if a slow client posts an invoice of 1000 lines and it takes 1 hour to do it, all the other clients cannot post invoices, G/L entries, Item Entries, ... because the entry-tables are locked to get the last entry no.

bbrown wrote:

1-I have started to modify the code introducing the SETCURRENTKEY before each filter that I make, but I have a few tables that have exceeded the number of Keys that I can add (40), so, It helps if I select the most alike key that I can find or it is better not to use a key?

Reduce the number of keys on your tables. Too many keys can have a negative impact on system prformance, as they must be updated whenever the table's data is changed.

You can do a technical upgrade (=upgrade the DB and clients, but keep your old objects) to 3.70 or also 4.00 SP1. With this you can use new tools for performance on SQL. E.G. Don't delete the indexes in navision, but DON'T MAINTAIN all of them (and the same for SIFT-fields) on SQL. SQL prefers small keys. E.G. key in T21 : "Customer No.,Open,Positive,Due Date,Currency Code". Best is to create a new key with only "Customer No,Open" in it and do not maintain the old on SQL.


Some other things:
-SQL has a maintenance-job to update its internal statistics for selecting keys. This job you should run from time to time.
-And last but not least (actually it should be my first advice):Search the forum for more info. There are LOADS of info on performance on sql. E.g. search for "sql and performance"

bbrown

These are all ok for the Navision-DB. SQL decides for itself which index to use

SQL will decide which index to use for retrieving data. However, the fillters you set, and the order you set them in will influence SQL key selection. On smaller tables, regardless of filters set, SQL will sometimes just use the clustered index.

The SETCURRENTKEY statement defines the order in which data is presented to the client. This my not be the order in which SQL retrieves it.

kriki

bbrown wrote:

SQL will decide which index to use for retrieving data. However, the fillters you set, and the order you set them in will influence SQL key selection.

I don't know about this. It is possible, but I think it is more the SQL-statistics that decide the key selection.

bbrown wrote:

The SETCURRENTKEY statement defines the order in which data is presented to the client. This my not be the order in which SQL retrieves it.

To make this clearer: the Navision SETCURRENTKEY in translated in a SQL "ORDER BY".

bbrown

Kriki wrote

...but I think it is more the SQL-statistics that decide the key selection

SQL uses statistics to determine how it will process a query. Since the filters set become part of the query, they will impact how it is processed. This behavior can be seen in Query Analyzer when you execute queries with filters and sorts (ORDER BY) that do not match table indexes.


BTW.. nice clarification on the SETCURRENTKEY = ORDER BY

aromanocohen

Thank you very much for your awnsers =D>.

I have more questions about performance in SQL and NAVISION:

1.-If I would like to locate a register in a table, what is faster for SQL, to use a GET or a SETRANGE combined with a FIND('-')?

2.-If I use a GET with a primary key I need to declare the SETCURRENTKEY before I use the GET?

3.-If some user in my network is posting a 1000 lines item entry in Navision every other users must wait until the post ends because NAVISION LOCKS the table 32 until the post ends. I think this is a design error in the application because it is not acting really like a multiuser application. What do you think about this?

Thank you.