WEB & DB Sandbox
-
I thought I would express one of my db/web peeves here. The topic is SQL related but not directly.
In the past year or so I have been working as a DB consultant and find that most performance issues are results of web programmers writing native SQL statements in pages. Prior to being a consultant I worked with a firm that wrote all SQL commands in stored Proc for Web pages. But that firm went over the edge with dynamic SQL in Stored Proc which like Native SQL strings does not take advantage of SQLServers ablility to store and retrieve data ie Query Plans / Indexes. Where is the happy medium?
In My humble opinion most Web Developers achieve a basic level of SQL while excelling rapidly in Web technology. The database is behind the curtain leaving the DBA to determine what actions are being called against the database resources. Mainly when proformance is less then optimal and the contract funds are close to being expensed.
True, it is possible for the DBA to lock security so that Web heads can't perform this type of activity but realistically the DBA resources are limited and so once access is establish for developement the SQL string nightmare begins.
When ask to re-direct development efforts towards a happier place the resistance is a double-sword.
Modifications for example.
WEB side. - Promoting a SQL change to the web page is cake then altering a Stored Proc.
DB Side. - Altering a query syntax is easier then posting a new page.
Reseaching trouble areas-
Web side - Reading a SQL statement is easy in web code.
DB side - Determining the proper SQL statement being called requires Web programmer assistance.
Web side -Changing a Page to use Stored proc is painstaking and time consuming.
DB Side -Well why didn't it start with a Stored proc and then we wouldn't be in this mess.
I'm I being crazy here? Is it a control thing? I thought if you needed a database object you would ask the DBA to create one. What realistic techiques and protocols for these areas of technology? They need each other to work well.
In practice CIO, CTO or PM end up being the tie breakers but this only pour salt on the wound a place no one needs to go. In most cases neither of these levels can provide the optimal decision.
Looking forward to you thoughts/ideas
-
I've been told I generalize too much, though some of it is in fun. I've also faced many developers who have a basic knowledge of SQL and avoid using or learning anything more. As Andy's taught me, they need to be instructed how to do things easier. There's a few ways to do this.
First, calling a stored proc or a query is the same. You learn it once and then cut and paste the same code, so that's a ridiculous arguement. Work with them to learn how to make the call and then leave it at that.
Second, all good programming techniques, whether procedural or OOP use abstraction in the form or functions/methods. Your web developers probably do this with Javascript, so let them know a stored proc is the same thing. Plus it gives you the abstraction to make performance mods without their altering their code. If you vertically split a table, their code doesn't change with a stored proc. Or if you add an index hint or something similar, you can make the proc change without them changing code.
Third, make it easy for them. Give them local copies of the db to code queries against if they don't want to write a proc, then let them send to you and you can compile the procs on the server. That way you can get the work done quickly without being a bottleneck and go back and look for performance optimizations later. You can also see if two people are writing the same or very similar queries and can share a proc.
-
I agreed there are a lot of web site developers not knowing SQL, maybe they know the basic 'SELECT * FROM Table', 'INSERT TABLEA...', 'UPDATE TABLE A...', 'DELETE TABLEA...'.
The DBA in my old company always wanted them to use stored procedure so it was more effective but since there were two different groups, the web group would never listen to the DBA group. Even designing the database, the web group would design something totally horrible and implement it, by the time the DBA reviewed it, it was too late since it had to put in production.
This was the current corp culture. One group does not know other group is doing. At least the upper management does not encourage them to do so.
I would say it is a 'Poor management' problem. Also as long as 'SELECT * FROM Table' is working, no one cares.
my 2 cents.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply