Howdy, Stranger!
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
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 304 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
SQL Query differences between Job Card and Customer Card
jordi79
Member Posts: 275
Hi,
I noticed that there is a slight lag when I opened a Job Card in NAV, but there is no such lag when I open a Customer Card. Therefore I used the Profiler to debug this matter further. Upon closer examination it just appears that the Select Statement for the Job Card is taking 2 times longer than Customer Card. But not only that, I realised that the NAV structured the SQL select statement differently between JobCard and CustomerCard. For JobCard the Select statement starts like:
While for CustomerCard the Select statement starts like:
I also tried with ItemCard and the select statement for ItemCard is similar to CustomerCard. I checked the properties in the CustomerCard and JobCard page for any differences in properties, but they are the same.
I suspect that the difference in performance in opening these 2 cards arise from the SQL statement. But what could have caused the different SQL select statements?
I noticed that there is a slight lag when I opened a Job Card in NAV, but there is no such lag when I open a Customer Card. Therefore I used the Profiler to debug this matter further. Upon closer examination it just appears that the Select Statement for the Job Card is taking 2 times longer than Customer Card. But not only that, I realised that the NAV structured the SQL select statement differently between JobCard and CustomerCard. For JobCard the Select statement starts like:
SELECT "timestamp","Entry No_","Job No_","Posting Date","Document No_","Type","No_","Description","Quantity"....
While for CustomerCard the Select statement starts like:
SELECT TOP (50) ISNULL("Customer"."timestamp",@0) AS "timestamp",ISNULL("Customer"."No_",@1) AS "No_",ISNULL("Customer"."Name",@2)...
I also tried with ItemCard and the select statement for ItemCard is similar to CustomerCard. I checked the properties in the CustomerCard and JobCard page for any differences in properties, but they are the same.
I suspect that the difference in performance in opening these 2 cards arise from the SQL statement. But what could have caused the different SQL select statements?
0
Comments
I could imagine that it's because on the Customer Card the OnFindRecord trigger contains some Code. The FIND statement should return a recordset of 50 records (based on your database caching setup, default is 50) which would explain the TOP (50) statement. But that doesn't explain why the statement is the same in the Item Card.
I could not figure out why the SQL select statement is different. But I found why the Job Card opened slower than the Customer Card. It is due to some customisation that was done. So it was not related to the SQL statement. But however, the SELECT statement is different between Job Card and Customer card. But since my problem is solved, I am moving on...