LogoLogo
WEM ModelerWEM ReferenceTutorialsMyWEM
  • WEM Platform Documentation
  • Getting Started
    • Introducing WEM
    • Create WEM Account
    • WEM Login
    • MyWEM Portal
      • MyWEM Features
      • MyWEM Support
      • Allow Support Access
    • Partner Portal
  • WEM Modeler
    • Introducing the WEM Modeler
    • Modeler Start Page
    • Manage Projects & Workspaces
    • User Roles Scheme
    • Create a new Project
    • Modeler Application Overview
    • Project Settings
      • Project Configuration
      • Security Settings
      • Portal Settings
      • SMTP Settings
      • Mobile App Settings
    • Expression Editor
    • Flowcharts
      • User Interaction
      • End Node
      • Sub-flowchart
      • Navigation
      • Clear session
      • Decision
      • Assignment
      • List Action
      • Loop
      • Copy Data
      • Save and Discard
      • Import Data
      • Export Data
      • Invoke Webservice
      • HTTP Request
      • Execute Process
        • Different processes explained
        • Send Email
        • Change settings
        • Changing files
        • Generate File
      • Ping server
      • Authenticate
      • Comet Messages
      • OpenAI
      • Async Tasks
      • Note Node
    • Data Model
      • WEM Session Folder
    • Services and Integration
      • Webservices
      • Remote Data Sources
      • Authentication Providers
        • SAML 2.0
          • SAML App Registration in EntraID
        • OAuth 2.0
      • Comet - Real Time Messages
      • Certificates
    • Concepts and Concept Queries
      • Concepts
      • Concept Queries
      • Concept Relations
    • Navigation
      • HTTP Endpoints
      • Tasks
    • Files and Assets
      • Widget Libraries
      • Template Fragments
      • Files
      • Hyperlinks
    • Multilanguage Dictionary
    • Agentic AI
      • WEM AI Agent
      • AI Flowchart Nodes
      • AI User Interaction
    • WEM Preview
    • WEM Runtime
      • Publishing Process
      • Staging
      • Live
    • Debugging
  • Template Editor
    • Interaction Template Editor
      • Interaction Template Hotkeys
    • General and preview
    • Style
      • Heading Type
      • Paragraph Style
      • Text Styling
      • Alignment
      • Hyperlink
      • Tooltip
    • Layout
      • Adaptive columns
      • Div
      • Panel
      • Alert
      • Table
      • Image
      • Icon
      • Label
    • Interaction
      • Form
      • Validation
      • Required field
      • Button
      • Dropdown
      • Free form item
    • Miscellaneous
      • Tabbar
      • Google maps
      • Nested template
      • Script
      • Reference
      • Conditional
      • Data grid
      • Repeater
      • Rich text
      • Custom
    • WMT Tool
  • WEM Reference
    • WEM Expressions Reference
    • Functions
      • Aggregation
        • Average
        • Count
        • First
        • Last
        • Max
        • Min
        • Sum
      • Concepts
        • ConceptId
        • Description
        • FindConcepts
        • GetAncestors
        • GetChildren
        • GetDescendants
        • GetParent
        • Intersection
        • LocalName
        • Synonyms
        • ToConcept
        • Union
      • Date
        • Date
        • DateDiff
        • DateOnly
        • DatePart
        • DayOfWeek
        • FromUnixTimestamp
        • MoveDate
        • Now
        • TimeOfDay
        • Today
        • ToDuration
        • ToLocal
        • ToUnixTimestamp
        • ToUTC
        • UTCNow
        • UTCToday
      • Environment
        • ApplicationId
        • CountryCode
        • CountryName
        • Hostname
        • IpAddress
        • IsFirstRequest
        • IsHttps
        • IsPreview
        • IsStaging
        • IsLive
        • NodeTrail
        • PortalId
        • PortalName
        • ProjectId
        • ProjectName
        • ProjectVersion
        • Setting
        • UrlPath
      • Files
        • FileName
        • FileSize
        • FileUrl
        • MimeType
      • Lists
        • Distinct
        • IsNewRow
        • IsOnRow
        • List
        • NameOf
        • RowId
      • Math
        • Abs
        • Ceiling
        • Cos
        • Distance
        • Exp
        • Floor
        • Log
        • Log10
        • Pi
        • Pow
        • Random
        • Round
        • Sin
        • Sqrt
        • ToNumber
      • Security
        • AesDecrypt
        • AesEncrypt
        • AesIv
        • AesKey
        • CreateJsonWebTokenH256
        • HmacSha256
        • Md5
        • Password
        • PasswordStrength
        • Pbkdf2
        • Sha1
        • Sha256
        • Totp
      • Text
        • AsText
        • Base32ToText
        • Base64ToText
        • CompareFuzzy
        • Concat
        • Contains
        • FormatDate
        • FormatDuration
        • FormatNumber
        • Guid
        • HexToText
        • HtmlAttributeEncode
        • HtmlEncode
        • IndexOf
        • JavascriptEncode
        • JsonEncode
        • Length
        • Padl
        • Padr
        • RegExIsMatch
        • RegExMatches
        • RegExReplace
        • Replace
        • Split
        • StringPart
        • SubString
        • ToBase32
        • ToBase64
        • ToBase64Url
        • ToHex
        • ToLower
        • ToRichText
        • ToString
        • ToTitleCase
        • ToUpper
        • Trim
        • UrlDecode
        • UrlEncode
        • XmlAttributeEncode
        • XmlEncode
        • XPath
        • XPathList
        • XPathNumber
      • Validation
        • HasValue
        • IsEmpty
        • IsKnown
        • IsUnknown
        • IsValidBankaccount
        • IsValidBSN
        • IsValidBTW
        • IsValidEmailAddress
        • IsValidIBAN
        • IsValidPhonenumber
        • IsValidURL
      • Localization
        • AvailableLanguages
        • CurrentLanguage
        • CurrentTimezone
        • LanguageStrategy
        • Languages in WEM
    • Keywords
      • Contains
      • Concepts
        • Ancestor of
        • Child of
        • Concept
        • Descendant of
        • Overlaps
        • Range of
        • Parent of
        • Contains
      • Environment
        • ApplicationRoot
        • Platform
      • Lists
        • In
        • Of
        • Where
        • Contains
        • All
      • Logic
        • And
        • Not
        • Or
        • Choose
        • If Then Else
      • Mobile
        • BatteryLife
        • BuildVersion
        • ConnectivityTypes
        • DeviceId
        • DeviceName
        • IsDevelopmentBuild
        • IsDistributionBuild
      • Text
        • Contains
        • Ends with
        • NewLine
        • Starts with
    • Operators
      • Addition +
      • Division /
      • Equality =
      • Equality-strong ==
      • Greater-than >
      • Greater-than-or-equal >=
      • If-unknown ?
      • Inversion -
      • Less-than <
      • Less-than-or-equal <=
      • Logical-and &, and
      • Logical-not !, not
      • Logical-or |, or
      • Modulo %
      • Multiplication *
      • Power ^
      • Subtraction -
      • Unequality <>
    • Data Types
      • Text
      • Number
      • Yes / No (Boolean)
      • Date Time
      • Duration
      • File
      • Rich Text
      • Concept
      • Conceptset
      • Reference
    • SQL Compatibility
      • Functions
      • Keywords
      • Operators
    • Content Security Policy
    • WEMScript for Custom HTML
    • WEM Widgets
      • WEM Widget Structure
      • WEMScript for Widgets
      • Widget Editor
      • Content Placeholder
    • WMT
      • File Structure
      • Basic Functional Template
      • Styling
      • Font Icons
      • Reference
        • Breadcrumb
        • Button Bar Buttons
        • Common Scripts
        • Common Styles
        • Conditional
        • Content
        • CSP
        • CSP Nonce
        • Expression
        • Hidden Fields
        • Language Select
        • Navigation
        • Node ID
        • Node Name
        • Placeholder
        • Placeholder Conditional
        • Startup Scripts
        • Template Fragment
        • Uses
    • Regression Test Framework
      • WEM Test Script
  • Tutorials
    • Tutorials
      • Making a Change Log
      • Placeholders for end-users
    • First Application
      • Expand First Application
    • Video Tutorials
      • By Partners
      • Security
    • Building Widgets
      • Basics
        • 1. Introduction
        • 2. Hello, World!
        • 3. General
        • 4. Properties
        • 5. Events
        • 6. Styling
        • 7. View State
        • 8. Resources
        • 9. Placeholders
        • 10. Template Editor Script
        • 11. Global Scope
        • 12. Risks and Responsibilities
        • 13. Conclusion
      • In-depth
        • 1. Introduction
        • 2. Platform Availability
        • 3. Development Workflow
        • 4. Properties
        • 5. View state
          • 1. Runtime.viewState
          • 2. sessionStorage
          • 3. Summary
        • 6. Styling
        • 7. Events
          • 1. Navigation
          • 2. Row position
      • WEMscript
        • 1. Introduction
        • 2. Variables
        • 3. Properties
        • 4. OutputId()
        • 5. Register Input - Required
        • 6. Encoding
        • 7. Script Block - Introduction
        • 8. Types
          • 1. Boolean
          • 2. Concept
          • 3. ConceptSet
          • 4. Datetime
          • 5. Duration
          • 6. File
          • 7. Number
          • 8. Richtext
          • 9. Text
        • 9. Script Block - Global Scope
        • 10. Script Block - ES Modules
        • 11. CSS - attachShadow
      • Examples
  • Tips and Tricks
    • Tips and Tricks
      • Keyboard Shortcuts
      • Video's in WEM
      • Extracting text from File
    • Custom Hostnames
    • FAQ
    • Expressions
      • Order of Operators
    • Processes
      • Email Process
      • Tips for creating PDF's
      • ZIP Archive Process Node
    • Concepts
      • Concepts and Runtime
    • Working with data
      • The Power of the Calculated Field
      • Consuming your OData source with Excel 2016
      • Expose your data via OData
      • Using OData to transfer data between runtimes
      • WEM Storage Options
    • Integration
      • Integrating external systems
      • Json
      • Single Sign-on
      • Office365 Integration
    • Performance
      • Performance Tips
      • Performance Profiler
      • Template Performance Profiler
      • The Split Loop
    • Security
      • 2FA with TOTP
      • Blacklist or whitelist IP addresses
    • Widgets
      • Advanced Charts
        • Boxplot Chart
        • Brush Chart
        • Bubble Chart
        • Candlestick Chart
        • Card with Chart
        • Effect Scatter Chart
        • Heat Map
        • Line, Area and Bar Chart
        • Map-Bar Morph
        • Mix Time Line Chart
        • Nested Pie Chart
        • Polar Area Chart
        • Polar Bar Chart
        • Profit Chart
        • Radial Bar Chart
        • Radial Bar Gauge
        • Range Bar Chart
        • Rosetype Pie Chart
        • Sankey Energy Chart
        • Scatter Chart
        • Scatter-Aggregate-Bar Chart
        • Stacked Columns Chart
        • Stepline Chart
        • Treemap
      • Advanced Input
        • Color Picker
        • Conceptset Image Selector
        • Google maps locator
        • Multi select input button
        • MultiFile Upload
        • On/Off switch
        • Process Arrow Steps
        • Rate
        • Rating view
        • Single select input button
        • Slider
        • Tree
      • Template Actions
        • Follow exit
      • Global widgets
        • Agenda
        • Audio Player
        • Calendar
        • Draw Signature
        • Grid Widget
        • Image capture
        • Kanban Board
        • Path
        • Progress Bar
        • reCAPTCHA
        • Timeline
      • Document widgets
        • Json Viewer
        • PDF Viewer
    • Various
    • Go to MyWEM
Powered by GitBook
On this page
  • Introduction
  • Accessing the Performance Profiler
  • Using the Performance Profiler
  • Session Overlay
  • Analyzing Performance Data
  • Interpreting Results
  • Best Practices

Was this helpful?

Export as PDF
  1. Tips and Tricks
  2. Performance

Performance Profiler

The WEM Query Performance Profiler helps identify and analyze performance bottlenecks in data queries, providing insights to optimize application efficiency.

PreviousPerformance TipsNextTemplate Performance Profiler

Last updated 2 months ago

Was this helpful?

This functionality is only available in the DevOps environment for private clouds.

Introduction

The WEM Query Performance Profiler is a powerful tool designed to identify and analyze performance bottlenecks related to data queries. It monitors all read and write operations to SQL databases, OData sources, and transient data, providing insights into query execution times and highlighting potential performance issues.

Accessing the Performance Profiler

To access the Performance Profiler, follow these steps:

  1. Navigate to the Applications tab within the DevOps environment.

  2. Locate the application you want to profile.

  3. Click the three dots (â‹®) on the right to open the context menu.

  4. Select Performance Profiler from the context menu. This will open the profiler overlay.

Note: Ensure you are selecting the context menu for the application that corresponds to the hostname of the environment you wish to test.

Using the Performance Profiler

Session Overlay

The first overlay you'll encounter is the session overlay, where you can manage profiling sessions to monitor query performance. The Performance Profiler uses sessions to track queries, allowing you to:

  • Start a New Session: Begin a new profiling session to monitor query performance in real-time. You can choose to profile all activity or filter the session based on a specific client IP address. This filtering helps in isolating performance issues related to particular clients or users.

  • View Active Sessions: See currently active profiling sessions and their statuses.

  • Access Historical Data: Review past profiling sessions to analyze trends and identify recurring issues.

When a session is created the follow screen appears:

Analyzing Performance Data

Once a session is active, the Profiler will collect data on query execution times and the nodes on which they are executed. Use this data to:

  • Identify Slow Queries: Pinpoint queries that are taking longer than expected to execute.

  • Locate Bottlenecks: Determine which expressions or components are causing slowdowns.

  • Understand Queries: Use the information from the performance profiler to better understand how queries are optimized and how to leverage this better.

  • Optimize Queries: Use the insights gained to optimize queries and improve overall application performance.

Interpreting Results

The Profiler provides detailed reports and visualizations to help you interpret the performance data. There are a few different parts of the performance profiler to focus on when analyzing query results. The follow image will be used to go through the different parts of the profiler:

Application information

At the top you find information about the project the query profiler is running on. This holds the portal id, project id and hostname.

The query feed

On the left you have a feed of all queries executed by the application during the profiling session. Each item has the type of action, ID of the node where the query is executed and the total time the query took in seconds. The following list has some types that can be expected:

  • Get row: used when a loop was not able to prefetch the right rows because of a inefficient expression

  • Update: Used when the "save directly to database" property is used with import nodes for example.

  • Query: Default query items.

  • Bulk copy: Used by the copy data node.

  • Commit changes: When a save database changes node is passed.

  • Delete: For deleting items directly from the database.

  • Get descendant row id's: When you have a row position on a nested list and the parent is removed.

  • Get File: For retrieving specific files or when showing.

Detailed query information

The query inspector has four sections for different parts of the query process.

The general information includes the type of process, the timestamp of query execution in UTC, the total execution time, the node where the query was executed, and a trace ID.

Details section This section depends on the type of activity selected in the query feed. Generally, this section contains information on the source list, including the list ID. If a filter is used, it provides the filter ID. When paging is involved, the "limit to parent" setting indicates whether the query is restricted to a parent or applies to the full list.

Different types of activities provide different information in this section. For example, the "CommitChanges" type will show the number of inserts and updates, indicate whether an activity was successful, and display how many retries were needed, for example.

Query execution details This section details the execution of a query on the runtime server. This is the optimized query that can be directly performed on the database. The execution time displayed here is one of the key aspects to examine. Another important detail is the number of rows the query processes, both individually and in total.

The image above shows the SQL query from the start of this section. This shows the actual SQL query as is executed on the database. Every item in this query displayed as f12345 corresponds with a field from the datamodel with the number as ID. This can be used to find the field using the search of the modeler data tab. The table names are usually the same as the list names in the modeler datamodel, with a prefix "tbl".

Query postprocessing details The postprocessing section represents the remaining tasks that the database service could not handle. This part of the process is executed within a user's session and can significantly increase processing time.

In this section, the execution time, number of rows, and total row count can indicate inefficient processes that may cause slowdowns. When the number of rows grows significantly, the time this part will take can exponentially increase causing the application to slow down quickly. The remaining filter expression can also cause issues. If this query is too inefficient, it will significantly contribute to long execution times.

Best Practices

Optimize SQL Compatibility:

  • Ensure your expressions are SQL compatible. This means that parts of the expression can be transformed into native SQL statements, allowing the database server to handle them directly. This reduces the need for retrieving excessive data and executing additional expressions in memory to further narrow down the results.

Avoid Complex Expressions:

  • Simplify expressions and avoid using complex calculations within SQL queries. Perform calculations in your application logic instead of within the query to reduce the load on the database server.

Batch Updates and Inserts:

  • When performing multiple updates or inserts, batch them together instead of executing them one by one. This reduces the overhead of multiple database round-trips. Also, make sure to fine-tune the batch size—sometimes a batch size of 250 records is optimal, while in other cases, you can go up to 5,000. It depends on the situation, so ensure that your batch size is parameterized within your flows, and experiment with different settings to find the optimal value.

Monitor and Profile Queries:

  • Regularly use the Performance Profiler to monitor query execution times and identify bottlenecks. This helps in pinpointing slow queries and optimizing them.

The WEM Query Performance Profiler is an essential tool for maintaining optimal performance in your applications. By regularly using the Profiler, you can ensure that your data queries are efficient and your application runs smoothly.