Monday, March 17, 2014

HANA SQL View Versus Attribute View Performance

In addition to the normal SQL view, HANA has three new types of non-SQL views called Attribute Views, Analytic Views and Calculation Views.  SAP training makes a big deal out of these views saying that they have capabilities not found in normal SQL views and, perhaps more importantly, they're supposed to be faster primarily because they bypass the SQL parser and query optimizer; going directly to the calculation engine.

After sitting through the training on Attribute Views, I was skeptical of their value. The tool used to build AT views looks like a standard graphical query designer (similar to Microsoft Access). I did not see that AT views had any capabilities that could not also be done in SQL.

For me, the one thing left was the possibility that attribute views might be faster than their equivalent SQL.

So, using the weather observation data,described in the two previous posts, I ran a direct comparison.

Here's the SQL:

select OBS_ID,
STATION_ID,
YYYYMMDD,
LATITUDE,
LONGITUDE,
ELEVATION,
VALUE,
QFLAG,
(VALUE)/10*9/5+32 AS TEMPERATURE
from "WEATHER_DATA"."Critigen.WeatherData.Data::DAILY_WEATHER_OBS" d
inner join "WEATHER_DATA"."Critigen.WeatherData.Data::STATION" s on d.STATION_ID = s.ID
where s.STATE IS NOT NULL
and d.ELEMENT = 'TMAX'
and VALUE >= -500
and QFLAG = ''
order by d.YYYYMMDD

The Attribute View returns the same result set and is executed via a stored procedure.

I ran each query 8 times and the average execution time in seconds is shown below.  SQL takes about 7 times longer to execute than the Attribute View.

As AT View As SQL
0.2585 1.77225

Attribute views are clearly much faster than SQL.




3 comments:

Neolithic said...

Hi Mikey,

Nice post! Can you please share sample data which you used for profiling so that we can also reproduce the same locally.

Thanks
DW

Neolithic said...

Hi Mikey,

Nice post! Can you pls share your sample data that you used for profiling, we would like to reproduce the same in our instance locally.

Thanks
DW

Mikey said...

You can download the data from NOAA at

http://www.ncdc.noaa.gov/cdo-web/datasets.

Look under the section called "Daily Summaries" and click on the FTP link. Once you're in the FTP server, open the "by_year" folder where you'll find compressed CSV files with daily summaries of weather data from 1764 to 2014.