Embedded sqls in front end
-
Can anyone please direct me to any links that would probably list out the disadvantages of having embedded sqls written on the front end side of the application. I know having these sqls as stored procedures gives a better control from a database perspective but if anyone has more details about it , that would be greatly appreciated.
TIA
-
I don't have any websites I can direct you to, but here's a couple of my own...
- Unless perfectly written parameterized code is used, there is no chance of a cached execution plan being stored which will reduce performance in a "high hit" system.
- Making a small change to the database may require the app (or parts of it) to be recompiled and reimplemented. If a stored procedure is changed, that isn't necessarily required.
- There are few chances for the real database "pros" (DBA's, Data Modelers, SQL "tuning" developers, etc) to optimize code they can't see or aren't aware of.
- Violates 2, 3, and N-Tier (yeah, I know... old school). Presentation layer and data layer should usually be kept separate. How many times have you heard to NOT format output in SQL? So why would you write dynamic embedded SQL in an App?
- Please, nobody get mad at this... it's just an observation... your experience may be different... most App programmers who "have 3-5 years Java experience with {some} SQL experience" just aren't going to be able to write the same high quality, performance enabled, scalable SQL that an "application DBA" or skilled SQL Developer is going to be able to write. Things like "Hibernate" help a lot and is a great tool, but anyone who really knows Hibernate, also knows that Hibernate stinks for high volume performance. And even when App Developers are highly skilled in SQL, you still end up with a recompile in many cases.
- Why would you haul cement in a Cadilac? You wouldn't... you'd use a cement truck or a good sturdy pickup truck. Likewise, you wouldn't try to win a Indy Race with a cement truck. Use the correct tool for the right thing.
Just my 2 cents, though...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Don't forget about the security issues as well
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy: -
A debate from ServerSide.net: http://www.theserverside.net/news/thread.tss?thread_id=29071
Course notes, Web Access to Databases: http://lsirwww.epfl.ch/courses/cis/2004ss/part3%20WebDB.pdf
Practical experience: I've had to cope with numerous issues concerning the use of embedded sql in a number of typical scenarios including Third-Party middle-tier apps, Web apps that can't cope with an increase in load, mobile apps, you name it. Don't go down that route if you can help it. It's a pain to put right.
-
I don't have any sites for you either but I do have to weigh in with the people who've already posted.
- The vast majority of people who write front end and even middle tier applications simply don't have the capacity or inclination to write clean, maintainable, high performance SQL.
- Set based development and design takes a much different mindset than procedural based design and the vast majority of front end and middle tier developers have years of experience thinking in terms of procedural based design so it's very difficult for them to think in a way which will allow them to create an efficient architecture when it comes to the back end.
- The increased cost of maintenance of embedded SQL cannot be over stated. I cannot quantify the increased cost because there aren't many, if any, companies which would go for the cost of a study but my experience has shown time and again that the increase is far more than 200%.
A general rule of thumb that my experience has shown me to be true is the best (meaning most cost effective, cheapest to maintain, and highest performance) application design relies heavily on stored procedures, enforcement of referential integrity through primary/foreign keys, and normalization while minimizing triggers and embedded SQL.
-
I agree with all of the negatives posted here.
Only one positive thing that I know of:
A query that is built up in a scripting language can end up being cleaner because you can build the query to return exactly what you want and the order that you want it based on the selectable options in your interface. Otherwise your procedure needs to be more complicated.
As a very simple example:
sql="select ... from table1"
if optDetail then
sql = sql & " inner join table2 on table1.id=table2.id"
end if
sql=sql & " where 1=1"
if param1<> 0 then
sql=sql & " and field1 = " & param1
end if
if param2<> 0 then
sql=sql & " and field2 = " & param2
end if
if param1<> 0 then
sql=sql & " order by field1"
elseif param2 <> 0 then
sql=sql & " order by field2"
end if
if optDescOrder then
sql=sql & " desc"
end if
execute sql
Also, if you have several tables that are similar in structure and you need to perform similar queries on all of them, you can pass in a table name to a procedure which can build up the queries. In SQL you would likely have to write a separate proc for each of them and maintain them all.
We used to build queries like this a lot in the old days partly because we were using Access, but generally our rule now is 'Don't do this anymore. Write it in procs even if it is harder'.
Of course you can build up a query in SQL and then execute it which accomplishes the same thing. We have a few cases of these.
My 3 cents,
Paul
-
There are arguments on both sides of this that are good. If you do some research on the web, you can find convincing arguments for either side, so this will simply be up to you and your developers.
One thing I have noticed is that when suing stored procedures to update data, developers and DBA's alike seem to completely miss the point of ADO and SQL server. We spend money on a database engine that performs well and handles multi-user access for us. Since many developers seem to be putting a select statement in a procedure and then an update in another procedure, they are completely bypassing the SQL locking mechanism. This seems odd to me since ADO is an application layer that does all the work of getting the appropriate cursors and lock objects, holding them correctly, and even help you handle lock conflicts on the resources you are using.
I think a lot of developers overuse stored procedures today because it is easy. I am a dba by trade, but I started out work as a VB / SmallTalk developer so I like to think I see both sides. I am amazed at how many applications I see with a stored procedure that has a single record being updated in a single table and nothing to correctly lock the record so the user does not have something updated between the time they open a form and the time they click the submit button.
-
I am amazed at how many applications I see with a stored procedure that has a single record being updated in a single table and nothing to correctly lock the record so the user does not have something updated between the time they open a form and the time they click the submit button.
I'd really like to see how you would do that without locking the table out from all others. Got a code example?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
>I am amazed at how many applications I see with a stored procedure that has a single record being updated >in a single table and nothing to correctly lock the record so the user does not have something updated >between the time they open a form and the time they click the submit button.
Michael, You should not use ADO in this manner. This is a recipe for disaster. If you need to "lock" a record for business process that is best handled at the APPLICATION layer ie business locking. ie design a locking architecture that handles user level locking for the relavant data elements in a "form".
Database locking is for transactional consistency and to prevent dirty reads etc.
Appropriate use allows scalability.
-
Stored procedures give you :
Performance
Maintainability (You are only passing parameters - not trying to work out how a lengthy command was constructed)
Modularity (the n-tier thing)
Security - you can restrict access to stored procedures only - no direct table access
Security - no risk of injection attacks (depending how and why you are constructing your dynamic SQL)
Negatives :
Possibly slightly more complex implementation (but I would expect negligible)
Your data access code may be more complex because of the need to prepare and pass parameters but this can be encapsulated in a class.
If your application developers and SQL developers are different, there is a risk of inefficiencies creeping in because of a lack of mutual understanding. My view is that application developers always should have a very good database understanding as well.
-
Even if you use stored procedures, you are still embedding SQL (stored procedure calls) into your application.
One thing I've found is that it's important to not have SQL scattered all over your application. You can segregate your SQL (be it stored procedure calls or SQL statements) in a separate module or class, or maybe use a resource file to store your SQL if your development tool supports that. Having SQL scattered all over your application, be it stored proc calls or SQL statements, quickly becomes a maintenance nightmare.
Personally, I prefer to use stored procs where possible (and parameterized SQL where not), but from an application standpoint it's also important to keep your data access centralized/easily accessible.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply