I have written a UDF that is supposed to get a string as its only argument, which is format. And inside the UDF i'm generating a code based on the format, and after generating this. i'm inserting this code into the database using JDBC. if the insertion query gets executed successfully. i return the code from function. if not ? i regenerate the code (Code is generated using few random numbers and few random characters)
Now the problem is JDBC connection takes 1 sec the make the connection to Teradata Database from inside the UDF.
I have a table, lets say customers_email table with 7 Million rows in it.
when i run following query it is taking a lot of time. like the whole day.
select myUDF('Win#!'),email_addres from customer_email;
it is taking a lot of time. Kindly suggest.
PS: if i remove all the database JDBC interactions the result is really fast. but database interactions are the requirement from a client.
Doesn't sound like a good design - I would not recommend your approach.
It is never a good idea to create an application which require a new connection per row to be processes - you will always face the issue that the connection will take some time.
It sounds like the system running the udf and the system storing the generated data are the same. If true why not return the default and the alternative code from the udf and insert the data with standard insert / select into the target table as a set operation?