I have come from a firm where it was common to embed sql into their inhouse VB app's. My new work has used stored procedures for every database function the app has to do leading to nearly a hundred sproc's in per database! My question is this: What is the industry standard considering this is a mid-sized firm (500 users), COM is not really used but may be a good option. The SQL Servers are stable and unlikely to change names etc. All opinions (even those biased towards SQL Server) are greatly appreciated. Thanks!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Sproc or embedded SQL?
- Thread starter Beesknees
- Start date
From what I've read stored procedures are a very efficient way to perform operations on data. They are compiled and can have optimized execution plans. I believe embedded SQL must be interpreted when executed ala script. At any rate the "fat server/thin client" approach is a pretty popular one. I'd be interested to know what Mr. Broadbent has to say since I am in the early development stages of a fairly complex project myself. Our current plan is to have a fairly generalized layer between the client and the database (because our accounting package will undoubtedly be changing in the near future) that will give us flexibility when using this data in our business specific reporting/operations. Where is the best place to put business logic?
I've worked in organizations that had it both ways. In a small but very successful dot com, the ASP developers generally did not use SPs but I started steering them that way.
In a large medical software firm, they have moved nearly all (98%+) dynamic SQL out of the C++ application and into SPs.
Generally, SPs have a slight performance advantage, with precompiled and stored execution plans (the more complex the query, the greater the savings).
But they also have a security advantage: you can remove all or selected rights (say, Delete) from using apps, and have them use SPs instead. You gain more control over what a rogue UI app may do to the data (such as a Delete without a Where clause). Robert Bradley
In a large medical software firm, they have moved nearly all (98%+) dynamic SQL out of the C++ application and into SPs.
Generally, SPs have a slight performance advantage, with precompiled and stored execution plans (the more complex the query, the greater the savings).
But they also have a security advantage: you can remove all or selected rights (say, Delete) from using apps, and have them use SPs instead. You gain more control over what a rogue UI app may do to the data (such as a Delete without a Where clause). Robert Bradley
- Status
Similar threads
- Locked
- Question
- Locked
- Question
