Sales shipment line Deletion

anandfssfss

Dear All,

Our sales shipment line table has more than 14 lacs of records out of which only 50 thousand records has quantity. Resulting which when we use the get shipment function it takes lot of time to select the shipment lines. Since in default functionality when we post a sales order, it takes all the line in the sales order to shipment lines. Even the "quantity to ship" field has zero values, because of this the size of the sales shipment line growing bigger. It causes more delay.

Now i want your suggestion. if i delete the lines from the sales shipment lines which has no quantity or zero quantity. Will it create any problem in the integrity of the system. We have not used sales shipment line in any of our reports. Please advice as it is very urgent.

Advance thanks to u all.

Anand

kine

If you have performance issues, it depends on which DB server - SQL or Native. I will prefer to first try to solve the performance problem by checking what is the bottleneck. And I will prefer moving invoiced shipments for older years into some "archive" table to have e.g. just this year and not invoiced shipments in the table and rest somewhere else from where you can move them back if needed (e.g. for printing them etc.).

rthsw

Hi, if i read your task correct, it is´not the speed of the system, it is the time, the users need to select the correct lines.

Yes, this function is not usabel in standard. Change the System to something with a filter, where are only the uninvoiced Shipmentlines for this user are displayed, and give the unser a chance to "select all" or "select marked" lines. Or use the Combine Shipment function, so navision will do all the work for combining Shipments for you.

miganggarrod

In our case of the 6.000.000 of records of the table Sales Shipment Line only 3% 180.000 have quantity different to zero.

I do not believe that is an output problem. Other tables not experimientan a similar growth.

Weekly update the estadisticas in SQL but he is not enough.

update statistics [xxxx$Sales Shipment Line] with sample 100 percent

We are thinking seriously to eliminate these records, or at least preventing to add those records that not ship, modifying the CU 80.

DenSter

There is this huge misunderstanding that having a lot of records in the table is a bad thing. SQL Server can handle tables with billions of records easily. Removing records is not going to alleviate your problem, it's only going to postpone the day when you can't remove any more "useless" records.

The problem is most likely indexing. You need to research what queries run slow, what kind of filtering is applied, what kind of ordering is applied, what kind of durations you are getting, how many reads those queries generate, stuff like that. Based on that, you probably need to do some index tweaking, and that will speed up the system.

Alex_Chow

DenSter wrote:

There is this huge misunderstanding that having a lot of records in the table is a bad thing. SQL Server can handle tables with billions of records easily. Removing records is not going to alleviate your problem, it's only going to postpone the day when you can't remove any more "useless" records.

In the case where the client wants to delay or postpone moving to SQL as long as possible, delete the sales shipment records is okay. NAV requires you to print a physical copy of the document before you can delete it.