Calcsums on Sift view causes a lock of the main table.

KYDutchie

Hi everyone,

we are working on an issue with a customer of ours. The customer wants to run the Account Schedule reports several times a day by several users with multiple sessions per user at the same time.
During the printing of these reports, it issues a lot of CalcSums, about 4800 on a dataset of 8Million records, on the SIFT View, specifically the "G_L Entry$VSIFT$2".
We have reduced the run time of these reports from 1 hour to less then 3 minutes, but the customer is still not happy, because during the run of these reports, nobody can post.

My question to all of you out there is, is there a way to force the query on SQL not to lock the main table, GL Entry, during the calculation of the CalcSums?

The customer is running Dynamics NAV 5.0SP1, SQL 2005 SP3 and the NAV client is on the latest update level.

Thanks in advance,

Willy

ara3n

can you paste the sql statement that is run on the vsift views?

The TransactionType for the report is <UpdateNoLocks> so it should not lock any table unless it is modifying a record

KYDutchie

ara3n,

thank you for your response. I double checked to make sure that the transaction type was set to UpdateNoLock, but it was set to SnapShot
I changed it to UpdateNoLock and retested it. It still gave the lock.
But when I changed the transaction type to <Report> it did not seem to give the lock.

We are currently testing it with the customer.

Thanks for your help,

Willy

ara3n

You are welcome and good luck.

KYDutchie

Rashed,

THANKS for putting me on the right track. Setting the Transaction type to <report> worked! =D> =D>

Willy