Venu:
You will definitely gain more from a stored procedure if you have to do any
manipulation of the data before it gets sent back. Instead of calling into
the database multiple times from the client, all the work will take place
within the database server. This can prevent all the back-and-forth network
traffic that you would get if your client was manipulating all the data.
This also gives you a good level of abstraction between the program and the
database, which will be very useful if you have to change the database
structure later or if you just want to split out the work between someone
that really knows the database and someone that really knows Java.
Talking from an Oracle perspective, you'll probably gain more from using a
stored procedure if it's really complex. If you are doing simple operations,
like lots of simple updates, you may be better off using a prepared
statement. It can actually be faster. Oracle has some good batch execution
functionality as well for these types of things, but that's vendor specific
so you would be tying your code into their drivers to get that
functionality.
Check out the book JDBC API Tutorial and Reference, 2nd Edition for an
excellent reference on JDBC...
Mark
-----Original Message-----
From: Venu Gopal [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 12, 2000 8:22 AM
To: [EMAIL PROTECTED]
Subject: Re: Stored Procedures
Hi Karau,
Thanks for your reply..
I need to know a bit more on this..
Consider a program to display search results, written
using both stored procedures & JDBC query, and if
there are simultaneously lots of users accessing these
two programs. My questions are:
1. Which one will perform better in terms of getting
data from the database? The stored procedure one or
the JDBC query one?
2. Will the stored procedure once executed need not be
executed again unlike the ordinary JDBC query (which
execute every time the program is invoked).
3. What is the difference between prepared statement
(which is also a compiled SQL statement??) & stored
procedure?
Kindly help,
Cheers,
Venu
--- "Karau, Joe" <[EMAIL PROTECTED]> wrote:
> Stored procedures will be more efficient then using
> JDBC and directly
> running queries. However, the difference may not be
> noticed unless a large
> and complex query was performed. The reason that
> stored procedures are more
> efficient, is that they are stored in the database
> as optimized queries.
> Therefore, they do not need to go through the SQL
> optimizer, and often no
> longer need to go through the SQL parser.
>
> If using regular SQL however, the query will have to
> go through the SQL
> parser, and the query optimizer before it is run.
> Thus, taking more time
> than stored procedures. As I mentioned earlier, if
> the query is simple
> there will be no noticeable difference.
>
> Joseph Karau
> Kingland Systems
> [EMAIL PROTECTED]
> 507-536-3629
>
>
> -----Original Message-----
> From: Robuschi (Delfi) [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, October 12, 2000 7:35 AM
> To: [EMAIL PROTECTED]
> Subject: Re: Stored Procedures
>
>
> I'm just tryin the best solution between simple
> queries and Stored
> Procedures.
>
> So i placed 5 tables, integrally referenced each
> other, and the main table
> has 100.000 records.
>
> I tried a query that joins these tables and a stored
> procedure that does the
> same thing.
>
> The result was the same: 4 seconds to execute both
> the query and the stored
> procedure. So we decided to use queries... there are
> less troubles in
> deploying the application.
>
> Hope this can help you...
>
> Robuschi Roberto -------------------- Delfi srl
> P.za Ravenet 1/b - 43100 PARMA
> Tel. 0521/932411 Fax 0521/989045
> [EMAIL PROTECTED] --- http://www.delfi.it
>
>
===========================================================================
> To unsubscribe: mailto [EMAIL PROTECTED] with
> body: "signoff
> JSP-INTEREST".
> Some relevant FAQs on JSP/Servlets can be found at:
>
> http://java.sun.com/products/jsp/faq.html
> http://www.esperanto.org.nz/jsp/jspfaq.html
> http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
>
>
http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
>
>
===========================================================================
> To unsubscribe: mailto [EMAIL PROTECTED] with
> body: "signoff JSP-INTEREST".
> Some relevant FAQs on JSP/Servlets can be found at:
>
> http://java.sun.com/products/jsp/faq.html
> http://www.esperanto.org.nz/jsp/jspfaq.html
> http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
>
http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
__________________________________________________
Do You Yahoo!?
Get Yahoo! Mail - Free email you can access from anywhere!
http://mail.yahoo.com/
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff
JSP-INTEREST".
Some relevant FAQs on JSP/Servlets can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
Some relevant FAQs on JSP/Servlets can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
