Qlikview SQL Timer – see how long each query takes

About a year ago I was working with a client to clean up a series of Qlikview load scrips that had grown to be quite lengthy over time. Each of the scripts consisted of several SQL queries.

As part of the process to investigate the queries, I was asked if there was a way for us to keep track of the time it took for each SQL query to run inside the script.

It is certainly possible to do this. All we have to do is create some variables, insert the start and end times into these variables, and store them in a table after each SQL query.

Here is the process:

First, I create the variables that will store the start date and time

let v_date_sql = today(2);
let v_start_sql = time(now());

Next, I will allow the SQL query to run.

After the query I create two more variables. One for the end timestamp and the other will be a name given to this particular SQL query (to differentiate it from the others).

let v_end_sql = time(now());
let v_query_sql = 'Query Name';

Once these variables have been established, I will store them before moving on to the next SQL query. I will create a table called ‘SQL_Timing’ and use the autogenerate statement to add this information as a single line in my new table.

SQL_Timing:
load
'$(v_date_sql)' as SQL_Date,
'$(v_start_sql)' as SQL_Start,
'$(v_end_sql)' as SQL_End,
Interval('$(v_end_sql)'-'$(v_start_sql)','ss') as SQL_diff,
'$(v_query_sql)' as SQL_Table
AutoGenerate 1;

I repeat these three steps for each SQL query in my script. Each time, I will be adding a new line of data to my SQL timing table.

After the last iteration, I will use a STORE command to save this information into a qvd file.

let v_file_save = timestamp('$(v_date_sql)','YYYYMMDD');
STORE SQL_Timing INTO '../SQL Timing QVDs/$(v_file_save)_sql_timing.qvd';
DROP Table SQL_Timing;

Using the DROP Table command, I removed this table to ensure the extra data is not saved into the qlikview file itself.

As a result, I will have a series of QVDs that can be loaded into another qlikview document. I can investigate the SQL loads and get a great picture of how the SQL queries compare to one another.

Here is the script without breaks that can be easily copied-pasted.

let v_date_sql = today(2);
let v_start_sql = time(now());

//*** the SQL goes here***

let v_end_sql = time(now());
let v_query_sql = 'Query Name'; //*** note this is the SQL query name. Must be changed for each query***

SQL_Timing:
load
'$(v_date_sql)' as SQL_Date,
'$(v_start_sql)' as SQL_Start,
'$(v_end_sql)' as SQL_End,
Interval('$(v_end_sql)'-'$(v_start_sql)','ss') as SQL_diff,
'$(v_query_sql)' as SQL_Table
AutoGenerate 1;


//*** after the last iteration, include this script to save the information. Note folder and file names must be appropriately changed ***

let v_file_save = timestamp('$(v_date_sql)','YYYYMMDD');
STORE SQL_Timing INTO '../SQL Timing QVDs/$(v_file_save)_sql_timing.qvd';
DROP Table SQL_Timing;

 

, , , , ,

Comments are closed.