In article Lrx@bony1.bony.com, thomasr@bony1.bony.com (Thomas Roche) writes: 
Author Message
 In article Lrx@bony1.bony.com, thomasr@bony1.bony.com (Thomas Roche) writes:

Quote:
> Being somewhat new to SQL and Sybase, I was wondering if there were any
> advantages/disadvantages to using embedded SQL in "C" code as opposed to
> calling stored procedures via dbcmd() calls.  I am aware of the obvious issues
> such as maintenance (ie. when a stored procedure is updated the code calling
> the stored procedure should be check for consistency, which could lead to a
> long QA session, etc.) and replication of code, but I was wondering what other
> benefits could be gained from one or the other (speed, performance, ease of
> use, etc.). Please post or e-mail and I'll summarize...

>                                            Tom R.

There is only one problem with using stored procedures on SQL server, as far as I am aware. There is a bug which causes the query tree to grow every time that the query plan is recreated. Eventually this causes the stored procedure to fall over and the only cure is to drop and recreate it. Sybase are aware of this problem but call it a feature (UK Technical Support) and inferred that it would still occur in 4.9.1. When I have experienced this problem the time between failures was not a constant, but it did

 not occur with all our stored procs, just the same few each time.
Sybase Tech Support (UK) were unavle at the time to give us any information as to what constructs caused the tree to grow quickly. Basically be aware, although storted procedures are preferable (due to performance, resusability etc.) they do have their occasional bad side.

There are circumstances when stored procedures cannot be used. This is because although you cannot use SQL variables as object names in queries.
        i.e




                SET ROWCOUNT 1

                BEGIN

                        FROM    sysobjects
                        WHERE   type = "U"


                        BEGIN


                        END
                        ELSE
                        BEGIN

                        END
                END
        which would give indications of number of rows in all tables.
This type of query can be written using openclient and "embedded sql", with the control performed in C.

Further if you are calling stored procedures on SQL server from open client it is better to use dbrpc...() calls (apparently for efficiency).

To summarise stored procedures are better when they can be used, but there may be circumstances where you cannot use them and you have to use Open Client and dbfcmd()



Tue, 26 Dec 1995 01:52:42 GMT
 In article Lrx@bony1.bony.com, thomasr@bony1.bony.com (Thomas Roche) writes:

Quote:

>There is only one problem with using stored procedures on SQL server, as far as I am aware. There is a bug which causes the query tree to grow every time that the query plan is recreated. Eventually this causes the stored procedure to fall over and the o

  << discussion of the stored proc bug/behavior where the
       query plan for the proc continues to grow till it
       chokes & has to be dropped & recreated  deleted >>

BTW,

From what I understand from one of the sessions I attended at
the '93 Users' Conference, this bug results from a design
decision (now regretted I would think!) made early on in
the development of the server software, and it cannot be
easily undone.  Changing this "feature" is so difficult that
it will continue into System 10.  However, System 10 will
offer a utility that will clean up the procedure space that
can be used in some type of housekeeping batch (or whenever).
I assume the utility either does something basic, like drop &
recreate a proc without human intervention, or something more
elegant (hmn), like cleaning up the query plan directly.

Either way, it'll be an after the fact, periodic fix.

:m
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

KaleidaTech Associates, Inc.    |
Huntington Station, NY  11746   |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



Thu, 28 Dec 1995 12:17:05 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. In article 5tv@nimno.wpa.com, john@wpa.com (John Bartley) writes:

2. In article 121344@is.morgan.com, duviv@is.morgan.com (Marc Duvivier) writes:

3. jobs@cnet.com, news.com, shareware.com etc etc

4. new deVBuzz.com article - eVB and SQL Server 200 CE

5. Passing a COM Object to another COM Object?

6. ABC2XML.COM: New Articles

7. New Articles from SQLJunkies.com

8. COM-Gen 2000 - Build 107 released - generates COM objects


 
Powered by phpBB® Forum Software