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
- 310 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
Web Client Filter SQL query with COLLATION and LIKE
skiddoo
Member Posts: 19
Hello,
filtering in the Web Client is causing a SQL query where the WHERE clause using a COLLATE LATIN1_100_CI_AI and a LIKE. This is a performance killer on tables with a lot of records because the SQL server is using then the clustered index instead of the proper index.
I can avoid that by using single quotes around the filter string.
I know NAV is doing it because of usability reasons. A user which is filtering for CustomerA wants to find CustomerA as well as customera. And it's fine for a small database like Cronus. But for big databases like ours it's a nightmare.
Is there a way to change that behavior. I would like to have a "as is" filter by default. In case the user want's something different he needs to adjust his filter with an @ or *. Like in the good old days
Regards
skiddoo
filtering in the Web Client is causing a SQL query where the WHERE clause using a COLLATE LATIN1_100_CI_AI and a LIKE. This is a performance killer on tables with a lot of records because the SQL server is using then the clustered index instead of the proper index.
I can avoid that by using single quotes around the filter string.
I know NAV is doing it because of usability reasons. A user which is filtering for CustomerA wants to find CustomerA as well as customera. And it's fine for a small database like Cronus. But for big databases like ours it's a nightmare.
Is there a way to change that behavior. I would like to have a "as is" filter by default. In case the user want's something different he needs to adjust his filter with an @ or *. Like in the good old days
Regards
skiddoo
0
Comments
In BC14 WebClient you could use the filterpane which has the same behaviour as the filterpane within RTC
If you use the searchbox then it searches all fields with like (very slow)