[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

stored procedures in Sybase ASE 15

Posted on 2011-09-14
5
Medium Priority
?
1,314 Views
Last Modified: 2012-05-12
Is there any real benefit in creating a storepd proc as ooposed to running adhoc sql statements in Sybase?

My understanding used to be that stored procs (SP) are precompiled.  And Sybase documentation claims recompiling takes only a fraction of a second. Doesn't that make the benefit of precompiling meaningless?

Also, this link claims there is no precoimpiling at all

http://www.scarydba.com/2009/09/30/pre-compiled-stored-procedures-fact-or-myth/

So DBAs/DB developers out there should I prefer SPs to SQL queries in Sybase ASE 15? Why?
0
Comment
Question by:Mydeen Yussouf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 14

Expert Comment

by:Jan Franek
ID: 36537115
The article is about MS SQL, not about Sybase ASE. But partially it applies - the procedure execution plan is created at first execution.

There may some performance gain in next execution, however ad-hoc plan can be also cached in statement cache (if you have enabled it).

In my opinion the main advantage of SP over ad-hoc statements is security. It's much harder to do sql injection if you use procedures. And you don't have to assign any access rights to tables at all - you may better control who and how uses your data if you allow only procedures.
0
 
LVL 1

Author Comment

by:Mydeen Yussouf
ID: 36537276
Hi, thanks for that.

Could you please show me the syntax on how to use the stement cache in sybase using embarcadero rapid sql?

Also, could you please elaborate on the security aspect of SPs?  if I am just allowed to execute predefined stored procs and explicitly restricted from running SQL statements I can see how that limits what data can be retrieved and queried and thus gives the DBA / DB owner perfect control over the DB opertaions & data security. Is that what you mean? Primarily I deal with stored procs and sql statements that are executed from an application. So, given that, does it make any difference from a security perspective. Just making sure we I got you right.
0
 
LVL 14

Expert Comment

by:Jan Franek
ID: 36537306
Ad statement cache:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag2/html/sag2/sag2108.htm

> if I am just allowed to execute predefined stored procs and explicitly restricted from running SQL statements I can see how that limits what data can be retrieved and queried and thus gives the DBA / DB owner perfect control over the DB opertaions & data security. Is that what you mean?

Yes.
0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 2000 total points
ID: 36559188
It is wrong to say there are no precompilation benefits to Sybase ASE.

There is a two stage compilation process. Creating a stored procedure creates a "query tree". This is done only once at create time. Object names are converted to ids, commands are replaced with tokens - think of it as the assembly language version of the procedure. Full compilation occurs whenever a query tree is turned into a query plan which goes into procedure cache. The rules for this are a little complicated but let's just say that in a single-user system with infinite memory we would only ever generate a query plan once, but in a multi-user system with finite memory we may be doing this many times for any given stored procedure.

Would we still want to do this even if it always took "less than a second"? Absolutely. If I have 100 concurrent users who are making a procedure call every 1-3 seconds then I totally want to save sub-second compilation time per call.

I don't know where you got this "fraction of a second" quote from. It totally depends on how complicated the procedure SQL is. It isn't hard to deliberately write terrible SQL that joins 50 tables where compilation will take a good deal longer than a second - it could take hours. Basically what's not to like, saving any time at all is a good thing.

But even if stored procedures never saved any compilation time at all, we still want them - and that's for network traffic. If I have a proc with 100 lines of SQL in it, then calling that directly from a client application might require 10kb of network traffic, but calling a procedure with identical code in it can be done in maybe 200 bytes. That adds up to a lot of savings in round-trip time and network utilisation and is enough to justify procedure use even if we never gained anything else from it. (But we do gain from precompilation as above.)

Stored procedures are totally about performance. Statement cache is an attempt to gain some of the benefits of procedure use for regular SQL... isn't that a bit of a hint that procedures must be better? :)
0
 

Expert Comment

by:deepak_dhurandhar
ID: 36602021
Hi.. 1 important point that I would like to make here is regarding how stored procs help with managing access.

The SQLs inside stored proc are internally executed with proc creater's permissions (usually dbo). (except in case it has dynamic sql; Even that can be run assuming creater's permissions if you have "procxmode set to 'Dynamic Ownership Chain' ")

So once you have permissions to execute the stored proc means, you don't need to worry too much about underlying objects and their permissions.


HTH.

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

It’s time for spooky stories and consuming way too much sugar, including the many treats we’ve whipped for you in the world of tech. Check it out!
Are you looking for the options available for exporting EDB files to PST? You may be confused as they are different in different Exchange versions. Here, I will discuss some options available.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

656 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