FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

Hi Geoff, 

I understand your point, but I look at it another way.  

When you port your application to another system -- *if* you do -- all your
SQL will be in one place, fully parameterized and modular, too.  From my
small exposure to non-TSQL flavors, porting looks to involve many changes,
store procedure syntax (or lack thereof) being only one among datatypes,
triggers, rules, temporary tables, databases, system tables, permissions.  

My firm recently ported a giant MS-SQL system to Oracle; it took a year.
Stored procedure syntax was not of itself the major issue.  Things like no
CASE statement were.  

Of course, if you build a layer in your system and call it a "middle tier"
that provides data services to the application and speaks SQL to the server,
you can say your application is portable.  But that "middle tier" just
isn't, stored procedures or no.  

>From a performance point of view, you're right in that these days with CPU
speeds what they are, the savings of caching stored procedure access plans
is small, relative to the data access time.  The real benefits to
performance lie in *human* specialization.  Stored procedures are an immense
aid to a good DBA, who can optimize and facilitate data access in a way he
cannot if the application constructs its own query strings on the fly.  

That, I think, is the standard defense of stored procedures.  

Regards, 

--jkl

-----Original Message-----
From: Geoff Winkless [mailto:geoff AT farmline.com]
Subject: [freetds] Re: SQL Server Emulation

...it's a bad choice to
base your entire middle tier on stored procedures - because if you then need
to port to another system you need to build a new middle tier.