?
Solved

Stored procedure takes a REALLY LONG time to finish when called by the application

Posted on 2007-10-09
10
Medium Priority
?
774 Views
Last Modified: 2012-05-05
Hi,

I have a Sybase stored procedure that will basically insert 50.000 records on a table. When I call this procedure directly from the database, it takes less than a minute to run.

However, when I call it from my Java application, it is taking forever to run! I waited for 30 minutes, then I gave up.

What should be the problem here? Any ideas?

Thanks in advance.
0
Comment
Question by:brunoguimaraes
9 Comments
 
LVL 92

Expert Comment

by:objects
ID: 20045566
how ru running it?
0
 
LVL 9

Author Comment

by:brunoguimaraes
ID: 20045596
I tried this way:

lStringBuilderSql.append(" EXEC procname ");
lCallableStatement = lConnection.prepareCall(lStringBuilderSql.toString());
lResultSet = lCallableStatement.executeQuery();

And this way:

lStringBuilderSql.append(" { CALL procname } ");
lCallableStatement = lConnection.prepareCall(lStringBuilderSql.toString());
lResultSet = lCallableStatement.executeQuery();

I need the resultset, cause the procedure returns rows in the end.
0
 
LVL 92

Expert Comment

by:objects
ID: 20045620
could be returning the 50k rows thats slow. try removing that just as a test
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 9

Author Comment

by:brunoguimaraes
ID: 20045653
No, I'm pretty sure it's not that.

It does not returns  the 50000 rows it inserts, but a few rows from another table.

I really have absolutely no idea what's going on!
0
 
LVL 9

Accepted Solution

by:
brunoguimaraes earned 0 total points
ID: 20046233
Just solved it!

The table I inserted the 50000 rows to had a trigger on inserts. When I ran the procedure from the application, the trigger executed some heavy stuff. I just had to modify the trigger a bit and now the procedure runs in less than a minute!
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 20047112
>>When I ran the procedure from the application, the trigger executed some heavy stuff.

Why did the trigger behave differently when triggered from the Java side?
0
 
LVL 9

Author Comment

by:brunoguimaraes
ID: 20052875
Because when I run it from the Java application, we insert in a log table the application user who inserted that record.

When I run it directly from the database, there's no application user, so there is no need to log.
0
 
LVL 92

Expert Comment

by:objects
ID: 20053078
yep, that'd do it :) good to hear u got it solved.
0
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 20135092
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Suggested Courses
Course of the Month15 days, 10 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question