Weeding out inefficient queries through analysis

We will learn about techniques to identify possible inefficient queries through this recipe. The identified inefficient queries can then be re-designed to be more efficient.

Getting ready

You will need to be connected to your Snowflake instance via the web UI or the SnowSQL client to execute this recipe.

How to do it…

We will be querying the QUERY_HISTORY Materialized View (MV) under the SNOWFLAKE database and ACCOUNT_USAGE schema to identify queries that have taken a long time or scanned a lot of data. Based on that result set, we can identify which queries are potentially inefficient. The steps for this recipe are as follows:

1. We will start by simply selecting all rows from the QUERY_HISTORY view and order them by the time taken to execute:

   USE ROLE ACCOUNTADMIN;
   USE SNOWFLAKE;
   SELECT QUERY_ID, QUERY_TEXT, EXECUTION_TIME,USER_NAME 
   FROM SNOWFLAKE.ACCOUNT_USAGE.query_history 
   ORDER BY EXECUTION_TIME DESC;

   You...