Using UserDefinedType (UDT) as Stored Procedure parameter will be a performance threat?
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
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.