Nav 2.6 Client "hanging", but is actually stray SQ

rogerjm

Running Nav 2.6 on SQL Server 2000. Applications are fast, but then suddenly seem to get stuck on a random basis. Turns out that there is a huge, stray select running and hammering the database.

For example: The user views and Inventory Item, History. They then click on the <Window> menu to switch to another window and the client "hangs". Watching the database activity for the SPID of that session in SQL Profiler shows that a huge select is running. In this case it is a select from Item Ledger Entry for the Item just viewed, but somehow it does not use indexes. It ended up doing 12 000 000 reads and hanging the client session for about 15mins. If I take the same query and run it in Query Analyser, it does 16 Reads and is fast.

In another example we have a header/detail form. If we click on different lines in the detail form, it is fast, but suddenly, clicking on a line causes a killer select to run (which relates to the line which we had selected before). The same thing can happen when moving between fields with the arrow key.

This is completely random and does not relate to the specific data in the field. Moving between 2 fields can be quick for 20 or more clicks and then suddenly it fires off the select. As you can imagine on tables with many rows, a query which does not use the correct index kills the machine and keeps the client session waiting for anything up to 40 minutes.

Very strange! Has anyone seen this before? Thanks

kriki

Do you run the SQL statistics regularly?
It is possible that SQL decides to use a certain index that he thinks is good based on outdated SQL statistics.

rogerjm

We've tried all the obvious things and are convinced that this is not a simple query optimisation problem. As I said, picking up the query which Navision generates in SQL-Profiler and running it with Query Analyser is fast.

I guess my real questions are:
1. Why does Navision submit a select when you are leaving a field/window - there's no point in getting data when you're moving onto something else and nothing was modified.
2. Why do these queries cause SQL-Server to do so many reads, whereas the same queries run from Query Analyser are optimised. It's almost as though something tells SQL-Server to use the incorrect index.
3. Why does this behaviour occur on a random basis? One can sit on a screen and click between the same field on two different lines of the detail records and then suddenly and randomly the application appears to hang and SQL-Profiler shows a huge query has run. We get this jump from instant response to 10-30min delay.

Thanks

kine

I must say: 2.60 with SQL is not optimized for SQL... there can be problems (SQL option was just 2nd in the row...). Exactly which version you are using? 2.60f,g ...

rogerjm

Using Version W1 2.60.B (W1 2.60.D)

(If this problem is fixed by an upgrade then this might be the motivation we've been looking for to get the client to migrate their (highly customised) system to version 4. As an interrim step, we may try and use v4 SP2 client with the current code and see if this fixes the problem.)

Thanks

kine

Yes, it will be good to try newer client. If you do not want to do so big version jump, you can try to use client 2.60g - it has some SQL bugs fixed.