Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Discussion: is e-Commerce making a mockery of SQL Server? Expand / Collapse
Author
Message
Posted Monday, July 30, 2012 5:44 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 7:41 PM
Points: 461, Visits: 1,266
Some of you may have heard of my latest tales of woe, in that I am in an environment that has Grails on the front end, along with MongoDB, MySQL, Redis, and Red Hat, and all of this is going in favor of Microsoft SQL Server. Assuming the obvious reasons are not the motivator (i.e. SQL licensing costs) I was dumbfounded to learn that premature optimization rules an iron fist in their ecommerce scheme. Where Foreign Keys are so greatly disliked for the sake of speed, that a 91 table database might have about 15 tables interconnected - and that is it. Now - that might be all that is needed, but when you factor in that any OLE DB / ODBC / JDBC device can now connect to this database - you have data integrity issues galore.

I thought perhaps I was in an isolated nuthouse, but a recent conversation that I had with a fairly well respected DBA out here on the West Coast has proven that this is becoming a more common practice in e-Commerce scenarios (where speed is the key de jour, and data integrity? Who needs to worry about that...).

Are we missing something here in the Microsoft SQL Server Community that is becoming a new way of doing business, or are people in the Western US who are using SQL Server for e-Commerce just nuts (where first to market on the latest e-Commerce idea means more to them than the security of the data they display).

I would love for all of us to sound off on this one...
Post #1337625
Posted Monday, July 30, 2012 8:10 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 3,081, Visits: 11,235
I'm really not sure exactly what you want to discuss.

There are plenty of people that create denormalized databases and claim they did it for speed, and not just in e-Commerce applications.

Usually, the answer is simple: they just didn't know how to create a normalized database, so they claim they did it for performance.



Post #1337632
Posted Monday, July 30, 2012 8:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:22 PM
Points: 36,016, Visits: 30,308
SQL_ME_RICH (7/30/2012)
Some of you may have heard of my latest tales of woe, in that I am in an environment that has Grails on the front end, along with MongoDB, MySQL, Redis, and Red Hat, and all of this is going in favor of Microsoft SQL Server. Assuming the obvious reasons are not the motivator (i.e. SQL licensing costs) I was dumbfounded to learn that premature optimization rules an iron fist in their ecommerce scheme. Where Foreign Keys are so greatly disliked for the sake of speed, that a 91 table database might have about 15 tables interconnected - and that is it. Now - that might be all that is needed, but when you factor in that any OLE DB / ODBC / JDBC device can now connect to this database - you have data integrity issues galore.

I thought perhaps I was in an isolated nuthouse, but a recent conversation that I had with a fairly well respected DBA out here on the West Coast has proven that this is becoming a more common practice in e-Commerce scenarios (where speed is the key de jour, and data integrity? Who needs to worry about that...).

Are we missing something here in the Microsoft SQL Server Community that is becoming a new way of doing business, or are people in the Western US who are using SQL Server for e-Commerce just nuts (where first to market on the latest e-Commerce idea means more to them than the security of the data they display).

I would love for all of us to sound off on this one...


The insanity isn't relegated to just the West coast. It's here in the Mid-West, as well. I worked with a supposed "master" developer who knew all of the tools. He used those tools to design a couple of tables. All of the INT columns were BIGINT and all of the character based columns, even if meant to hold only 1 character, where all NVARCHAR(4000). When I asked him why, his answer was "Premature Optimization is the Root of all Evil."

The statement "Premature Optimization is the Root of all Evil" was never meant to justify such a garbage design. Gail Shaw teaches the subject and I hope she shows up on this thread because she does a much better job than I at explaining what it really means.


--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1337634
Posted Monday, July 30, 2012 8:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:05 PM
Points: 20,485, Visits: 14,144
Michael Valentine Jones (7/30/2012)
I'm really not sure exactly what you want to discuss.

There are plenty of people that create denormalized databases and claim they did it for speed, and not just in e-Commerce applications.

Usually, the answer is simple: they just didn't know how to create a normalized database, so they claim they did it for performance.





This is far too common. And every time I have seen it, the argument was the same - for performance. Refactoring the database to demonstrate the error of their ways has shown no issues with performance and that they just did not understand good db design.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1337637
Posted Tuesday, July 31, 2012 6:53 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 3,081, Visits: 11,235
SQLRNNR (7/30/2012)
Michael Valentine Jones (7/30/2012)
I'm really not sure exactly what you want to discuss.

There are plenty of people that create denormalized databases and claim they did it for speed, and not just in e-Commerce applications.

Usually, the answer is simple: they just didn't know how to create a normalized database, so they claim they did it for performance.





This is far too common. And every time I have seen it, the argument was the same - for performance. Refactoring the database to demonstrate the error of their ways has shown no issues with performance and that they just did not understand good db design.



The claim of having denormalized for performance is usually easy to shoot down. Just ask to see the normalized model that they denormalized from.


Post #1337866
Posted Tuesday, July 31, 2012 8:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:42 PM
Points: 14,840, Visits: 27,315
I worked on a project for about three years (it was supposed to take 18 months) that still wasn't done when I left the company. They too had jumped on the premature optimization band wagon taking it to mean, as many do, that you don't have to design anything, ever. They primarily used nHibernate as the means of ignoring the database (instead of ORM, they made it into OOM, Object-to-Object-Mapping). It completely eliminated the database and DBAs from their development process...

Now, 4.5 years into the project (again, it was only going to be 18 months long, eliminating the DBAs is part of what makes it so much faster to deliver...) they still haven't delivered and they're finding HUGE performance issues. Plus, here's a shocker, people actually expect to be able to run reports on the data, but since it's stored as objects, you can't easily cut across it. They are throwing money at the problem currently. They've implemented Availability Groups to create a read-only mirror of the database so that they can remove reporting from the production system, but that didn't fix the performance issues. Now, they're working on a full-blown database design and will build a load process to go from the object model to a relational model, but only for reporting. It's a nightmare. What's more, it's a nightmare that I predicted, in a detailed report, written 4.5 years ago when the project started.

You're not alone.

What I'd say is happening is that developers seem to be more and more frustrated with the persistence of data. Combine this with a lack of understanding of data systems and you get ORM tools, No-SQL movements, pre-mature optimization as a design paradigm, etc.. It's all an attempt to bypass the thing that is hard for them. Unfortunately for all these guys, the core mechanisms of relational storage, either in OLTP normalized systems or Star schema's or whatever, still works. But it only works when you do it correctly.

By the way, I'm not against ORM tools. I think they're great. But you still need a database design. I'm not against MongoDB or any of the other NoSQL databases, but when you have to do reporting against them, you're going to relational storage again. The simple fact is, the relational model is a good one and nothing has yet come along that will completely replace it. Not yet.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1337919
Posted Tuesday, July 31, 2012 12:48 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 7:41 PM
Points: 461, Visits: 1,266
I really do appreciate you all sounding off here. It really rattled me earlier this year when I was told that the place I worked at didn't like the Referential Integrity down at the database level. When I heard this in the way of someone else saying "yeah - that's good" (in reference to their being no RF in the database), I was starting to think I was being left in the dark in understanding why SQL was being used this way.

The all too common response is as you have all pointed out - we need speed, and the engine doesn't have to worry about looking at RF when we do it up in the app.

My response to this is often with a smile and a nod, but inside I am just fuming with frustration over a mentality that is prevailing in today's e-Commerce (and other) markets.

SQL Server (from what I have studied) was not meant to be compromised in this way, but if people believe that they will lose overall query speed for their app by putting an appropriate design in place for RF - what can you do?

Post #1338103
Posted Tuesday, July 31, 2012 1:16 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 10:04 AM
Points: 15,442, Visits: 9,590
I too have seen all kinds of justifications for various data-nightmare scenarios.

One place I worked, they were proud of the fact that there were no delete statements ever issued to any of their databases, and the only updates were to mark a "Current" column 0 instead of 1. Everything else was inserts.

They were completely positive they had this virtual delete/update environment fully in-hand and perfected. When I started there, I found that they had thousands of "ID" values that had as many as thousands of rows marked "Current", in violation of their model (supposed to only ever be one Current/"ID"), and no way to tell which one was correct in many cases.

Every report they had given to management about the business, for over 2 years, had been egregiously wrong, in terms of minor things like sales volume, customer retention, inventory, and everything else.

All referential integrity was handled in the front end, because the fake ID values they used prevented having FKs in the database. They had tens of thousands of rows of orphan data, which were again falsifying reports, graphs, etc.

When I found this, the solution the manager responsible for those systems came up with was fire me and hope nobody ever found out.

Fun times.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1338125
Posted Tuesday, July 31, 2012 1:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 3:41 PM
Points: 5,986, Visits: 6,932
Ow G2, that SUCKS. Been there done that though. However, my version of that report got every consultant let go because they couldn't 'let go' normal employees while temps worked in their department.

I'm not the biggest fan of Foreign Keys, personally. They can be a royal PITA when trying to do bulk work against a database and can slow things down just an extra hair. That said, there's a huge difference between 'Do we need a Foreign Key here?' and 'NO FOREIGN KEYS EVAH!'

Gyeah, I'd like to take ORM out in the back yard and Ol' Yeller it some days when I hear these horror stories. Got a few of my own at the moment but they're from a different app vs. DB mindset.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1338136
Posted Tuesday, July 31, 2012 2:04 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:58 AM
Points: 41,572, Visits: 34,497
Premature optimisation is the process of making design decisions or modifications based on assumed performance improvement without testing, benchmarking and verifying the change.

"Foreign keys are slow so I won't create them" is in fact an example of premature optimisation. (unless someone has sat down, done the measurements and found that in this particular case the foreign keys are unacceptably slow)
"I'm denormalising for performance" is another common case of premature optimisation, someone has assumes that normalised designs are slow and denormalised without testing that assumption (at least I've never encountered a case where they did test).

Optimising queries or tweaking data models or playing with hints or indexes without any measurements and without any idea of the results of the fiddling is premature optimisation

Creating a good, solid, scalable design is not premature optimisation. It's doing a good #@$@# job.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1338160
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse