Hi folks, In one of my requirement i have variable number of store procedure parameters to use, and max will cross derby default limit 92 parameters, so i used a UDT (a java class which is having all properties will be -1 by default) in store procedure i will get this class object and check what columns need to update/insert.
While making our application benchmarking, the behaviour of this store procedure is not same. Some times it is working less than a second, some times it going up to 20 seconds to execute. Is this UDT will be causing this delay? I have work load of 10K threads running concurrently, and i am allowing max 100 threads to access this stored procedure. Stored procedure functionality: 1) check row exists or not by using name column. (name is varchar size of 20 & having index) 2) if not exists get the default data from another table & insert into my table. 3) if row exists then update the columns. 4) Row exists or not exists(after executing 2nd step), I have 3 insert statements into 3 different tables. I am using JDBC to access the db, with AutoCommit off. We have tomcat connection pool with max 300 active connections, 100 minimum connections. Please suggest me to make this store procedure consistent. Thanks in advance. -- View this message in context: http://apache-database.10148.n7.nabble.com/Using-UserDefinedType-UDT-as-Stored-Procedure-parameter-will-be-a-performance-threat-tp143245.html Sent from the Apache Derby Users mailing list archive at Nabble.com.