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
- 300 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 35 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
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Quick Filter and SQL Collate in NAV 2016
kball
Member Posts: 3
Hello,
first of all: I'm not sure which category is the correct one as it both fits in NAV Three Tier and SQL. I have also asked this question already at NAVUG but it seems that nobody has experienced it this far...
We are currently migrating from NAV 2009 R2 to NAV 2016. We have a fairly large database (> 1 million contacts, > 300k customers, > 4 million entries in the sales header archive, ...) and we have experienced a strange behavior concerning the quick filter on overview pages with large datasets.
Let's take for example the Contact List. In the quick filter we are using the No. field and search for one contact number, for example 00000001.
Loading time until the record comes up in the list is about 3 seconds. It takes another 3-4 seconds until the contact card is loaded completely.
When doing the same search with the normal filter the loading time is 1 second max. Both on the list and on the card.
Now comes the really strange part:
When using the quick filter and searching not for the contact number 00000001 but for example for two contacts (00000001..00000002) the loading time is 1 second max. for both the list and the card.
We are able to reproduce this over different pages with different fields. The only thing they've got in common is that there are many records on the table.
By looking into the SQL profiler we were able to see that NAV is sending a extra COLLATE command to the SQL server when using the quick filter with only record. This doesn't happen with the normal filter or when using the quick filter with two records.
I know this sounds a bit petty, but for our upgrading project we are really working on improving performance for the users... and 3-4 seconds when having a customer on the phone can be a really long time. And it doesn't happen on 2009 R2.
Some more information about the setup:
2016 is on Build 46773.
SQL Server 2014, Hardware is sufficient for the database (2 * Intel Xeon CPU E5620 @ 2.40GHz (16 cores), 120 GB RAM).
Clients are running on Win 7 Pro and Win 8 Pro. Middle Tier is running on 2012 R2.
SQL Keys have already been optimized by my NAV partner.
We have already tried to delete all FlowFields from the List page (deleted in the page object and not only just disabled). Same behavior.
Did any of you guys did experience something like this?
Thank you.
first of all: I'm not sure which category is the correct one as it both fits in NAV Three Tier and SQL. I have also asked this question already at NAVUG but it seems that nobody has experienced it this far...
We are currently migrating from NAV 2009 R2 to NAV 2016. We have a fairly large database (> 1 million contacts, > 300k customers, > 4 million entries in the sales header archive, ...) and we have experienced a strange behavior concerning the quick filter on overview pages with large datasets.
Let's take for example the Contact List. In the quick filter we are using the No. field and search for one contact number, for example 00000001.
Loading time until the record comes up in the list is about 3 seconds. It takes another 3-4 seconds until the contact card is loaded completely.
When doing the same search with the normal filter the loading time is 1 second max. Both on the list and on the card.
Now comes the really strange part:
When using the quick filter and searching not for the contact number 00000001 but for example for two contacts (00000001..00000002) the loading time is 1 second max. for both the list and the card.
We are able to reproduce this over different pages with different fields. The only thing they've got in common is that there are many records on the table.
By looking into the SQL profiler we were able to see that NAV is sending a extra COLLATE command to the SQL server when using the quick filter with only record. This doesn't happen with the normal filter or when using the quick filter with two records.
I know this sounds a bit petty, but for our upgrading project we are really working on improving performance for the users... and 3-4 seconds when having a customer on the phone can be a really long time. And it doesn't happen on 2009 R2.
Some more information about the setup:
2016 is on Build 46773.
SQL Server 2014, Hardware is sufficient for the database (2 * Intel Xeon CPU E5620 @ 2.40GHz (16 cores), 120 GB RAM).
Clients are running on Win 7 Pro and Win 8 Pro. Middle Tier is running on 2012 R2.
SQL Keys have already been optimized by my NAV partner.
We have already tried to delete all FlowFields from the List page (deleted in the page object and not only just disabled). Same behavior.
Did any of you guys did experience something like this?
Thank you.
0
Answers
https://msdn.microsoft.com/en-us/library/hh879085(v=nav.90).aspx
Basically, when you enter 0000001 in the quick filter box it's effectively filtering on @*0000001*
Thank you for your answer, but unfortunately in my opinion this is not the cause of the problem.
The example i wrote about was for a code field. But it also happens when searching in a text field (for example in the Contact list the field Name).
In the Quick filter i'm using the search string "example" (without the ") -> NAV automatically filters on @*example*.
In the advanced filter i'm using the search string "@*example*" (without the ").
And it is the same behavior. Quick filter = Slow, Advanced filter = Fast.
And if this is the cause of the issue, the quick filter shouldn't be called quick filter in the first place...
What i don't understand though is the extra COLLATE which is done by the SQL server. But maybe there is an SQL specialist who can shed some light...
Thank you.
Please look at sql profiler and check if there are any change in CPU time, read when filtering with Quick and Advanced and try to test execute the same query using report or codeunit. Try also to check the execution plan to find the differences.
The COLLATE is used to convert database collations. This can be a issue if the database is CASE SENSITIVE and the @ must search case insensitive.
Please also note that the second search on same range of records could benefit the sql server cache.
Regards and good luck,
parm