Solved

stored procedures in Sybase ASE 15

Posted on 2011-09-14
5
1,222 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:olmuser
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:olmuser
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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Agreggate sums in sql 1 523
Sybase Customized sp_thresholdaction 2 668
Sybase initialize new disk in Solaris 10 failed 2 940
SSIS - Update a Sybase Table 33 163
If you thought ransomware was bad, think again! Doxware has the potential to be even more damaging.
In this article we will learn how to backup a VMware farm using Nakivo Backup & Replication. In this tutorial we will install the software on a Windows 2012 R2 Server.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

777 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