I am working as the Developer /DBA for a company that has heavily modified the unposted sales document creation (Sales Header / Sales Line) And I wrote a Codeunit that imports records from an external table with ADO (that performance is not a problem) and creates sales headers / sales lines There are lots of business logic processed with sales line insertion, and I end up with alot of deadlocks on insertion of sales lines. I am on SQL Server 2000 and I have disabled the SIFT indexes on the Sales Line table with no change. I intend to disable SIFT indexes on the Sales Header table as well. I have read over and over again about SQL and LOCKTABLE and SIFT etc. but I have also noticed some discussion about Flow Fields slowing down access to a record set. My predecessor was a FlowField freak and every problem he encountered with data access was handled with a Flow Field. Are there any guidelines on the number of Flow Fields a table should have? What are the recommendations for table locking sequence on Sales Line insertion? If I access the Customer table during insertion to retrieve one specific value from the customer table, will I run into a performance problem if the Customer table has a ton of flow fields, but I do not issue a CALCFIELDS? Any help would be appreciated
I hate to say it, but Navision has quite a lot of problems with deadlocking especially in SQL version, but this depends quite alot from the Navision version and functionality you’re using. If you use reservations for example this increases the risk of deadlocks, because Navision has to insert another record in Res.Entry table and execute quite a lot of code. On the FlowField question - have you tried to run Items table from the object designer in ver.3.10A with Manufacturing module installed and used, running on SQL server. With 25000-30000 items it can take quite some time even to open it, and it will be a problem scrolling through it. So YES if you have a lot of FlowFields which are visualised on a form, they are recalculated every time when user opens the form or scrolls through the records in this form. Though, they are not calculated when are accessed through C/AL code until CALCFIELDS statement is invoked. Could you elaborate a bit more on the “table locking sequence whe inserting in Sales Line”? What actually Navision does is locking of the table when you execute the first INSERT/MODIFY/DELETE statement from your object and it REMAINS locked until end of objects execution OR until a COMMIT /use this with a lot of caution/ statement is fetched. good luck.
quote:
I am on SQL Server 2000 and I have disabled the SIFT indexes on the Sales Line table with no change. I intend to disable SIFT indexes on the Sales Header table as well.
If by “disabling” you mean deleting, this will cause a lot of problems in all objects using those SIFT indexes - hopefully you mean changing the MaintainSIFTIndex property to “No”. Doing this will optimize write-performance, but slow down read-readperformance so it’s a tradeoff. BTW -there are no SIFT indexes in the Sales Header Table?
quote:
Are there any guidelines on the number of Flow Fields a table should have? What are the recommendations for table locking sequence on Sales Line insertion?
Q1: No not as far as I know. But there is an upper limit to how many you can show on a form. Generally Navison avoids displaying flowfields in listforms due to performanceissues. Q2: LOCKTABLE (in most cases) should not be necessary when inserting sales lines. If you use SL.INSERT(TRUE) the OnInsert code will do the locking for you. Generally, I think flowfields are a great feature when used with care. You’re deadlocking problems (if not a result of poor coding) can be solved with (very careful) use of commits.
It’s a shame you’re in the USA - I just attended a performance troubleshooting workshop at MBS here where they demonstrated some excellent tools for analysing the Code Coverage and Client Monitor results to identify deadlocking problems, as well as some good advice on how to manage SIFT usage in SQL databases. They also invited people to bring along cases such as yours to be analysed. Is might be worth enquiring about availability of the tools over there.
Thanks one and all for your responses. To answer some questions: No I am not deleting anything related to SIFT Indexes, I am merely changing the property to “No” I have had some exposure to Performance Troubleshooting and Client Monitor and I am getting the necessary objects and granules from my NSC I think FLOWFIELDS are great too when not overused, however, they can cause performance issues. I am afraid that my problems are from poor coding. I tried to use some carefully placed commits, but do I understand correctly that commits must be paired with locktable?
I would be great if someone could expand a little on “(Very careful) use of commits” so I can explore more troubleshooting options
- There is no point in using LOCKTABLE in your situation. 2. It is not stressed enough - you should be VERY VERY VERY VERY careful! Suppose you have a posting transaction for a Sales Invoice. There are a lot of things happening then. First the doc is tranfered to Posted Documents /Sales Invoice Header/Line/Shipment if neccessary/ tables, then if there are any Items and Item Tracking involved there are entries created in the Item Ledger Entry, Value Entry, Item TRacking Entry, meanwhile there are some entries created in the Customer Ledger Entry and General Ledger Entry, some other Lines are removed from other tables. NOW suppose that somebody has put somewhere between this inserts and/or modifications a COMMIT statement and AFTER this commit the system ot the server crashes for some reason. Then you’ll have data inconsistency. The invoice will be partially posted. There will be for example entries in the Item Ledger abd Value Entry tables, but no Entries in General Ledger, etc. So this is actually the main problem - risk of data inconsistency. Thats why you should be very carefull when using COMMIT statement and be sure that the story above will never happen
I had such cases and correcting this errors is quite hard. good luck.
This is a rather complicated issue, which cannot be easily be answered. Do a search on “COMMIT” - there’s tonnes of info on the site… Basically a COMMIT commits your writetransactions to the database and unlocks the involved tables for other users (this is the main problem in your case). If not used carefully, however, this can lead to inconsistency (as examplified by Nicola). In your case - if you use COMMIT - the import codeunit should be able to recover from errors during the import (clean-up already imported sales orders or pick up from where it failed). Anyway, if you don’t feel you fully understand the implications of using COMMIT my only advice (i know it’s poor) is DON’T.
At this point Devon, with as amny problems as you have, you should be getting your NSC involved, many things can be solved in a forum like this, but not everything, at some point you are going to have to call in external help. A second set of (fresh) eyes can often see something that has been staring you in the face for a long time. Your NSC should have this skill set, if they don’t then you may need to look further.