LOCKTABLE & SQL in 3.7a

AdvStore

Hi, I've got kind of a strange one. We just got a new server for running Navision with SQL and I'm doing some testing. For some reason, navision seems to be doing a LOCKTABLE even with simple MODIFY and DELETE instructions - LOCKTABLE is not being called explicity.

I wrote a routine to quickly run through the Location table and perform a MODIFY on each record. In the client monitor, it shows a LOCKTABLE instruction before it start doing the modifies.

Has anybody seen this behavior before or have an explanation? Am I reading the client monitor wrong?

Thanks,

Chris

kine

I think that you have some overlooked locktable somewhere (try to run client monitor and code coverage and process the data through extended client monitor and look, where the locktable is called in code), because modify only lock record, and if you can see in client monitor locktable, it means, that it is somewhere explicitly called...

megawavez

Here the code:

IF location.FIND('-') THEN REPEAT
location.MODIFY;
UNTIL location.NEXT = 0;

There are no ranges set and no explicit call to LOCKTABLE. I know this should only do row level locking, but the client monitor show "LOCKTABLE". Perplexing.

kine

And some code before???

fl

Hi,

According to my information, SQL doesn't lock a table when you place the locktable command.

But, it is very important to use the right key. You MUST always add the setcurrentkey command. Also the setrange and setfilter commands afterwards MUST be placed in the same order as the fields of the key used. Also the there may be no holes left in those fields compared to the key, and ofcourse you may not filter on fields not in the key.

If you don't follow these rules by SQL your application will work but you have performance loss. I geuss this is happening at your server.

Also do check chapter Performance of Application Designer Guide, which you can find on product CD

Francois

DenSter

Is it maybe because you don't set any filters on the Location table, that Navision recognizes that you're accessing all the records and locks the table? I am just brainstorming, I don't actually know . Have you tried setting filters though and checked what happens?

AdvStore

Hi,

I just got a response from our Navision vendor who contacted somebody from Microsoft - it looks like the tables are not actually being locked, but the client monitor is just displaying the CA/L code. Thanks for the responses.

Chris

ngebhard

fl wrote:

Hi,

According to my information, SQL doesn't lock a table when you place the locktable command.

But, it is very important to use the right key. You MUST always add the setcurrentkey command. Also the setrange and setfilter commands afterwards MUST be placed in the same order as the fields of the key used. Also the there may be no holes left in those fields compared to the key, and ofcourse you may not filter on fields not in the key.

If you don't follow these rules by SQL your application will work but you have performance loss. I geuss this is happening at your server.

Also do check chapter Performance of Application Designer Guide, which you can find on product CD

Francois

Hi everybody,

do you know if there is a written document where I can read something about this "SQL programming rules" that Francois mentioned? I always hear about special programming for SQL but I still don't know exactly how to do it or at least where to get informations about it. Would be interesting..

Thanks for your hints.
Regards,
Nicole

kine

May be that better will be wait for SP1, because there will be (I hope) some more documentation, because in SP 1 there are new COMMANDS for programming under MS SQL...