Solved

stored procedures in Sybase ASE 15

Posted on 2011-09-14
5
1,200 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sybase SQL Syntax 2 271
sybase optimizer statistics 2 33
sql anywhere query 5 134
Not able to use a TRUNCATE command in 4 34
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now