I agree, SQL is not good. [:(] I have a customer running Attain 301B on SQL via citrix, and performance is not stable, one day it flies through, the other day oh so slow. I do not have the knowledge of SQL database setups, but our company has a delicate team (not Navision) to do that task (and they believe their setups are perfect). We have tried to optimise the Navision codes as much as possible to suit their business requests, i.e. proper key for proper filters (as all the claims to Navision application [}:)]) , but still do not know why it is slow for a particular record, but not for others, and the other day the record caused slowness before then is ok, so mystery. And because they run via citrix then it adds to other problem. Sometimes it is frozen, just when users click to lookup on the contact table. Our customer is just using Attain for 6 months, and their database now grows up to 20G, yes they have a big contact table (and customer)(nearly 1 million customers), and this table is the main one, it keeps modified/updated all the time, and all the search functionalities are done via contact table (you may know the duplication search functionality in Navision, we made a big change in this function otherwise never get it to work with such a big contact table) The speed issue is not a problem with C/SIDE
It is very easy to identify the SQL statements that are running with large duration, using the Profiler tool. Your SQL team should know how to do this and so isolate the table, key and filter values that cause the problem. It is nearly always a problem with not filtering with the first field(s) of an index, since SQL Server does not perform well when these fields are absent from the WHERE clause. To determine the C/AL code executing the code, use the Client Monitor - either alone or as part of the troubleshooting guide’s supplementary objects. You should then be able to determine the cause of the bottleneck. Of course you need to do this when the problem arises, not when everything runs smoothly. So it make take several tries to provoke the problem, but that is the essense of troubleshooting I’m afraid. Navision Server is much more forgiving with bad keys or bad filters than SQL Server is, since it has been designed to use the C/AL database functions nativley. SQL Server has not be designed for record-at-a-time data retrieval or modification, so as long as you have a C/AL language the way it is, you’ll have some mapping to SQL Server that is non-optimal. OK, but don’t give up when it doesn’t run as smoothly as Navision Server did. Invest some time in determining the root cause of the problem - these can be solved in most cases.
We went to SQL Server Option over 6 months ago and are very thankful we did. In our shop SQL is much faster, administration is much easier, you can backup while users are in the database and take advantage of log backups, log shipping (for warm standby), and ADO connections to the tables. The duplication of every table per company is kind of a pain, but the advantages far outweigh the disadvantages. We are going through a major expansion adding thousands of customers and continually modifying the code. Our database size has tripled, and I am sure that if it was still on a C/Side database, it would be a major headache. We also had an integration project that we were trying to leverage with C/FRONT and C/ODBC driver [xx(]. IT NEVER WORKED. As soon as we went to SQL, and had the ability to connect with Access data projects, ASP, ADO, ODBC, etc. the project sailed right through. If you are in a shop where you need creative, flexible ways to develop with Navision I can’t imagine doing ANYTHING but the SQL option. Just a few caveats: If you want to connect with ODBC, you will have to write views to cleanse Navision field and table names. ODBC chokes on spaces, periods, etc. Don’t ever put any data into the SQL tables directly!!! Another advantage to SQL is the ability to add a table in the Attain database through SQL. Navision will just ignore the table. Then if you want some simple interaction with that table outside Navision, you can right some triggers on Navision tables, such as if you wanted to create a change log, etc. Other than HW requirements, I can’t think of any serious disadvantages
With respect to Loan Phan’s post about performance and SQL code optimization. Another caveat is the LOCKTABLE statement. Basically, all native Navision code that contains a LOCKTABLE statement is preceded by IF NOT RECORDLEVELLOCKING. Translation: “IF not on SQL Server”. One of the other things we had to go through was our NSC had to review our custom code and add IF NOT RECORDLEVELLOCKING to any LOCKTABLE statements. I am not completely sure about what performance problems come with LOCKTABLE on SQL Server Option, but I am wondering if Loan’s people have reviewed that as a performance problem. [?]
David, out of interest what version(s) are you running against SQL Server? I can update you a little on LOCKTABLE against Navision Server vs. SQL Server. For Navision Server, a LOCKTABLE (default parameters) will try to lock the entire table immediately at the execution of this function, as you know. Also, any modification will do this implicitly. For SQL Server, a LOCKTABLE will not send any command to the server at all, at its execution. Instead it flags two things: 1. That the SQL TRANSACTION ISOLATION LEVEL should be set correctly as soon as the first following database operation is executed, until the end of the transaction. Correctly means a SERIALIZABLE isolation level. 2. That this table should have the appropriate row-level locks placed on all subsequent database operations performed on it, until the end of the transaction. So it is the database operations that follow the LOCKTABLE that will actually apply any kind of lock to the table. This will never be a table lock - always a record (or key-range) lock; either a shared, update or exclusive lock. You should use LOCKTABLE on SQL for the same reasons as on Navision Server, for example to ensure that records you read can be later modified. If you wrap LOCKTABLE with IF NOT RECORDLEVELLOCKING, then on SQL where this will yield FALSE, you will not be locking the table, i.e. record locks will not be placed when they are read. But since you would have been locking on Navision Server, your logic probably requires that the table is locked to protect modifications. Not doing this on SQL will be a problem, in a multi-user situation. A situation where RECORDLEVELLOCKING is useful, is where you would like to write LOCKTABLE(TRUE, TRUE). On Navision Server, this will protect later modifications without locking the table before reads have occured. On SQL Server this is not supported - you need a regular LOCKTABLE, so you need to differentiate for the code to run on both platforms. What I am saying is I hope your modifications have not just removed LOCKTABLE to aviod a suspected problem. You cannot in gerneral do that and expect data consistency in a multi-user environment.
quote:
Originally posted by robertc
Thats because a 3.6 application is x times more complex that a 2.6. If you look at the server calls required with 3.6 application areas compared to 2.6 you’ll see the reason for it being slower. If you compare like with like, i.e. a 3.6 c/side running your 2.6 application it is much faster against SQL Server than the 2.6 c/side because there has been around 18 months of c/side optimization in that period. However, the real performance benefits come in the application design, and there has been little (almost no) emphasis on improving the performance again SQL Server in that respect.
That sounds disappointing. I was thinking of upgrading. We have a 150 gig SQL 2000 database running on 3.10 executables, 2.6 objects with Adv. Dist. We don’t use the Advanced Distribution portion though. but it added too many keys. We have 40 million Item ledgers. Half the database size is the keys for the Item Ledger. Through a lot of tuning with the keys, we have the performance acceptable, but could be better, especially the Month End Adjust Cost routine.
I have to agree that eventually Attain will become a totally SQL dependent product. It would seem that Microsoft would demand it. We are using SQL in our office and 2.6e, and the performance is OK generally. Our sales header and line tables seem to be the worst performers, but everything else is good. In terms of Loan Phen’s problem, it would seem to me that with that many customers, that the processing of the flow fields is what is causing the slowness, since the Customer table has a lot of them. And let us all remember what SQL Server does well, and that is handle SQL statements. Since Financials/Attain has to translate C/SIDE to SQL statements, it may not always be the best SQL statement for that job, than what would be able to done if you were a SQL black belt.
I don’t think the flowfields cause the problem of slowness, as users only access contact table (not customer) and there are certainly a very few flowfields on the table. And as from my understanding (correct me if i am wrong), if no flowfields are displayed on the form then it does not affect the performance on opening the form. About the filters and keys, yes we have set the right keys for the right filters (understand SQL well [:p] of the keys problem), the searching is really performance acceptable, just the strange behaviour here is when the search found the record (from the list) then goes to the card, then sometimes the card becomes frozen, you cannot move to the other fields (no flowfields on the card). I don’t know if it relates to the recent report on Navision service system on frozen of the item card or not. They have a really big sales orders everyday (about 400-500 orders), and posting and printing is acceptable (fast as my customer said). Just a side question, when run just a simple report, CPU consumes upto 20%. How report in Navision works in terms of memory, and what is the big affect, local’s memory or server’s memory?
I am end user,database is sqlserver2000,more than 200 user,two companys.I feel the navision speed is fast generally work with sqlserver2000,but when i remove planning worksheet linesit is very slow,maybe related data are very big.
You should check the code that is being executed when you open the forms that are the slowest. You might find your answer. I am really unsure as to what Navision does when it encounters FlowFields on the table but not on the card, whether it is still doing calculations. You may find that when the form is opening that it is doing a lot of CALCFIELDS. We sometimes get locked forms for users that are working on our sales orders at peak times. It seems to be when the table gets locked for longer than expected, other transactions get locked out, and the form just hangs, even after the processor hungry transaction has stopped.
You don’t need to be guessing what is causing the freeze when you have at least two tools available to you for the SQL version that will tell you right away. Please see my earlier reply and you’ll know exactly what is causing the problem. Then you have the chance to fix it.
What type of hardware and configuration are you using? We are contemplating moving to SQL Server, running two companies. I’d like to size for a 120GB database, with 200 users. We have about 1200 orders per day. Thanks!
quote:
Originally posted by peterxiong
I am end user,database is sqlserver2000,more than 200 user,two companys.I feel the navision speed is fast generally work with sqlserver2000,but when i remove planning worksheet linesit is very slow,maybe related data are very big.
What type of hardware and configuration are you using? We are contemplating moving to SQL Server, running two companies. I’d like to size for a 150GB database, with 220 users. We take about 1200 orders per day. Thanks!
quote:
Originally posted by ddiehr
quote:
Originally posted by robertc
Thats because a 3.6 application is x times more complex that a 2.6. If you look at the server calls required with 3.6 application areas compared to 2.6 you’ll see the reason for it being slower. If you compare like with like, i.e. a 3.6 c/side running your 2.6 application it is much faster against SQL Server than the 2.6 c/side because there has been around 18 months of c/side optimization in that period. However, the real performance benefits come in the application design, and there has been little (almost no) emphasis on improving the performance again SQL Server in that respect.
That sounds disappointing. I was thinking of upgrading. We have a 150 gig SQL 2000 database running on 3.10 executables, 2.6 objects with Adv. Dist. We don’t use the Advanced Distribution portion though. but it added too many keys. We have 40 million Item ledgers. Half the database size is the keys for the Item Ledger. Through a lot of tuning with the keys, we have the performance acceptable, but could be better, especially the Month End Adjust Cost routine.
Hi to all, we are working on a Native DB with 14 GB and 80 users. We never have or had any time-problems. But it is remarkable that sometimes users are waiting for anotherone, it depends on “Table-Locking” in the Native-Database. The “Table-Locking” isn’t good, so for that reason “Record-Locking” in SQL-Database is more better. Hopefully it will also be realised in “Native DB”!? May be anyone of ??? Navision??? can comment it?! regards Rolf
quote:
Originally posted by mrbert We are contemplating moving to SQL Server, running two companies. I’d like to size for a 120GB database, with 200 users. We have about 1200 orders per day.
Albert: am i right that you currently using the Navision-DB? As our installation seems to be similar, i would like to know if you currently have a more or less problem with table-locks[?]
well i have a 8GB installation with 20 users on MS-SQL. customisation is quite heavy, and whenever we tried to open a form with a lot of flow fields, it tooks us 5 seconds. but when we port the database to native c/side, the whole system froze when we tried to open the same form. the system started “counting records”, and you can see some numbers enumerate in the status bar. in this instance, it is clear that SQL is better.
Hi,mrbert? the Sqlserver2000 server is Dell 4400,memory size is 2gb. normally,navision speed is ok,but only to run MRP and to remove MRP Lines(planning worksheet lines) is slow,it will take near 24 hours to calcllate regenerative plan.we have 500 sales,purchase,production orders per day. the database size is 28gb. can you tell me how long to run mrp?
Peter, What version of Attain are you running? There have been both C/SIDE (general) and Application (specific) optmizations for Calculate Regenerative Plan, made for the 3.10a release.
Robert, Attain version is 3.01b. thanks
Jordan. I think You should take a look at Your keys in the underlaying transaction tables… To You others. When running SQL. Allways use the latest client. In 3.60 there is a lot of performance enhancements compared to 3.01. //Lars