The Vertica Database Forums

Posted: **Fri Aug 23, 2013 6:53 pm**

Can queries like these be performed in UDFs to make the execution faster?

vsql -d VMart_Schema -w ${DB_PASSWD} -c "INSERT INTO GL.CURRENCYTABLE (CURRENCY_CODE, PERIOD_YEAR, BEGIN_BALANCE_DR,INDIAN_CURRENCY) SELECT 
	 CURRENCY_CODE, 
	 PERIOD_YEAR,  
	 BEGIN_BALANCE_DR,  
         (CASE  
		WHEN CURRENCY_CODE = 'GBP' THEN BEGIN_BALANCE_DR*95  
		WHEN CURRENCY_CODE = 'CAD' THEN BEGIN_BALANCE_DR*60 
	  ELSE 
		BEGIN_BALANCE_DR 
	 END ) as INDIAN_CURRENCY 
     FROM 
	GL.GL_BALANCES;COMMIT; "

[/color]

Posted: **Sat Aug 24, 2013 8:14 pm**

You wouldn't run SQL from a UDF. UDFs are intended for computational extensions for operations that aren't supported out of the box by Vertica.

If a query is underperforming, it's most likely a projection design issue. Try Database Designer with sample queries to create projections. This is an INSERT, so be sure to have only as many projections on the table as you need - more projections means longer load times.

--Sharon

The Vertica Database Forums

Performing SQL Queries in UDFs

Performing SQL Queries in UDFs

Re: performing sql queries in UDFs