We (end users) had a third-party programmer create an OCX for our Navision Financials (US 2.00) system. It works, but is unacceptably slow. The OCX (written in MS Visual C++) is called during billing for each order line item. The OCX looks up applicable US state - county - and city sales taxes via SQL calls to a MS SQL 7.00 server. Each lookup, from the user’s point of view, requires 20 to 25 seconds. (Without this OCX, each billing calculation within Navision is, as one would expect, almost instantaneous.) Testing of the SQL lookup routines outside Navision results in very fast look ups. I have no experience with Navision. I wonder if Navision – especially at the client machine – ever does any swapping of data or code to disk before giving control to an OCX? If so, is there any way of preventing this swapping? All network benchmark results are very good, so I don’t think that the problem is caused by network congestion. Here’s our environment: Navision server: Win NT 4.0, SP4. 2 x Pentium II, 450 MHz. 384 MB RAM, duplexed 18 GB 10,000 RPM ultrawide2 SCSCI drives. Navision database size: 1.1 GB TCP/IP protocol Client stations: Win 98. 128 MB RAM. 6 GB EIDE drive. Protocols: IPX and IP Clients: MS client for Netware (a Netware server provides user authentication and print services) and MS Client for Windows MS SQL server 7.0: Win NT 4.0, SP4. 2 x Pentium III, 550 MHz. 384 MB RAM 2 x 18 GB 10,000 RMP ultrawide II disks, Adaptec 3 channel hardware RAID 1 controller TCP/IP protocol Network protocols: Ethernet II frame type (only) IPX/SPX and TCP/IP Each station is on a 100 baseT switched port (Cabletron switch). All station and server network interface cards operate in 100 Mbps half-duplex mode. (My tests of full-duplex mode with Windows have been disappointing, so we force all network components to half-duplex.) I’m stumped. Can anyone with real world experience in making Navision work with OCXs suggest why our function might run so slowly? Thanks, Russ Bellew Radiodetection Corp (end users) Mahwah, NJ USA russ@rdcorp.com
Hi This is just some thoughts on using the SQL language itself that might speed up the process. I have been programming SQL for about 5 years(but not using the MS SQL server, just the SAS environment), and my experience is that creating the correct indexes and keys on the tables increase the speed with approx. 8 - 10 times. So before diving into adjusting your total hardware settings, it would be worth taking a look at your SQL queries used by the OCX. Do you have access to the SQL command code part of your OCX? This is just a suggestion from a fellow developer 
Regards Andreas Lundin
Thanks, Andreas, for your reply. The OCX was written by a third-party developer. We will meet with him tomorrow, together with the Navision Solution Center people. We’re all baffled. None of us has experience with interfacing Navision with OCXs. My guess is that we need to speak with someone who has extensive experience with this. The SQL database and SQL calls are provided by the leading US-based sales/use tax company: Vertex (www.vertexinc.com). The OCX developer built his OCX around these SQL calls. I’ll pass along your suggestion to the OCX developer. I welcome (am desperate for!) any further ideas. Regards, Russ
You need to isolate the source of the problem. You said that you tested the SQL routines, but did you test the OCX outside of Navision… for example, with VBA inside Excel? Perhaps the OCX is very slow. If you experience OCX slow inside Navision, and the same OCX fast outside Navision then perhaps Navision expertise can help. But first, make sure that the OCX is fast called from another language. Good Luck!
Thanks, Jim for your guidance. The OCX programmer has written a simple Visual BASIC program to test his OCX; it works quickly on the same client machines, on the same LAN with the same servers. I keep thinking that we’re running into some odd behavior by Navision that’s exhibited only when it calls OCXs. Neither myself nor the OCX programmer – who seems to be a capable C++ SQL programmer with good SQL experience – has ever worked with Navision before, and the programmer from our Navision Solution Center has limited Navision experience. (I don’t think that he’s ever used Navision with OCXs.) I’m all ears if you have any (even wild) ideas. Thanks again, Russ
It is unfortunate that you are not running Navision on SQL server as you could create a view into the other billing table and pull the data straight into Navision from within Navision using no OCX. I assume that you are using this external SQL table because it belongs to some other system that updates this data. How often would this data be updated? If it is not very often, then perhaps this OCX you have had written could pull the data into a new Navision table and you can use the data from there. If you used timers (in 2.01x) you could have a client that automatically updates every fews seconds,minutes,hours etc. This of course would mean you would have to move to 2.01B and have a client running for this process to automate. These of course are methods to avoid using the OCX for each line when posting. I’ll be interested to see the results of the testing of the OCX. If there are keys etc involved then make sure they are being set/searched on in the correct order as this can change a result to 10x+ what it should be. Craig Bradney Technical Manager Criterion Technology Pty Ltd P:+61-2-9436-0436 F:+61-2-9436-1004 E:craigb@c-t.com.au
Thanks, Craig, for your ideas. The SQL database (and the SQL calls themselves) are provided by Vertex. The SQL-based tax tables are updated by them each month. The OCX programmer tells me (and I believe him) that Vertex has done a good job of providing optimal indexes and searches. Your observation concerning NF and SQL is astute: from the start, I’ve had mis-givings about the wisdom of going with a proprietary database. I think it’s inevitable that we’ll need to move to a SQL backend sooner or later. In the meantime, our ability to bill has slowed to a crawl, so we’ve gotta get this OCX working. Your last sentence refers to “keys”: are you referring to keys within Navision or keys within the SQL database? Please explain to this dummy. Thanks again, Russ
Hi again Just a small comment on using OCX: I have developed several OCX utilities in both Visual Basic and Visual C++, and when it comes to speed, the VC++ OCX is more than three times faster than the VB OCX. It if course matters if your OCX only handles simple data processing, or if it has to calculate and search a huge amount of data. The difference in speed is not that much, when talking about just handling simple tasks as exporting data. But when it comes to processing, searching and computing on big scale tables, the VC++ OCX is much faster than the VB OCX. I have several similar OCX utilities done both ways, and every time the VC++ OCX is considerable faster than the VB one. This is of course my own experience. But it would be nice to hear some other opinions on the subject. Regards Andreas L.
Thanks, Andreas, for your input. (I’m really grateful to receive ideas from experienced people such as you and Jim.) This morning I’m in the throes of creating multiple Win98 configurations on both a test client and a production client PC. (Thank heavens for Powerquest’s Partition Magic!) Our NSC so far refuses to send in anyone with heavier duty Navision/OCX experience and instead asks us to strip at least one client to bare-bones TCP/IP (only), MS CLient for Windows (only), with no virus scanner or any other embellishments. I envision us having at least several Win98 OS’s, each with progressively more embellishments, while benchmarking. Does this seem like a logical troubleshooting method? Thanx again, Russ
Russ, If the SQL tables are only updated each month then I suggest that you make a new table in Navision to mirror the SQL one, then when the tables in SQL are updated, you run the OCX to update the Navision table and then you will be back to Navision speed when the posting takes the data from the table that is in Navision. Craig Bradney Technical Manager Criterion Technology Pty Ltd P:+61-2-9436-0436 F:+61-2-9436-1004 E:craigb@c-t.com.au
Thanks, Craig – that seems like a good idea, but the OCX programmer tells us that Vertex, who furnish the SQL tables, also furnish the C language code that accesses them. He feels that accurately translating Vertex’s C code to Navision C/AL would be a problem. I suppose that one advantage of using the OCX (with Vertex’s routines) that he’s created is that if there’s a tax calculation error, it’s prolly not our fault. Somehow, I have a feeling that thsi will be an on-going issue. Thanx again for the thoughtful ideas, Russ
Russ, I dont know about translating the code from C to C/AL but I do know that Navision offers enough of the language constructs to be able to do any sort of maths required. There is no difference using fields in a table rather than variables in C. Surely the calculations are not that difficult. If you want - email me some code and I’ll have a look for you… If I had to choose another language to write in it’d be C… but I program Navision 8-10 hours a day so I don’t get the chance as much anymore. Craig Craig Bradney Technical Manager Criterion Technology Pty Ltd P:+61-2-9436-0436 F:+61-2-9436-1004 E:craigb@c-t.com.au
Thanks, Craig. I’ll ask to have a look at the Vertex provided C code. (I started with BDS C on a Z-80 c1980, but I prefer Pascal, as I’m only a part-time programmer and I learned to like Pascal’s strong typing and self-documenting if verbose characteristics. The only times that I long for C is when handling zero terminated strings whose length exceeds 255 characters.) The issue of “sales and use tax” in the U.S. is a major one: each of our 50 states as well as each of the thousands of counties and municipalities may impose sales tax on SOME product categories that are purchased by their residents. The applicable tax for any item is a function of product category and user location (which inconventiently enough doesn’t conform to our postal ZIP codes). Vertex and a handful of competitors undertake to routinely collect, compile, and distribute a single rational table; Vertex also provides software that allows us to print sales tax returns using digital forms that are approved by the states. (Yes, each state has its own form.) Each state and other teaxing authority has different filing requirements: monthly, quarterly, etc – as well as penalties for late filing and under-collection. It’s a byzantine nightmare, more worthy of medieval fiefdoms than 21st century USA. It’s been a political football for many years; our auditors strongly recommend that we collect sales tax, and our board insists on it. Using Vertex software with our old Macola Accounting system saved us about one full-time clerk per year. BTW, I notice that our Vertex SQL database (Vertex_data.MDF) is 204,800 KB in size. Our Navision database.fdb is 1,130,000 KB. Thanks again for your ideas, Russ
Russ, even if the SQL table is 204mb, it is probably storing fields that wont be necessary (or hold data) in Navision. I really doubt that the structure of the tax system would require 204mb of calculations and data to go with it. Throw it into Navision… :). It’ll work well then. Craig Bradney Technical Manager Criterion Technology Pty Ltd P:+61-2-9436-0436 F:+61-2-9436-1004 E:craigb@c-t.com.au
Craig, I agree the data and functionality belong INSIDE Navision, but it is very possible the data is 200MB. The jurisdiction/product type/rate/rounding rules matrix for the USA is unbelievably complex. Every single city in the USA can have different rules which are in addition to transit districts, counties, and states. Some items are taxed based on the price. Others are based on the quantity of the item sold. And then there are the exceptions to the tax. Every special interest group gets a chance to exempt their product from the tax. And then there are those areas that are within one jurisdiction but closer to another with a lower tax rate… and they need lower taxes (another exception) so they can compete with their neighbors. Jim Hollcraft aka Skater drilldot.com Unauthorized Navision News Link Edited by - Jim Hollcraft on 5/31/00 6:19:16 AM
Jim, Ouch! what a messed up tax system :). In Australia we are just about to switch to a consumption based tax system (GST) like Canada and Europe etc. I don’t know if it will be any better than our wholesale sales tax now. Hence we are having ‘fun’ applying NSAU’s GST servicepack - which isnt too bad at all. At least with licencing now you dont pay for database size so adding a 200Mb table wouldnt be a massive task for the server (as opposed to looking at a transactional continuously changing table.) Craig Bradney Technical Manager Criterion Technology Pty Ltd P:+61-2-9436-0436 F:+61-2-9436-1004 E:craigb@c-t.com.au
Hi Craig, Jim is correct: it IS a mess. We first became entangled in this web perhaps ten years ago, when the state of Texas had an auditor camp in our New Jersey office for a week or so. They charged us undercollection penalties for many sales to Texas entities – notably MCI (HQ: Richardson, Texas). I’ve heard that most large US manufacturers dedicate a person/room and/or department just to handling state tax issues and audits. I’ve often wondered how so many small- to mid-size US companies skate by without collecting sales tax for sales to residents of “foreign” US states. I can only guess that they have no outside sales or support staff or just choose to fly below the radar and hope that no state decides to audit them. The US federal courts (notably South Dakota vs Quill??) have said that if a seller has a “nexxus” (a legal term that I can’t explain) in a “foreign” US state, the seller must collect sales tax for the “foreign” state. The question seems to hinge on just what constitutes a “nexxus”. BTW, the third-party OCX developer and our Navision NSC programmer both tell me that while importing the Vertex tax table into the Navision database is possible, porting the Vertex algorithms to C/AL would be a monumental task. Apparently Vertex (www.vertexinc.com) provides these in DLL format, and an API in the form of standard C header (".h") file(s). In any case, after each month of operation, we would need to export the (now updated) table from Navision back to SQL, so that we could use Vertex’s (easy to use) programs to file reports and tax returns. Thanks very much for your continued valuable ideas, Russ
Russ, Has your programmer compared the access times of the OCX versus using ODBC to access the SQL data directly from within Navision using a data source?