Skip to main content

Query Performance Testing

Users can test the performance of SQL queries against a baseline ‘expected runtime’. The actual query runtime can be compared with the expected runtime and categorize the scenario outcome into – Pass, Information, Warning, or Fail.

  • Pass – When actual query execution time is below or equal to the expected runtime
  • Information – When actual query runtime is more than the Pass threshold and less than the Information threshold
  • Warning – When actual query runtime is more than the Information threshold and less than the Warning threshold
  • Fail – When actual query runtime is greater than or equal to Fail threshold

There are two different ways that users can test the performance of their SQL Queries:

  1. Users can directly connect to any type of RDBMS profiles including the JDBC connection type and perform necessary SQL query validations from the QPT Dashboard.
  2. If users have SQL Queries saved in a local excel sheet, then they can download the sample template and add all the required SQL queries from their local excel sheet and upload them onto the QPT dashboard to perform the required validations.

First Method:

  • Click the ‘SCENARIO STUDIO’ tab.
  • Hover on the left pane options to view ‘Query Performance Testing’ snippet.
  • Click the ‘Query Performance Testing’ option.

Note: Alternatively, the user can click the ‘Query Performance Testing’ button available on the top to navigate to a new ‘QPT’ session page.

Path 01: SCENARIO STUDIO > Scenario Builder > Query Performance Testing

Path 02: SCENARIO STUDIO > Query Performance Testing button

image1.png
  • The user is navigated to a new ‘QPT’ session page.
  • A Profile drop-down list is displayed.
  • A ‘Refresh Profile’ button is used to refresh the ‘Profile’ session page.
  • The ‘Profile Info’ button is enabled as soon as the user selects any profile from the Profile drop-down list.
image2.jpg
  • Let’s select ‘MSSQL Northwind-QA’ from the Profile drop-down list.
  • A green tick mark appears indicating the Profile selection is successful.
image3.jpg
  • Let’s, select ‘49 queries.’

Note: A user can select any query number based on the requirement. Simultaneously, the user can also add or delete any selected queries.

  • Click the ‘Proceed’ button. 
image4.png
  • The below screen is seen where the user is required to add the Pass Threshold, Information Threshold, and Warning Threshold values for each query to validate each SQL query performance.

Note: Pass Threshold, Information Threshold, and Warning Threshold values can be selected in Minutes, Seconds, and Millisecond’s time frame from the drop-down list. Where 1 minute is equal to 60,000 milliseconds and 1 second is equal to 1,000 milliseconds. By default, the ‘Minutes’ time frame is displayed for these categories.

Note: By default, the ‘TimeOut’ is displayed to 3600 seconds but the user can change the ‘TimeOut’ period within seconds’ time frame only.

  • Three validations namely Pass Threshold, Information Threshold, and Warning Threshold is displayed.
  • Pass Threshold must be less than Information Threshold and Warning Threshold
  • Information Threshold must be greater than the Pass Threshold and less than the Warning Threshold
  • Warning Threshold must be greater than Pass Threshold and Information Threshold

Note: The user must follow the above specified validation rules, without them the he/she cannot validate the SQL query performance.

Note: All time frame units taken in Minutes or seconds or milliseconds in the front-end will be ultimately calculated as milliseconds in the back-end.

image5.png
  • If the user doesn’t add the time values (MSSQL-Category) according to the validation rules specified, a red color mark with a warning pop-up (One or more thresholds are errored, refer to help text) is shown.
image6.png
  • Let’s change the time values (MSSQL-Category) to 1 minute for pass threshold, 2 minutes for information threshold, and 3 minutes for warning threshold matching the validation rule criteria.
  • Click the ‘Save’ button.
image7.png
  • A ‘Save Query Performance Testing’ pop-up window is displayed.
  • Let’s enter the ‘QPT Test on SQL Queries’ text under the ‘Scenario Name’ text field.
  • Click the ‘Save’ button.
image8.png
  • The ‘Scenario Name: QPT Test on SQL Queries’ is displayed beside the ‘Profile’ drop-down list.
  • Click the ‘Execute Now’ button.
image9.png
  • The user is navigated to the ‘Query Performance Testing Summary’ session page.
  • By default, the ‘Summary’ tab is shown displaying the Execution Status and Result in the donut pattern with a number format.
  • Individual tiles in percentage format for all the categories such as Query Count, Pass, Information, Warning, Errored, and Fail categories are displayed on the right side.

Note: The QPT validation with 49 queries is failed here since there is at least one query under the ‘Fail’ validation category.

Note: Especially in tiles, if users find zero (0) queries in any of the validations including ‘Errored’ or ‘Fail’ percentage tiles, an option to click on it to go through its filtered report is disabled. Here, zero (0) is displayed for the ‘Warning Threshold’ condition (indicated in yellow color), where the user cannot click them to view a filtered report.

Each validation under the ‘Result’ donut session is highlighted in a unique color and different validations represent different colors as shown here.

  1. Green-Pass Threshold Validation
  2. Orange-Errored
  3. Blue-Information Threshold
  4. Red-Fail
  5. Yellow-Warning Threshold
image10.png
  • Hover and click the ‘Result’ Donut’s Green portion (Pass: 42) from the ‘Summary’ tab.
image11.png
  • The user is navigated to the ‘Details’ tab displaying the ‘Pass’ threshold SQL Queries.
image12.png
  • Hover and click the ‘Information: 2’ from the ‘Result’ donut.
image13.png
  • The user is navigated to the ‘Details’ tab displaying the ‘Information’ threshold SQL Queries.
image14.png
  • Hover and click the ‘Errored: 3’ from the ‘Result’ donut.
image15.png
  • The user is navigated to the ‘Details’ tab displaying the ‘Errored’ threshold SQL Queries.
image16.png
  • Hover and click the ‘Fail: 2’ from the ‘Result’ donut.
image17.png
  • The user is navigated to the ‘Details’ tab displaying the ‘Fail’ threshold SQL Queries.
image18.png
  • Click the ‘Pass’ percentage tile from the ‘Summary’ tab.
image19.png
  • The user is navigated to the Details tab displaying the ‘Pass’ threshold SQL Queries.
image12.png
  • Click the ‘Information’ percentage tile from the ‘Summary’ tab.
image20.png
  • The user is navigated to the ‘Details’ tab displaying the ‘Information’ threshold SQL Queries.
image14.png
  • Click the ‘Errored’ percentage tile from the ‘Summary’ tab.
image21.png
  • The user is navigated to the ‘Details’ tab displaying the ‘Errored’ threshold SQL Queries.
image16.png
  • Click the ‘Fail’ percentage tile from the ‘Summary’ tab.
image22.png
  • The user is navigated to the ‘Details’ tab displaying the ‘Fail’ threshold SQL Queries.
image18.png
  • Click the ‘Details’ tab to view all the Pass, Information, Warning, Errored, and Fail SQL Queries.
  • Using CSV and Excel buttons, the user can download the SQL Queries validation results.
  • Click the ‘Email’ button.
image23.png
  • A ‘Send an Email’ pop-up window is displayed.

Note: By default, support@getrightdata.com is displayed in the provided text field. The user can change the email address and send it to any required person.

  • Click the ‘Send’ button to send an Email.
image24.png

Second Method:

  • Click the ‘SCENARIO STUDIO’ tab.
  • Hover on the left pane options to view the ‘Query Performance Testing’ snippet.
  • Click the ‘Query Performance Testing’ option.

Note: Alternatively, the user can click the ‘Query Performance Testing’ button to navigate to a new ‘QPT’ session page.

Path 01: SCENARIO STUDIO > Scenario Builder > Query Performance Testing

Path 02: SCENARIO STUDIO > Query Performance Testing button

image1.png
  • The user is navigated to a new ‘QPT’ session page.
  • A Profile drop-down list is displayed.
  • A ‘Refresh Profile’ button is used to refresh the ‘Profile’ session page.
  • The ‘Profile Info’ button is enabled as soon as the user selects any profile from the Profile drop-down list.
image2.jpg
  • Let’s, select ‘MSSQL – TPCH’ from the Profile drop-down list.
  • Click the ‘Download Excel Template’ button.
image25.png
  • The downloaded excel template appears as shown below.

Note: By default, ‘QueryName’ and ‘SQLText’ columns are added and displayed.

image26.png
  • Let’s add a Query name ‘orders’ with SQLText (select * from orders) as shown here.
image27.png
  • Now, click the ‘Upload queries from excel file’ button.
image28.png
  • Now browse and select the required excel file (RD QPT-02082022101628) and click the ‘Open’ button.
image29.png
  • The SQL query is uploaded onto QPT Dashboard.
image30.png
  • The user can continue from point 'Click the ‘Proceed’ button' which is highlighted in red color in the first method.
Was this article helpful?