HANA Table Variable access vs Column Table access SQLScript

[sap_cohort]

I have 2 "similar" SQL Selections that pull data from a Column Table. Some of the selections are the same. Between the 2 sql selects, 3 of the "where" clauses are the same and both have another one that is different.

1. Would it be better to create a single SQL using the similar where clauses and then select out the different data from Table Variable?

2. The big question I have Is:
Is there any difference in performance when selecting from a column table vs a Variable Table? I can't seem to find any information on this online.

Thanks!

[lbreddemann · Accepted solution]

There’s way too little information in your question to provide an answer.

SQL and SQLScript processing is a highly complex function and the final execution plan for any query depends on many factors (e.g. the actual query constraints, the data definition, the data distribution, software version and configuration...).

Why don’t you post your SQL statement or SQLScript code instead? You may also do some investigation yourself by building both options and check the resulting execution plan and performance characteristics via PlanViz.

Concerning your second question about the “variable table” I assume you mean “table variable”. The documentation explains this concept. When you define a table variable, you are NOT creating some sort of data container for your data (you are not allocating some sort of array for your records). Instead, you declare what the content of a variable should be like. These table variables, along with scalar variables, further SQL commands and SQLScript operations then form a kind of data manipulation graph.

Upon executing this “data manipulation graph” HANA checks what parts actually go into the result set(s), which parts are functional dependent on each other and/or can be run in parallel. Whether or not any of the table variables get “materialised”, that is, the intermediate result gets stored in memory, is only determined then.

This means, when you declare a table variable and execute a SELECT against it, HANA will likely resolve this SELECT to the base tables and run the selection against the tables directly if possible and if no intermediate result set is required otherwise.

All this is explained in the SQLScript documentation, tutorials etc., I highly recommend to give these documents a read.