VB code vs. Stored procedures
-
Could some one help me answer this question...
I work for a small software development company. We write programs in visual basic and we use sql server for our data.
Which would be better... using stored procedures in a vb program or writing sql statements in the vb code? My boss and I are having an argument about this. He's saying its better to write the vb code because you can just send the customer a new .exe. instead of having to both send a new exe and a new db.
Isn't there a way you can write a self executing script that would alter the stored procedure, thus relieving you of writing new vb code and creating a new .exe of the program?
What would be the best solution to this?? Any suggestions would be appreciated.
-
In general I would keep SQL within stored procedures for umpteen billion reasons that have been discussed on this site.
- Stored procedures are compiled
- Are more secure
- Can be debugged on the SQL Server.
- Provide a layer of abstraction between the application and the database.
This approach also spreads the load between the application and the database server.
Yes you can send scripts to update the database application. See BOL on running query analyser from the command line.
You can also script the security of the objects.
IF EXISTS(SELECT 1 FROM dbo.sysobjects where type='P' and Name='usp_myproc')
DROP PROC dbo.usp_mypro
GO
CREATE PROC dbo.usp_myproc @myarg etc
AS
/* Best practices comment block */
SET NOCOUNT ON
....etc
GO
The "Just send them a new EXE" approach suggests that the application has not been designed as a high capacity app.
We take the approach of having a database layer, business code/logic stored in various DLLs or EXEs depending on how we want the pool of memory to be used.
As far as I am concerned the only situations where I would use VB to submit SQL code would be if the complexity and variability of the SQL was too great to be encoded in a stored procedure. In such a scenario my first question would be "how can I simplify this" rather than "how can I encode this".
Hope this helps.
-
Thanks for your help. -
Laughing.
Your boss is not a DBA; he just plays one in his office. Thanks for the laugh - "because you can just send the customer a new .exe. instead of having to both send a new exe and a new db. "
He sends the customer a new database each time he updates a stored procedure? He's scary. It must be a nightmare for him to figure out what to do with the client's data every time he wants to make a change. No wonder he doesn't like databases.
Please talk to him in a quiet room and explain, slowly, that he can make changes to the database with scripts - very small text files. The developer makes the changes, saves them to a .sql file. Then you copy the file to the customer's database and, if you have the right permissions (and he should if he's destroying the database for every version of the software) you run the script and that stored procedure is updated. No fuss. No mess.
Enjoy
Quand on parle du loup, on en voit la queue
-
You're right he's not a dba and wouldn't I love to show him your comments!! hehehehe!
Glad I was able to share a good laugh. I'm constantly laughing over here!
-
Hi ,
I feel you should make your boss also happy.
Write one VB application which executes the script without using sql QAnalyzer . you can use the osql utility to execute the sql files. Jeswanth
--------------------------------
-
Nancy,
You are really doing research as a script writer for Dilbert column aren't you
-
Actually, your boss has a valid point. There are many reasons to prefer SQL code to SPs. I think that too many DBAs buy into the hype, and don't analyze things from a developer's or business point of view, and almost never use DATA to support their arguments.
1) IMHO, it is MUCH faster to develop, modify, and debug apps using VB/VBA and SQL rather than SPs. At least twice as fast, IMO. Time is money. It's often much cheaper to add an extra CPU than to add an extra developer.
2) IMO, SQL debugging is FAR easier with VBA/SQL/ADODB, than using the the brain dead T-SQL development environment. Any performance advantages are usually trumped by the increased devel time and the risk of introducing bugs in the SP due to the inabilty to use a real coding language and debugging environment. In particular, array and error handling in T-SQL are utterly brain-dead. The lack of a true Help/ intellisense environment is a major productivity killer.
3) I have never seen benchmarks that actually document (science, anyone) that SPs are SIGNIFICANTLY faster for small to medium size projects. (For -extremely- active SQL Server machines, SPs are likely to become more important.) Since SQL Server 2000 can use prepared SQL statements anyway, repetitive execution of SQL statements is claimed (by MS in BOL) to be almost as fast as SPs. In the BOL, MS claims that performance advantages of SPs are much less in the 2000 version. Of course, it is almost always better to use set based T-SQL, than VBA looping, but this can also be accomplished without SPs, negating any performance advantage.
4) The simplest of apps requires at least 3, and usually many more SPs on most tables. This is a T-SQL code maintenance nightmare, since there are really no T-SQL code maintenance tools that rival the VB/VBA editor. I suppose this may be somewhat easier in .NET.
((One can produce HUGE development savings by using SQL statements [or SPs] with a true DB development like Access (in the hands of an expert Access developer) for many projects. Unfortunately, many closed-mided people ridicule Access and similar tools, prefering to take the "path of most resistance" (e.g. VB) instead. Access projects can do some truly amazing things with straight SQL, as well as with SPs in the hands of an expert developer. But alas, there are very few expert Access developers in the world, resulting in some truly horrible Access apps out there. But there are also plenty of horrible VB db apps out there.))
5) It is not at all trivial to update SPs in a production system. Often, SPs are used by multiple procedures, multiple apps or multiple versions of an app, for multiple reasons, and a change in the back or middle tiers could seriously impair apps, in ways that you may not even be aware of. This is particularly acute in a commercially-distributed application, where your customers/users may use your data tier and SPs for their own custom projects, or to interface with other commercial projects. Changes in the front end client SQL are less likely to affect any other clients using the data tier. In any case, it is often best to create a new renamed SP than to update the original. Of course, this introduces other problems.
6) The security advantage with SPs is overblown. It is trivial to check for SQL injection and the like. You should always use a secured network connection for any SQL Server app. ((In many cases, you can also use application roles to completely prevent users from accessing database objects outside your app. Unfortunately, App Roles are severely underused in the real world, even when they are the most secure option for non-web apps.))
7) The VB/VBA code to execute parameterized SPs is significantly longer and more diffiicult to implement correctly with ADODB than custom SQL statements.
8) Of course, it is not really possible to secure SP's because they can always be decrypted by anyone with db access that can use Google to find the decryption methods.
9) In the real world, where time is money, it often makes perfect sense to develop using SQL, rather than SPs.
10) In the case of triggers and functions, I agree there is usually no option other than SPs (I consider triggers as a kind of SP anyway). As it stands now, I have to write complex SPs and update scripts just to maintain my hundreds of triggers. Major headache, and extremely bug-prone.
Practically speaking, I prefer to develop using SQL in VB or Access VBA, and then selectively convert SQL statements to SPs when it appears to confer a perfomance advantage. This SQL-first approach confers a huge performance advantage in terms of my deveIopment time. However, I still use SPs for all complex data manipulations, and for most schema manipulations. In a complicated, user-friendly app, with many custom data-filled combo boxes etc, I just can't understand (show me DATA, if you disagree) how the all-SP development approach can be cost-effective. Once you have a working, debugged and STABLE app, then you can think about converting to SPs, but I still think that maintenance of the hundreds to thousands of SPs per app (plus all the scripts to update them) could be a poor business and development decision in many situations, and may result in an inferior end-user experience due to the misapplication of developer time.
-
Point 1
If you are a one man band working on small projects then the VB/Access route can be the fastest way to go.
If you are working in teams on larger projects then server based technologies become ever more important.
I tend to use Access as a fast prototyper.
Point 2
SQL QA has a debugger built in.
You are going to have to debug your SQL anyhow so what is the difference between debugging a stored procedure and debugging embedded SQL?
Point 3
See todays front page article "Stored procedures and caching"
Point 4
I personnally don't use any third party developer tools with SQL. My boss, Mr Long Pockets has extremely short arms and his wallet has been closed so long that it has healed back up into a cow.
I know they exist (Frank, where are you when we need you) so this argument doesn't hold water.
I stored most of my base SQL in templates that I can recall at a drop of a hat so T-SQL development is pretty fast for me.
Point 5
Granted that updating stored procedures in a large environment is not always trivial, but welcome to the big development world. I could counter that replacing a DLL isn't trivial.
Point 6
The security advantage of stored procedures is not overblown. If you allow direct access to a database table then it doesn't take long before some smart arse with MS Access tries to dabble.
If you only grant access via stored procedures and only grant specific database roles access to specific stored procedures then your security is much tighter.
Point 7
Use boilerplate code.
Point 8
Lock down syscomments.
DLLs and EXEs aren't that hard to decode either.
Point 9
If your expertise is VB/VBA then you are going to develop faster using VB/VBA.
If your experise is T-SQL then you are going to develop faster using T-SQL.
IMHO people get tunnel vision with their tools to the point where they try and do array manipulation in SQL or looping through records to do set based changes in VB.
Its a bit like someone using a hammer to knock in nails.
Point 10
Our developments tend to be large so we have a formal methodology for development.
This means that our developments tend to be weighted towards the planning stage of the project.
This means that developing in VB, C#, Java, PHP, C++ or T-SQL makes very little difference in terms of delivery time. We are working to a defined specification.
If you are using a more informal approach where the customers are allowed to move goal posts throughout the development then it is hard to keep all the balls in the air.
Once your developments get larger the informal approach starts to fall down.
-
Where do you guys find the time to code in either environment?
-
Before a decision is made either way, you need to consider scalability.
Let's assume the function is complex - requiring multiple database queries and you're building an interactive app. Can you afford the network traffic sending result sets across the network to an app server just to let it process the business logic?
On the other hand, you could run out of CPU cycles (or memory) running the business logic from within the stored procedures. This comes down to KNOW THE ENVIRONMENT.
-
Yes, it's not always such a simple answer!
-
Code generation of stored procedures based off of the database schema shortens the development time also. In addition, for smaller projects code generation of the Data Layer in VB shaves yet more time.
Try using a freeware product called CodeSmith. It really helps.
-
A situation where I use dynamic SQL is in an "advanced search" type query where there are a large number of optional parameters.
Even then I use a stored procedure to run the query via sp_ExecuteSQL because I have found that users tend to use the same functionality and therefore the cached query plan gets used.
For add, update, delete functionality I stick with bog standard stored procedures because there is a known and static query plan.
My understanding of what happens when you have an app submitting dynamic SQL is that
- you are submitting a large volume of data (your query) across the network to your server
- The server then has to interpret this and prepare a query plan
- The server then executes the query
- Any data gets returned from the server to the client
When you have an app submitting parameters to a stored procedure executing an sp_ExecuteSQL statement is
- You submit only the call and parameters (therefore vastly less data) across the network.
- The server determines whether the query plan can be retrieved from the cache. Only if it cannot be retrieved is the query recompiled.
- The server then executes the query
- Any data gets returned from the server to the client
My main beef with dynamic SQL is not that it is used but that it is used badly. Developers tend to take shortcuts
- SELECT *
- Not qualifying objects with the owner.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply