Queries running slow and vertica functions .

kfruchtman

Hi Experts!
I have a question:
I want to ask if it is possible to create a function/procedure in Vertica that returns sql expression and how do I do it and use it.I have read all the documentation regarding functions and external procedures but I am still not sure I know exactly what to do and how it is possible exactly.
My scenario is that I have specific sql expressions that I run for reporting on a dashboard with variables from the dashboard.This SQL is repeating on almost every query of the dashboard and I would like to store it in the db for the following purposes:
1.for not having to write them on each query that needs them
2.for faster performance.Will it be faster if they are stored in the db?
3.for security reasons I prefer them to be in the db and not on the dashboard itself.
So if my sql looks aprox something like this:

WITH
selected_brands_by_operators AS (
select brand.id as id from brand where brand.operator_id IN ( select id from operator where name IN($arg1) )
),
filtered_orders AS ( select order as filtered_order,currencyId from orders where time between $arg2 and $arg3
and brandId IN (select id from Brand where name IN ($arg4 ) and brandId IN (select id from selected_brands_by_operators)
and deviceType IN (select id from device_types where name IN($arg5 )
and barcodId IN (select id from barcod_info where name IN($arg6 )
)
select * from filtered_orders

This sql is running on my dashboard many times, I want to put it in the db and just call it.A function returns one value and this returns rows.The query doesn't finish there I need to keep manipulating the data according to other dashboard requirements.

Does it make sense?Am I clear enough or more info is needed to understand my question?

Many Thanks for all your help
Keren

kfruchtman

anybody?

Vertica_Curtis

You can create simple functions in Vertica which take a parameter and return some result. For example, this function takes a UNIX epoch (The number of seconds since 1970) and returns it as a date.

CREATE OR REPLACE FUNCTION UNIXEpochToHour(UNIXepoch int) RETURN DATETIME
AS BEGIN
RETURN TIMESTAMP WITH TIME ZONE 'epoch UTC'::timestamp with time zone + UNIXEpoch6060 * INTERVAL '1 second' ;
END ;

However, Vertica doesn't support full SQL syntax inside these functions. We are adding support for this kind of functionality. I think it's slated for version 11. So, keep an eye out for that.

Having said that, I don't know the behavior of that, exactly. I do know that in some databases, it's preferable to have the function stored in the database, as it provides for slightly more performance, since the database can optimize the queries in some manner. I don't know if that will be the case in Vertica. But it could be the case that there's no real benefit gained from just writing the query vs. writing it as a function, unless it just makes your life easier in some way.