-
Title
Very slow SELECT on "QER_FTEntitlementSourceOrg" in IT Shop -
Description
On investigation of an extremely slowly opening form in IT Shop the SQL (from the generated log) displays:
"DEBUG (SqlLog):(269765 ms) - select UID_Org, UID_ParentOrg, Ident_Org, Assigntype, ObjectKeyOrg from QER_FTEntitlementSourceOrg(@BaseObjectKey, @uidorg)"
In this case a SELECT on the table "QER_FTEntitlementSourceOrg" takes about 5 minutes. On further investigation in the table DialogJournal, there are entries with the message "#LDS#Trace flag {0} should be enabled.|2453|". -
Cause
The SQL-Function "QER_FTEntitlementSourceOrg" has been improved with hotfix #23381. In this optimization, table variables are used instead of temp tables. Most of the time this improves performance.
But there is a known issue with MS SQL Server which results in bad performance when using table variables:
FIX: Poor performance when you use table variables in SQL Server 2012 or SQL Server 2014 -
Resolution
Confirm if you are affected by this issue with the following SQL-Statement:
select MessageString, MessageDate, ApplicationName from DialogJournal
where MessageString like '%2453%'
order by messagedate desc
If you see messages like "#LDS#Trace flag {0} should be enabled.|2453|" you are affected.
Enable Trace-Flag 2453 on the database server:Configure Server Startup Options (SQL Server Configuration Manager)
Restart SQL Server and verify that the above mentioned messages in DialogJournal do not appear again.