Reading Customer Table from Query Analyzer

ara3n

Hello I'm trying to find out what is happening. Here is my scenario.

I have two Navision clients 4.0 SP1 connected to same SQL database. I have also Query Analyzer (QA) open to the same database.

In QA I'm running the following statement

SELECT  *
FROM "CRONUS USA, Inc_$Customer" 

In Navision 1st client I have the cust. ledger entry table open.
When I copy the Cust. ledger entry table to clipboard. I switch 2nd client and can run the customer table or run a report that can loop through the customer table, and don't notice a big performance hit.

Running the Select statement above in Query analyzer takes 40 seconds. Normally running this statement takes less than 1 second.

Running a Navision report that loops through customer table and prints them takes less than 3 seconds.

I know there is no process priority.
Looking at client Monitor. Navision doesn't execute any special sql statements. Even doing a dirty read doesn't effect the statement performance.

SELECT  *
FROM "CRONUS USA, Inc_$Customer" 
WITH (READUNCOMMITTED)

It is really easy to test this and recreate it.
Any suggestion is appreciated.

Using SQL Profile, you can see that Navision uses store procedure. What I ded next is create a store procedure would use the SIFT tables for item ledger to return quantity on Hand. Executing the stored procedure gives me the same results.

kriki

Strange, I did the same in the QA and I got the result in less then a second.
Copying the records from the Navision-client to clipboard took me a few seconds.
I did use the W1 version because I don't have the USA-version installed.

Edited:I just found this post. Maybe this has something to do with your problem: http://www.mibuso.com/forum/viewtopic.php?p=41548#41548

kine

If you want to have exact results, you need to clear data and object cache on the MS SQL. Else you will have fake results. For example if you run the select on "cold" database, the data need to be read into cache and it can take some time. After that you try it from Navision and all is fast... (yes, because the data are already in cache...) :-)

ara3n

It is strange. I tried today again, and I get different results like kriki. Navision client behaves the same way as query analyzer.