Serious performance issue: functions don't get pushed down to the source - ideas?
If we use a compiled function on a non-materialised view it severely affects performance. Materialisation is not an option since the latency will be too high.
Any ideas how to improve performance while still using functions on non-materialised views?
-
Comment actionsOfficial comment
Hi Lorenz,
Stored procedures work in a different way in Data Virtuality compared to relational databases which have stored procedures as well.
Select statements and views which are based on declarative SQL which can easily be pushed down completely to the underlying data sources ensuring the best performance. This is due to the fact, that the underlying SQL on the data source side is very similar for different databases.
For the procedural SQL language there is no such common standard which would allow us to push procedures to the data sources easily.
The procedural SQL language differs strongly between different databases. We do push down the declarative SQL inside the stored procedure if we see that its possible, but never the procedure as a whole.
Because of this, while DV stored procedures are good for managing the workflows which consist of heavy-load statements, they are never good for encapsulating the small reusable pieces of code which you want to use in different places.
To answer your question: "Is there anything else we can do to abstract away repeating pieces of code within virtual procedures?" -- there is nothing as of now, but there will be a few things upcoming.
One thing is already in the roadmap batch for early next year: in addition to SQL stored procedures, we will be introducing pushdown SQL functions (UDFs). With this, you would be able to define a function on DV side which maps to an SQL function call on the source side and then call this function on DV side in a performant way.
Another thing for which we are still gathering feedback but which looks promising already is adding the possibility to have Jinja templates applied to Data Virtuality code. This one has not yet gotten enough votes to get onto the roadmap planning list, but you can add your vote if this is something you would prefer having.
best regards,Matthias (on behalf of)
Nick
Please sign in to leave a comment.
Comments
1 comment