Skip to content
logo Knowledgebase

Searching on sold-to column in left list of Sales Orders is very slow

Created on  | Last modified on 

Description

On LIVE folder, when using the left list in Sales Orders, if selecting on "sold-to" column, the search takes over 8 minutes to return control to the user

Navigate to Sales, Orders, Order
Select ALL transaction entry
Query in the "sold-to" column in the left-list to see the problem
After around 30 seconds, the left list is displayed, but the whirly thing keeps going for another 8 minutes

Doing a left list search using other columns such as "Order number" or "Order Date" come back instantly

Does not reproduce in SEED folder

Cause

Reviewing the SQL trace for this activity shows that the generated SQL is using the UPPER key word on the WHERE clause, which is not efficient for SQL Server

SELECT ...
FROM ...
WHERE ( SOH_.SOHNUM_0 = @P6 ) And ((UPPER( SOH_.BPCORD_0 ) >= @P7) And (UPPER( SOH_.BPCORD_0 ) < @P8))
ORDER BY ..


Resolution

Because the UPPER function in SQL commands can be very time consuming on big tables, there is a parameter to change this functionality

Navigate to Parameters, General Parameters, Parameter values Select chapter SUP, then group SEL and then set the parameter SELRAPCAS "Case sensitive in rapid search" to Yes

Logout and login again for the parameter change to take effect

Users must now enter the correct case but it will perform faster

This parameter setting is described in more detail in the online help at http://online-help.sageerpx3.com/erp/12/staticpost/case-in-quick-selection/