Im getting stuck with my implementation of C/ODBC. Can anyone help me? I am using Navision version 2.01B, Australian version. Whenever I use pass through queries on a large table (about 1,000,000) records it takes a long time, depending on the complexity of the queries (25 minutes to return 37,917 records). Looking at the switch it seems that there is a lot of traffic being generated and Im wondering if the Navision engine is parsing my query at all? From the amount of traffic, I would guess that the Jet engine is doing the work. I have C/ODBC installed as a ‘Machine Data Source’. What could I be doing wrong or am I just expecting too much? Thanks Ben[:(!]
From my experience that is about average or maybe a little quicker than we see. Some observations from our experience - - 2.60E is faster then 2.01 - C/ODBC uses the local client so it is ‘fat client’ and reads the tables back to the workstation just like the Navision client - is you have select criteria (eg. posting date is between x & y) then performance degrades very rapidly as you move the select down the relationship. What I mean by this is if you select on date in say an Invoice header you get one level of performance, if you then join customer to invoice and select on posting date in the invoice you get a much slower result. The more joined tables the slower it gets. I have never been able to get selections based on flow filter or flow field columns to work via C/ODBC. I also seem to remember a problem with 2.01 (over 12 months ago now) where if you had an ‘Order By’ clause on the query Navision either took forever or returned complete garbage. I adopted the approach of doing sorting in Excel or Access not in the C/ODBC query.
Oh, thats a shame. OK thanks very much for your help. I am writing a marketing app that reads from Navision and then generates a mailing lit based on selection criteria. You can then track a campaigns statistics at any time. Clearly its going to be a long process!
quote:
Originally posted by PeterCox
From my experience that is about average or maybe a little quicker than we see. Some observations from our experience - - 2.60E is faster then 2.01 - C/ODBC uses the local client so it is ‘fat client’ and reads the tables back to the workstation just like the Navision client - is you have select criteria (eg. posting date is between x & y) then performance degrades very rapidly as you move the select down the relationship. What I mean by this is if you select on date in say an Invoice header you get one level of performance, if you then join customer to invoice and select on posting date in the invoice you get a much slower result. The more joined tables the slower it gets. I have never been able to get selections based on flow filter or flow field columns to work via C/ODBC. I also seem to remember a problem with 2.01 (over 12 months ago now) where if you had an ‘Order By’ clause on the query Navision either took forever or returned complete garbage. I adopted the approach of doing sorting in Excel or Access not in the C/ODBC query.