Slow delete from Reservation Entry Table

Alex_Chow

We have a client that receives and ships using Lot numbers.

Over the years, the Reservation Entry has become quite large. Now it takes forever to run the Requisition Worksheet.

When using the client monitor in addition to the SQL Server Profiler, this statement seems to be taking a long time.

DELETE FROM "ClientServer"."dbo"."CRONUS Company$Reservation Entry" WITH (REPEATABLEREAD)  WHERE ("Entry No_"=@P1 AND "Positive"=@P2)

The process where this slows down is in codeunit 99000853 - Inventory Profile Offsetting. In the DeleteTracking function, there's a code to loop through the Reservation Entry table, then it does a delete.

I noticed that no index was used when you delete. Is there a way to improve the performance of the delete?

I'm using NAV2009 without the SP1.

kine

Over the years, the Reservation Entry has become quite large

Why? There should be only entries for active documents, nothing "historical"... Thus number of entries could be bigger if you have bigger count of opened documents/lots/ser.nos., but not "Over the years" like posted entries...

And second thing - have you some SIFT maintenance? (deleting zero SIFT records)

David_Singleton

Was this an upgraded database? Is it possible it has the bug where there is no clustered index on the primary key?

Alex_Chow

kine wrote:

Why? There should be only entries for active documents, nothing "historical"... Thus number of entries could be bigger if you have bigger count of opened documents/lots/ser.nos., but not "Over the years" like posted entries...

You're right. There are unusally a large amount of reservation entries. Even so, I'm not sure why it takes so long to delete?

kine wrote:

And second thing - have you some SIFT maintenance? (deleting zero SIFT records)

I thought the delete 0 Sift records doesn't apply to VSIFT? I'll try it out.

Alex_Chow

David Singleton wrote:

Was this an upgraded database? Is it possible it has the bug where there is no clustered index on the primary key?

No, the clustered index is properly set.

Alex_Chow

Ok, I figured out what the problem is.

When you use the Get Receipt Line to do invoicing. The original PO doesn't get deleted so the item tracking entries remain open in the Reservation Entry.

After a purge of completed PO, it's a lot faster now. \:D/

kriki

Alex Chow wrote:

David Singleton wrote:

Was this an upgraded database? Is it possible it has the bug where there is no clustered index on the primary key?

No, the clustered index is properly set.

Don't trust that property in NAV. Check directly in SQL. I have a customer (recently) in which the property is set, but if I check in SQL, it is not set. After all, I think I will write a blog about it.

Alex_Chow

kriki wrote:

Don't trust that property in NAV. Check directly in SQL. I have a customer (recently) in which the property is set, but if I check in SQL, it is not set. After all, I think I will write a blog about it.

How can that be? If this is the case, how do you enable it? Turn it off, save, then turn it back on?

kriki

Alex Chow wrote:

kriki wrote:

Don't trust that property in NAV. Check directly in SQL. I have a customer (recently) in which the property is set, but if I check in SQL, it is not set. After all, I think I will write a blog about it.

How can that be? If this is the case, how do you enable it? Turn it off, save, then turn it back on?

Well, I didn't wait to long to blog about it: here it is http://mibuso.com/blogs/kriki/2010/03/18/clustered-or-not-clustered/.