Enable Performance Logging

Author: Kepion Support Team

Introduction

In the Settings node of the Modeler, the Performance Log option enables you to record performance issues in Form queries and Rule execution. This option can be helpful when troubleshooting and/or optimizing Kepion's performance. 

Example

You can then use the following queries to examine the results:

Query performance log data:

SELECT * FROM [dbo].[Log]
SELECT * FROM [dbo].[LogExtension]

SELECT L.[UserID], L.[CreatedOn], L.[Duration], P.* FROM
(
  SELECT L.[LogID], [Key], [Value]
  FROM [dbo].[LogExtension] LE
  INNER JOIN [dbo].[Log] L
    ON L.LogID = LE.LogID AND L.Operation = N'Query-Form'
) LE
PIVOT(
  MAX([Value])
  FOR [Key] IN (
    [FormId],
    [Query],
    [CellCount]
  )
) AS P INNER JOIN [dbo].[Log] L
ON L.LogID = P.LogID

SELECT L.[UserID], L.[CreatedOn], L.[Duration], P.* FROM
(
  SELECT L.[LogID], [Key], [Value]
  FROM [dbo].[LogExtension] LE
  INNER JOIN [dbo].[Log] L
    ON L.LogID = LE.LogID AND L.Operation = N'Run-Rule'
) LE
PIVOT(
  MAX([Value])
  FOR [Key] IN (
    [SubmissionId],
    [RuleId],
    [Rule]
  )
) AS P INNER JOIN [dbo].[Log] L
ON L.LogID = P.LogID

Filter by results greater than performance threshold

DECLARE @Threshold SMALLINT = 10 --Set performance threshold in seconds
 
-- GET SLOW FORMS
SELECT DISTINCT
F.[FormName]
FROM Log L
INNER JOIN LogExtension LE ON L.[LogID] = LE.[LogID]
INNER JOIN Forms F ON LE.[Value] = F.[FormID]
INNER JOIN Users U ON L.[UserID] = U.[UserID]
WHERE L.[Duration] > @Threshold
AND L.[Operation] = N'Query-Form'
AND LE.[Key] = N'FormId'
 
-- GET LOG DETAILS ABOUT SLOW FORMS
SELECT DISTINCT
L.[LogID]
,U.[FullName] [User]
,L.[CreatedOn]
,L.[Duration] 
,F.[FormName]
FROM Log L
INNER JOIN LogExtension LE ON L.[LogID] = LE.[LogID]
INNER JOIN Forms F ON LE.[Value] = F.[FormID]
INNER JOIN Users U ON L.[UserID] = U.[UserID]
WHERE L.[Duration] > @Threshold
AND L.[Operation] = N'Query-Form'
AND LE.[Key] = N'FormId'
ORDER BY L.[Duration] DESC
 
-- GET SLOW RULES
SELECT DISTINCT
R.[RuleName]
FROM Log L
INNER JOIN LogExtension LE ON L.[LogID] = LE.[LogID]
INNER JOIN Rules R ON LE.[Value] = R.[RuleID]
INNER JOIN Users U ON L.[UserID] = U.[UserID]
WHERE L.[Duration] > @Threshold
AND L.[Operation] = N'Run-Rule'
AND LE.[Key] = N'RuleId'
 
-- GET LOG DETAILS ABOUT SLOW RULES
SELECT DISTINCT
L.[LogID]
,U.[FullName] [User]
,L.[CreatedOn]
,L.[Duration] 
,R.[RuleName]
FROM Log L
INNER JOIN LogExtension LE ON L.[LogID] = LE.[LogID]
INNER JOIN Rules R ON LE.[Value] = R.[RuleID]
INNER JOIN Users U ON L.[UserID] = U.[UserID]
WHERE L.[Duration] > @Threshold
AND L.[Operation] = N'Run-Rule'
AND LE.[Key] = N'RuleId'
ORDER BY L.[Duration] DESC

Note: It may take up to 30 seconds for the results to be logged.