Support Forums: Message List - Stored procedure versus Java Selects

Java selects

Fri, 04 Jan 2013 06:40:36 -0500

more on Stored procedure versus Java Selects please!!

Abstraction

Tue, 08 Mar 2005 02:12:18 -0500

One reason i can think of why you would use stored procedures for select statements is abstraction of the database structure.
You can use views for abstraction, too.

Yes, view can abstract in some...

SP

Sat, 05 Mar 2005 02:52:27 -0500

IMO the biggest problem with SP is that they can move the bussines logic from app server to DB

app servers have very good horizontal scalability and relatinal DBs still not (maybe Oracle 10grid has; any comments?)

therefore we are trying...

Stored procedure versus Java Selects

Thu, 03 Mar 2005 22:39:00 -0500

I would imagine some performance gains using Stored procedures. Someone on this thread referred to a nice article that provides numbers for SQL SPs on Oracle.

Can I safely assume the same numbers for COBOL Stored procedures ? Am thinking they'd...

Stored procedure versus Java Selects

Thu, 03 Mar 2005 15:56:35 -0500

Stored Procedures have a couple of advantages for read-only queries:

- Data access logic can be maintained in one place and re-used across multiple client applications (these could be Java apps or other languages)

- There are often...

Stored procedure versus Java Selects

Thu, 03 Mar 2005 10:11:35 -0500

I had a chance to perform a study for a client who (I kid you not) had about 600 Oracle stored procs that did nothing but running single select statement. I was not able to detect any performance/memory differences between running one of those procs and...

Stored Procedures and Callable Statements

Thu, 03 Mar 2005 07:50:11 -0500

Your tone is extremely patronizing.

I'd like to see some evidence for your '65 executions' claim.

Normally its Java developers who are responsible for coding queries, whether as Stored Procs or app code, so I'm not convinced by our second...

Stored Procedures and Callable Statements

Wed, 02 Mar 2005 21:58:34 -0500

To convince you it's going to be hard. You probably write very good SQL and you know how to tune with SQL hints. That was me 6 months ago. I'm a firm believer now in dumping the SQL queries because the stored pocedure queries are effective...

Stored procedure versus Java Selects

Wed, 02 Mar 2005 18:43:22 -0500

Think about having to repeat that query for every condition for every sort order. Add to the mix that I cant parameterize "fetch" size in DB2 with static SQL SPs.

Also generally, selects could get really complex unlike...

Stored procedure versus Java Selects

Wed, 02 Mar 2005 13:39:17 -0500

Kind of a silly one but, perhaps, consistency, and not abstraction, is the answer. If you are already keeping insert/update/delete activity in sprocs, why not keeps select statements there as well? Why have two separate places for SQL code? In a way,...

Stored procedure versus Java Selects

Tue, 01 Mar 2005 10:21:33 -0500

If I recall correctly, MS SQL Server could not cache SQL statements (parse tree and execution plans) unless they were in a stored procedure - even if you used a PreparedStatement. So encapsulating them in a stored procedure was a great performance boost....

Well

Tue, 01 Mar 2005 09:57:59 -0500

Sir, I fundamentally disagree with your assumptions

The table and column names are now hard coded in your application.

Its good practice to place SQL in config files (usually) rather than hardcode them. In terms of abstraction,...

Performance

Tue, 01 Mar 2005 03:16:34 -0500

Maybee this article is of some help:
http://www.oreilly.com/catalog/jorajdbc/chapter/ch19.html

It is an oracle bases article with some metrics on the oracle...

Stored procedure versus Java Selects

Mon, 28 Feb 2005 17:19:47 -0500

Regarding the abstraction thing - one can argue that select statements would be abstracted using Data Access Object patterns. That way they remain in only one layer and are not "proliferated".

We use DB2 as backend and use cobol stored...

Abstraction

Mon, 28 Feb 2005 11:31:27 -0500

One reason i can think of why you would use stored procedures for select statements is abstraction of the database structure.

You can use views for abstraction, too.