Here are some reasons why I prefer inline (though we do use stored procedures here and there, especially for reporting). t
-
Dynamic SQL. Most of the list screens we have in our application include various combinations of search criteria that may or may not be used. Stored Procedures (at least pre-SQL 2005, which I havent used yet) dont let you conditionally change the statement, you either repeat the modified statement for each different criteria) or you do (field = THIS PARAMETER or THIS PARAMETER is null) style coding which I have seen greatly slow down the efficiency of a query when there are many of those conditions. There are places in our Dynamic SQL screens where we would have to write 1000s of Stored Procedure statements to mimic all the ones that can be generated dyamically.
-
Searching your code. When I need to see where fields are being used for update and insert (which we do not do via stored procedures) I can just search the code directories and easily find the places using it). If in stored procedures I have to do one pass to find which stored procedures are involved and then many more passes to find where all these procedures are called. I believe it is far more maintanable to see usage of your SQL right in your code… whether it is in a DB layer or not.
-
Showing your SQL statements. In our ASP (classic and .Net) we call GetDisconnectedRecordset(), ExecuteSQL() or GetDataset() functions (or methods) that wrap around the native ADO code. In doing so, we can consistently trap errors, record out of bounds timings (outside of performance monitoring), and even more importantly FLIP A SWITCH ON OUR WEBSITE and instantly see every query going on to load every page and during every update. That alone is worth its weight in gold. Debuging in many cases is reduced to seconds. And for new people… you want to see what tables and fields are being used? Turn on ShowSQL and get your answer right on screen!.. HUGE!.. and possible because its INLINE SQL and all run through common routines. Our common routines also include logic to look for, not process, and alert us to any injection attacks. The “showSQL” functionality when in classic ASP shows up right on the page where the SQL is called (very 2 tier)… in .Net where we have a data layer, our SQL calls write their processing info to a string that we can then get the value of pre-render to display when ShowSQL is on (which is only allowed for developers)… so that is a way to still keep your tier separation, if your app needs it, but still have showSQL functionality. The other thing I can note, with our inline usage, whenever we choose to move a statement to a stored procedure, we turn on Show SQL and in cutting and pasting we are at least 80% of the way there in one shot. We format our SQL statements in code with spaces and carriage returns and on output replace with %20 and BR so the statements are VERY readable.
-
Many people dont like to talk about the actual needs of the application you work on. Do you need true Ntier separation? Are you overworking for potential scalability that may never matter? If you structure your app well, it should come down to matters of organization where you could separate to different DLLs and physical machines IF you need to. If you are building EBAY with millions of hits every hour, you have different needs than a company specific app not exposed to the public… with 1000 users and only 100 active at any time. In that type environment, the complexity of splitting your tiers up so dramatically may not yield you much benefit either in development or performance. In fact, the more tiers you have, the more scalable you MIGHT be (if you did it well engouh), but every tier adds more going on in terms of processing. If you read your data from the dataset/recordset and then move it to XML, array, string, etc, and then have to unpack it in the next tier just because you refuse to use DB objects in that tier, imo, that can be going too far in many situations and slowing down your processing needlessly. Calling your SQL within your ASP page code isnt always automatically wrong or due to having weak experience… you can make a case for your situation that it might be the right thing to do (especially if you arent dealing with lots of it and lots of concurrent users). Part of our job is to use the right solutions for the task at hand and there is such a thing as overworking a solution! Same with objects… object oriented isn’t automatically better, especially on the web. If you are building robust objects and now your page needs a few things from a few “objects” but now you have to load all of them completely to get your data, that is overkill compared to having SQL return only what you need for the page in a statement (even though that is not an object-oriented approach). I always believe the NEED at hand is the best way to dictate the approach… not a blanket approach for everything. I have done many n-tier systems and many two tier… neither is truly an EVERYDAY right or wrong approach, each can be the right approach depending on the need. Especially on the web, if an area lends it self to business objects… do that… if it doesnt, dont be afraid to NOT use business objects there. Ultimately, efficiency matters… and in many cases inline SQL right in the page is going to be more efficient and responsive. As someone said, inline vs stored proc is always an evaluation of pros and cons… both from developer viewpoints, and from the needs of the application. What is right in one case might be wrong in many others, but it doesnt mean each approach itself is always right or wrong.
-
Maintenance. If every SQL call in a large app is in stored procedures, I think you have a huge mess. It’s alot to look through… and SQL Server (or most database tools) do not give you much in terms of choices in organizing. SQL 2000 and before (again, I havent used 2005)… all stored procedures are in ONE directory and you only control the naming. Even though you can use .SQL script files to initiate changes (and get into source safe) (which we do) it still becomes a mess juggling all of these… especially when some are so simple they hardly are worth the bloating of the DB objects.
-
Bugs due to stored procedures separation of statements. If you add new fields to a table and thus have to change insert and update stored procedure to accept these fields, but have 6 apps using it. As soon as you change the procedure, you better have updates for ALL those apps in place or else they will all break on the change. If you add new fields and use inline code, each app will generally work fine with the new fields there (if they allow nulls) and you can then work in changes to use those fields by updating those apps one by one all the while the prior version still runs (and there are cases where this is useful). Again, its relative to what your system does, who the users are, and how its rolled out, among many other things.
-
We use Crystal Reports… and the last thing we want is Crystal controlling the queries… so there we use stored procedures that Crystal Reports will work with.
-
We have a couple cases where we couldnt run a statement inline (too complex: many joined tables, group by clauses in statements acting as tables within outer queries that are also grouped) and moved it to a stored procedure because that made it run. Some would say, you shouldnt write such complex queries. I say, in relational databases, where you have many tables involved in the answer (this is what normalization leads you to often) if you can write a statement that is VERY complex but runs in 2 seconds compared to one that is made up of many simpler statements but then takes 40 seconds… I lean on the speedy approach!
For me, comparing inline SQL to Stored Procedures is like Comparing Manual Transmissions to Automatic. I think Automatic can isolate you from many things, but in the end, there so many more pros to the Manual transmission that Automatic cant hang with… and thus, thats also why I choose inline over Stored Procedures. It’s not a product of being “lazy” at all, it is that there are many overwhelming advantages that taken altogether make it, for me, the best choice… IF YOU USE IT SAFELY AND WISELY and focus on efficient statements and injection protection. I also hate the notion that you have to isolate the DB from your junior or UI only developers. If that is how you run your business, imo, you run your business wrong. If developers cant handle DB work, they simply shouldnt be developers! Developers need to be encouraged, trained, and expected to handle DB coding and learn how to write efficient SQL Statements (whether inline or in stored procedures), they either have to learn or should leave the profession. Having DBA handle all that and isolating the database from the developer is not an approach I favor. Having DBs review things, look for problems, and help developers, on the other hand is just as valuable as having people who do nothing but test the application!
When applications get too rigid in rules and layers and layer separation, I find very often functionality is skipped because its too hard to do given the structure of the app and the layers. That is a shame, because ultimately, a system is used at least 90% of the time to make someone’s job faster and easier… so the tools and techniques should not routinely impose limits on that goal!
Finally, inline SQL does not equate to crappy sql. You get way more bang for the buck in learning the right techniques to speed up your SQL Statements (hit indices, use joined table results as its own table as if the results were a single table and then join to those results)… and those techniques can produce amazing results whether inline or stored proc and they are the more important bang for the buck. Crappy is independent of tools and techniques. You can write ASP with inline SQL that is very clean. You can write .Net with stored procedures where its a mess. The nature of there being a mess is not driven by or limited by the tools used!
(Sorry for the length, but I unless I missed something, I think I added some significant additional thoughts here).
Too often, I think people use a certain tool or technique because they were told that was the way to do it… and they believe it like a religion. Think deeper than that… there is place for just about everything… stored procedures arent always wrong… inline SQL is certainly not always wrong… but how you use that mix really depends on your application, its purpose, and the size and needs of the user base. I have yet to work on any application where stored procedures for all queries and insert or update statements would have been the best approach and in every case I would have greatly missed the benefits that inline SQL provided in comparison!!