MySql vs Sql, Bombay vs Rio
-
I am in a nasty million dollar dispute with a large outsourcer from india over delivery, best practices, on a data intensive, transactional base, data integration project. Because of the delay of delivery, my team developed a solution (Rio, I'm brazilian) in .Net employing state of the art sql backend: stored procedures, partitioned database for text and data (we have large blocks of xml), fine tuned indexes, normalized design, lean and mean.
Our response time is 3 to 8 times faster to extract, modify, consolidate and standardize data in xml for our costumers.
Our oponents develop their solution (Bombay) over open source, in java, relaying in Mysql, Jboss, Jpox, Jms, lucene, etc.
no stored procedures, rare indexes (lots of table scans), oversized logging, no transactional control, etc.
We just received a report stating (in between a bunch of lies) that stored procedures are NOT best practices in modern enviroments, and should be avoided, they have excellent, high level costumers and industry credibility.
I came here to induce you folks to a high level discussion over the subject, and to collect arguments for my response, any comments will be appreciated.
Best
dreeZ
-
as a start,,,
Stored procedures are the only SURE way to guard against SQL Injection attacks.
Stored procedures offer more security as the application only has execution privileges against procs but not data tables.
Stored procs offer better performance. Ask for a shootout stress testing and compare the 2 solutions( if i can be done )
You said [about their solution] : "no transactional control" : not sure what the details are , but sounds scary to have a system without such.
SQL has much better functionality than mySQL such as data partitioning in SQL2005 ( not sure you can use it though as it is available only in some editions of SQL2005.)
SQL2005 comes with better easy to use clustering and mirroring than mySQL. Not sure mySQL has mirroring even.
-
Thanks for reply!
Regarding transaction control, both systems rely on a database queue which divide several requests in tasks, a complete business transaction must have all tasks complete otherwise is incorrect and will make us (or our costumers) liable.
And they don't use sprocs, and I could not see, (I will investigate further) the use of sql transactions to commit or rollback a transaction as a whole.
again, thanks for the reply
-
One or two considerations from the system side. It will be easier and more secure to implement a sql 2005 solution in a Windows environment. Permissions granularity can be done with Windows Groups. Authentication can be much better controled, if data is sensitive and so on. All that with Windows on board capability.
For large and intensive databases you can implement a 64-bit (OS and SQL) solution. A cluster or mirroring can give the necessary avability.
-
My view is that if your 'opponents' have produced an Expert's report stating a particular view, then your organisation will need to commission an Expert report also. Your responsibility should be to show to your management that an alternative view to the opponent's report is possible, and convince them to commission the appropriate Expert to write the report. If your dispute ever gets to court and the only Expert testimony comes from your opponent, the court is likely to find in their favour.
You should also look at the credentials and past history of the Expert your opponent has commissioned. Try to find other reports they have produced, either as court documents or in the public domain. If you think their view that stored procs are deprecated in best practice applications is wrong, you need documented evidence to challenge their credibility. If your opponent's expert has a long history of publications supporting this view, then making the challenge will be harder. If it can be shown their report appears to justify your opponent's application design and does not present an impartial view of best practice, then your challenge is easier. Finally, I am only a DBA - if you want meaningful legal advice you need to talk to qualified legal people.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Since when are stored procedures not a best practice in "modern environments"? I would have to ask what they consider to be their "environment". Is it the environment where you want your application to run with the best performance and robustness as possible, or the environment where they want to pimp out the services of their Java developers writing generic code without having to worry about system-specific features and database tuning?
-
The real issue is why none of this was listed in the specifications and requirements documents. This should have been laid out in advance, then there would be no question what the delivery platform was supposed to be...
-
even mysql says that stored procs are useful, faster and more secure...they also say their implementation of stored procedures is not yet complete...
for an expert to contradict the database provider makes the expert seem a bit suspect.
http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Guys, thank you all so much for the responses, I have enough substance here to help us out with our endeavour. We just found out they made a major mistake in a sign off which will take them out of the picture easier.
I really appreciated all your help
Best,
dreeZ
-
dreeZ,
First, I'll order a pinga two fingers deep and a big helping of feijoada! Que saudades do Brasil!
Second, why wasn't the environment and platforms specified in the Spec Documents. That would have eliminated the headache and you would have at least an apples to apples comparison.
Third, my teams in India/Russia will often do this and I just roll my eyes at their recommendation that Stored Procedures are not standard--and then we have a very long discussion about the business needs.
In many cases, I pull out my SQL books and a few articles for both Oracle and SQL that show otherwise. Most of the articles are from this site and http://www.sql-server-performance.com/ Also books like Ken Englands Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook. Also, our CTO used to work for Oracle and he has the team there do Stored Procedures in most cases in Oracle. If worse comes to worse, I create a few examples on very large datasets that shows them how it is faster.
Basic logic seems to come into play here. What does a DB server need to do when it receives T-SQL to query the DB? First it must parse the query to make sure it is valid, then optimize the T-SQL, then compile it and finally execute it. When you use a stored procedure, it checks the cache and if it finds it executes it as the others steps are already done. Four steps versus two. My bet is on the one with fewer steps to execute faster and with SQL Server, Profiler proves my point.
Also, this Stored Procedure thing isn't cut and dried--it's kind of like the arguements about religion. My mantra is to use the platform and it's advantages/specialities to produce the fastest possible application. In SQL Server's case, stored procs are the way to go 95% of the time....As for MySQL, I have not idea as I don't know much about it and don't consider it ready for prime time just yet. Oracle and DB2 will both benefit from stored procedures in most cases as well.
Fourth, Rob Howard has a interesting discussion of some of these topics and arguments at http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx
Fifth, what happens if the MYSQL was tuned properly with indexes so that table scans are avoided. How does your performance compare then? Seems as if you are comparing a tuned system (yours and kudos for doing that as I don't often see it) to an untuned system.
Sixth, if they aren't using Stored Procedures, how are they controlling security to the data, preventing SQL Injection, handling transactions so that the data remains in an ACID state and prevent orphans, preventing deadlocks...and a whole host of other things. . I hope that is done via the business logic layer.
Hope this helps!
SJ
-
Just a couple of thoughts:
1. You might challenge your competitor to sign a service level agreement (SLA), detailing the minimal acceptable levels of application/database performance.
2. I've used both SQL Server 2000/2005 and MySQL. Don't discount MySQL's capabilities; it's a very stable, fast, scalable DBMS. The biggest problem I see with MySQL is that its feature set usually seems to lag SQL Server's by a few years -- MySQL only recently introduced triggers,for example, and the last I knew, XML support was "planned for a future release".
I'll conclude by saying that I'm simply astonished by the incorrect and inaccurate representations your competitor has made. Some people will do anything for money.
-
I don't understand what the anit-stored procedure argument is ! Some issues can be argued one way or another, and both have merits. But what are the merits of not using SPs ? Do they give a list of "cons" for SPs ?
-
In my experience, most people arguing against stored procedures in general are trying to mask their own incompetence with writing, debugging, deploying, securing, and/or maintaining stored procedures. Or it is a turf war where the developers are trying to control the entire code base and don't want to work with a DBA. Maybe they don't want to hear the DBA tell them what a crappy design they've come up with. There are specific cases where stored procedures may not be the best solution, but to argue that they shouldn't be used at all is ridiculous.
I don't think that all DBAs are geniuses, but I am suspicious of developers (with lousy non-indexed databases) using bogus arguments about why the world would be better if you leave everything to them. Would they listen to a DBA tell them how much faster their programs would run if they quit using all those resource-hogging objects and strings, and went back to good old FORTRAN? No dynamic memory allocation, garbage collection, or other nonsense, just pure performance! (When they say "You've got to be kidding!", you can always answer "Well, you started it!")
Accepting all invitesViewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply