Solved

stored procedures in Sybase ASE 15

Posted on 2011-09-14
5
1,243 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 500 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sybase sql anywhere unload database 3 841
SQL select to return the latest row for each vacancyID 17 124
sql statement error 18 85
install sybase 15.7 over 15.0.x 2 79
The world seems to conceive of a curious bubble separating IT from “the business.”  More so than just about any other pursuit in the commercial world, people think of IT as some kind of an island.
This article was initially published on Monitis Blog, you can read it here . When it comes to deciding which approach to website performance monitoring is best for your business, unfortunately, like so many options in life . . . it depends. In t…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

730 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