Using UserDefinedType (UDT) as Stored Procedure parameter will be a performance threat?

[kosurusekhar <ko...@gmail.com>]

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.

Re: Using UserDefinedType (UDT) as Stored Procedure parameter will be a performance threat?

[Rick Hillegas <ri...@gmail.com>]

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,
-Rick
> 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.
>