db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: Using UserDefinedType (UDT) as Stored Procedure parameter will be a performance threat?
Date Wed, 19 Nov 2014 21:27:58 GMT
It is hard to say what is causing your performance problem. Can you 
profile this code and see which statement is taking too long? More inline...

On 11/19/14 6:52 AM, kosurusekhar wrote:
> 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?
It is hard to understand how a UDT would cause this slowdown unless 
there is some problem in its (de)serialization.
> 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.
This sounds like a job for the MERGE statement which was introduced in 
Derby 10.11.

Hope this is helpful,
> 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.

View raw message