Reading Customer Table from Query Analyzer

ara3n
Member Posts: 9,258
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
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.
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.
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.
0
Comments
-
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#41548Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!0 -
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...) :-)0
-
It is strange. I tried today again, and I get different results like kriki. Navision client behaves the same way as query analyzer.0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions