ODBC Slow

McCoy

We have a client who is using ODBC interface to access the Navision 2.6db he is having a problem that I will paste below;

"We have installed the table and key changes that you sent us, but they unfortunately did nothing to improve the speed of the website. Further testing leads me to believe that the real bottleneck is the C/ODBC driver. I will try to explain what we are seeing. I have included a stripped-down asp page that executes the same query we are using on our site. It executes the query and loops through the resulting record set counting the records. It puts time stamps in various places to show where the delays are happening.

The record set can be created on the server. When this happens, the data is retrieved almost instantly. But, stepping through the record set causes each row of data to be sent to the client and this causes a long delay while counting the records.

When the record set is on the client (oRecSet.CursorLocation = 3) the data is sent to the client at the time of query execution. In this case there is a long delay executing the query, but the loop that counts the records is almost instant.

So it appears that the transfer of data through the C/ODBC connection may be the problem. Do you have any experience with this?

If anyone knows of a possible solution or any ideas - please let me know

aci

Hi,

First, Tips & Tricks forum is for already solved problems. Navision Financials forum is more appropriate for this post, perhaps the admin will move it there...

To speed up query execution time, always use ORDER BY in a SELECT statement, and the fields you list in ORDER BY must start with the same sequence of fields listed in that table's key fields. For example,

Use this for example:

SELECT * FROM "Sales Header" ORDER BY "Document Type", "No." <-- Fast

Not this:

SELECT * FROM "Sales Header" ORDER BY "No." <-- Very slow

This works too:

SELECT * FROM "Sales Header" ORDER BY "Document Type" <-- Fast

To see the list of fields and the order to use them, go to Object Designer-->Table-->"Table Name"-->Run. Now click on the sort icon in the tool bar. This would list the keys for that table. Notice that none of listed keys start with "No.", so you cannot use ORDER BY "No.". The client could create extra keys, but it could be a waste of disk space.

Here is a correct example of retrieving a Sales Order as fast as possible:

SELECT * FROM "Sales Header" WHERE "No."='SO0507001' ORDER BY "Document Type", "No."

McCoy wrote:

When the record set is on the client (oRecSet.CursorLocation = 3) ...

3 refers to adUseClient. When in adUseClient mode, all selected records are transported to the client and that takes time if there were too many records.

In adUseServer(2) mode, C/ODBC driver supports only adOpenForwardOnly(0) CursorType. If you set it something else, it will revert back to adOpenForwardOnly after you open the recordset.

Also, in adUseServer mode, RecordCount property is not supported by C/ODBC driver, it returns -1. This is normal. But in adUseClient mode, RecordCount will give you the number of selected records, that's because it transported all records to the client, and hence they were easy to count.

If you plan to use ODBC extensively, it's best to upgrade to the SQL version and bypass the C/ODBC driver all together. You will be using MS SQL ODBC Driver rather than Navision's C/ODBC Driver.

Navision's C/ODBC Driver is buggy and is not thread safe.

ADO Reference...

Administrator

[Topic moved from Navision Tips & Tricks to Navision Financials forum]

McCoy

We had created keys for the client exactly matching their sort order which did not improve anything. It is also very slow when we completely remove the ORDER BY clause from our SQL statement.


We have also tried the second suggestion with no success. When set to adUseClient the query takes a long time to transfer the data to the client, but we are able to step through it very quickly to display the data. When it is set to adUseServer the query is very quick, but it takes a long time to step through the records because the data needs to be sent to the client at that time. Each scenario takes the same length of time. The problem is simply transferred between the query and the data access.