Bulk Item No Change routine

kamranshehzad

Hi
I have written some code to change items nos, but it takes a lot of time.
the current process i tought was to import the new codes into a table and write a codeunit to read the table and rename item records one by one as it takes time. but unfortunately, this takes a lot of time and in 24 hours i am only able to change 1000 item codes and i have got 38000+ items in our database.

I am thinking about alternative, i try to look at queries generated by running sql profiler but that doesn't seem a good solution. now i am thinking i should be doing something like this.


write a program in c# that calls the code unit (either through c/front i am not sure i can do this or not) or webservice. the program will be multithreaded and will be executing 100's of concurrent threads and will modify the items as per devised algorithm. I can think of anything else unless some one can advise me a better solution.

I am using nav2009 sp1.

regards,

kine

I think that renaming one by one is best. Just add commit after few renames (or one) to separate the transactions and not t have big locks. The way with more threads will end on locking issues...

kamranshehzad

yes .. i thought about it and i'll commit after each transaction in each thread. .and will try to mark the items in process to make sure there are no deadlocks..

i am stilling thinking about it..
regards,

FDickschat

Change the Object Cache setting in the client to 100MB or more. The more the better. Use a Gigabit Connection to the server.

In a customers DB with ~10Mio Item ledger Entries and ~25Mio Value Enries the rename of the first item takes approx. 1-2 minutes. But the next one is just a few seconds.

kamranshehzad

FDickschat wrote:

Change the Object Cache setting in the client to 100MB or more. The more the better. Use a Gigabit Connection to the server.

In a customers DB with ~10Mio Item ledger Entries and ~25Mio Value Enries the rename of the first item takes approx. 1-2 minutes. But the next one is just a few seconds.

I did try that by setting object cache to 250 mb and it increased the change item no process bit faster but for 38000 products it will take couple of weeks..still i can't find it a good solution....

i can not use cfront as it wont execute triggers...so i am not sure how can i change call it from any external process..perhaps i needto write a multi threaded com object..to handle this...by calling it in a code unit.. not sure...yet may be some one can advise a faster solution..

regards,

FDickschat

I just tried it in a DB with 5Mio ILEs / 8 Mio VEs. The DB is a test DB on a SQL server with 160GB in total, with no statistics at all, no maintenance plans on very few slow local disks.

1st Item: 2:30 Min
2nd Item: 25 seconds (same time for 3rd, 4th, 5th item)

Calculating with 25-30 seconds per item you would be able to do ~3000 items per 24 hours. :-k

Some 12 years ago I had to do the same in an old DOS based NAV installation. But there the rename function was ridiculously slow (3hours+ for 1 item). So we ended up in programming a report which does the renaming (3 minutes per item).

If your DB resides on a SQL server I would suggest to write a sql script which does the job for you.

kamranshehzad

I did look into that by running the profiler and check the sql queries. there are huge no of queries generated by system. ill look into that again. it will be be alot faster to do it in database rather than using. i am just scared to miss any query or table to update..

but it does take alot of time and is currently processing almost 2 items / minute.

regards,