SQL and Wrong Key

ldreamsl · 2005-08-11

Hi to all,
I have a problem, we have moved the DB on a Sql server, and in some situations the performances of the system am notably lowered.
Looking for to understand something, we have performed the Trace with the Monitor client.

What we have ascertained that some time, to a SetCurrentKey ( Key1, Key2...), SetRange ( Key1,..) in Navision, it corresponds a totally wrong use of the key in SQL.

Does some know how to says me the because?

Urmas · 2005-08-11

What is your Navision client version? There were similar bugs in early navision SQL versions.

ldreamsl · 2005-08-11

Urmas wrote:

What is your Navision client version? There were similar bugs in early navision SQL versions.

Client 3.70B

kriki · 2005-08-11

SQL decides which key to use, based on the DB-statistics (you should run these automatically each day, for example after the daily backup). The SETCURRENTKEY of Navision is translated into an ORDER BY on SQL. So don't be alarmed if SQL uses another key to find the records.

See also my response in http://www.mibuso.com/forum/viewtopic.php?t=7235

ldreamsl · 2005-08-11

kriki wrote:

SQL decides which key to use, based on the DB-statistics (you should run these automatically each day, for example after the daily backup). The SETCURRENTKEY of Navision is translated into an ORDER BY on SQL. So don't be alarmed if SQL uses another key to find the records.

See also my response in http://www.mibuso.com/forum/viewtopic.php?t=7235

With the Client Monitor, I get: Time server: 300 ([ms]), then much fast the SQL Query, while, Elapsed Time: 25.000 ([ms]), anyone has determined this loss of time? and because?

Thanks.

kriki · 2005-08-11

I am just guessing:
I think that "Server time" is the time the CPU of the server was occuppied to find the records. "Elapsed Time" is the time it needed to do it, this because other processes also used time in the same moment and they had to share the time.

Maybe someone else has a better idea.

ldreamsl · 2005-08-12

he that torments me is the following: I enter from Form to a Table with a lot of records, Random, some time goes all normally, other time stops and be necessary wait a long time before that is frees.

Then is not possible that a time uses the key near, and an other be wrong key, I don't understand the motive of this.

This happens since are passed to the SQL DB.

kriki · 2005-08-12

-could it be that there are flowfields on the form and that sometimes some of them are not shown and other times they are shown? If a flowfield is not shown on the form, it is not calculated, so the DB does not lose time the get the data.
-it can also be that there is some extra processing on the form that sometimes doesn't trigger and sometimes it does.
-it can be that the first time it is slow, and the next times it is fast. This is because the next times, the data is in the DB-cache and it is faster to get the data. But this means something is read from the DB with bad indexes (Some filters on the table, some flowfields that are calculated with bad indexes)

These are all guesses.

ldreamsl · 2005-08-12

kriki wrote:

-could it be that there are flowfields on the form and that sometimes some of them are not shown and other times they are shown? If a flowfield is not shown on the form, it is not calculated, so the DB does not lose time the get the data.
-it can also be that there is some extra processing on the form that sometimes doesn't trigger and sometimes it does.
-it can be that the first time it is slow, and the next times it is fast. This is because the next times, the data is in the DB-cache and it is faster to get the data. But this means something is read from the DB with bad indexes (Some filters on the table, some flowfields that are calculated with bad indexes)

These are all guesses.

The

FilterGroup(2);
..
SetRange(..)
..
FilterGroup(0)

It may influence on the speed ???

On SQL the FilterGroup as they act??

kriki · 2005-08-12

FILTERGROUP will not influence the speed. It justs sets another filtergroup.
This can be used to put a filter in another filtergroup, and then getting back to filtergroup 0 to avoid that the user can see a filter or change it.
It can also be used to put 2 filters on the same field.
Eg. Give me all customers with an 'a' in their name and a 'b' in their name.

SETFILTER(Name,'%1','*a*');
SETFILTER(Name,'%1','*b*');

This will only retain the last filter.

SETFILTER(Name,'%1','*a*');
FILTERGROUP(6);
SETFILTER(Name,'%1','*b*');
FILTERGROUP(0);

This takes both filters.

In short : the filtergroup itself does not influence the speed. The SETRANGE,SETFILTER in a filtergroup influences the speed like a SETRANGE,SETFILTER without filtergroup.[/code]

ldreamsl · 2005-08-12

I am known the operation of the filtergroup. I wanted only know if the client in the translation in SQL istruction was able somehow influence the performances. The fact is that in the native DB works correctly, in the SQL DB are introduced this problems. Doesn't know more thing think.

SQL and Wrong Key

Comments

Categories