Sql – Does ORM for social networking sites makes any sense

language-agnosticormsocial-networkingsqlweb-frameworks

The reason why I ask this is because I need to know whether not using ORM for a social networking site makes any sense at all.

My argument why ORM does not fit into social networking sites are:

Social networking sites are not a product, thus you don't need to support multiple database. You know what database to use, and you most likely won't change it every now and then.
Social networking sites requires many-to-many relationship between users, and in the end sometimes you will need to write plain SQL to get those relations. The value of ORM is thus decreased again.
Related to the previous point, ORM sometimes do multiple queries in the backend to fetch its record, which sometimes may be inefficient and may cause bottleneck in the database. In the end you have to write down plain SQL query. If we know we are going to write plain SQL anyway, what is the point using ORM?

This is my limited understanding based on my limited experience. What are you're experience with building a social networking sites? Are my points valid? Is it lame to use bare SQL without worrying about using ORM? What are the points where ORM may help in building a social networking sites?

Best Answer

The value of using an ORM is to help speed up development, by automating the tedious work of assigning query results to object fields, and tracking changes to object fields so you can save them to the database. Hence the term Object-Relational Mapping.

An ORM has little value for you regarding database portability, since you only use the one database you deploy on.

The runtime performance aspect of an ORM is no better than, and typically much worse than writing plain SQL yourself. The generic methods of query generation often make naive mistakes and result in redundant queries, as you have mentioned. Again, the benefit is in development time, not runtime efficiency.

Using an ORM versus not using an ORM doesn't seem to make a huge difference for scalability. Other techniques with more bang-for-the-buck for scalability include:

Managing indexes in the RDBMS. Improve as many algorithms as possible from O(n) to O(log₂n).
Intelligent caching architecture.
Horizontal scaling by database partitioning/sharding.
Database load-balancing and replication. Read from slave databases where possible, and write to a single master database. Index slaves and masters differently.
Supplement the RDBMS with complementary technology, such as Sphinx Search.
Vertical scaling by throwing hardware at the problem. Jeff Atwood has commented about this on the StackOverflow podcast.

Some people advocate moving your data management to a distributed architecture using cloud computing or distributed non-relational databases. This is probably not necessary until you get a very large number of users. Once you grow to a certain level of magnitude, all the rules change and you probably can't use an RDBMS anyway. But unless you are the data architect at Yahoo or Facebook or LinkedIn, don't worry about it -- cloud computing is over-hyped.

There's a common wisdom that the database is always the bottleneck in web apps, but there's also a case that improving efficiency on the front-end is at least as important. Cf. books by Steve Souders.

Julia Lerman in Programming Entity Framework (2009), p.503 shows that there's a 220% increase in query execution cost between using a DataReader directly and using Microsoft’s LINQ to Entities.

Also see Jeff Atwood's post on All Abstractions are Failed Abstractions, where he shows that using LINQ is at least double the cost of using plain SQL even in a naive way.

Related Solutions

What’s the best strategy for unit-testing database-driven applications

I've actually used your first approach with quite some success, but in a slightly different ways that I think would solve some of your problems:

Keep the entire schema and scripts for creating it in source control so that anyone can create the current database schema after a check out. In addition, keep sample data in data files that get loaded by part of the build process. As you discover data that causes errors, add it to your sample data to check that errors don't re-emerge.
Use a continuous integration server to build the database schema, load the sample data, and run tests. This is how we keep our test database in sync (rebuilding it at every test run). Though this requires that the CI server have access and ownership of its own dedicated database instance, I say that having our db schema built 3 times a day has dramatically helped find errors that probably would not have been found till just before delivery (if not later). I can't say that I rebuild the schema before every commit. Does anybody? With this approach you won't have to (well maybe we should, but its not a big deal if someone forgets).
For my group, user input is done at the application level (not db) so this is tested via standard unit tests.

Loading Production Database Copy:
This was the approach that was used at my last job. It was a huge pain cause of a couple of issues:

The copy would get out of date from the production version
Changes would be made to the copy's schema and wouldn't get propagated to the production systems. At this point we'd have diverging schemas. Not fun.

Mocking Database Server:
We also do this at my current job. After every commit we execute unit tests against the application code that have mock db accessors injected. Then three times a day we execute the full db build described above. I definitely recommend both approaches.

Sql – Simulating group_concat MySQL function in Microsoft SQL Server 2005

No REAL easy way to do this. Lots of ideas out there, though.

Best one I've found:

SELECT table_name, LEFT(column_names , LEN(column_names )-1) AS column_names
FROM information_schema.columns AS extern
CROSS APPLY
(
    SELECT column_name + ','
    FROM information_schema.columns AS intern
    WHERE extern.table_name = intern.table_name
    FOR XML PATH('')
) pre_trimmed (column_names)
GROUP BY table_name, column_names;

Or a version that works correctly if the data might contain characters such as <

WITH extern
     AS (SELECT DISTINCT table_name
         FROM   INFORMATION_SCHEMA.COLUMNS)
SELECT table_name,
       LEFT(y.column_names, LEN(y.column_names) - 1) AS column_names
FROM   extern
       CROSS APPLY (SELECT column_name + ','
                    FROM   INFORMATION_SCHEMA.COLUMNS AS intern
                    WHERE  extern.table_name = intern.table_name
                    FOR XML PATH(''), TYPE) x (column_names)
       CROSS APPLY (SELECT x.column_names.value('.', 'NVARCHAR(MAX)')) y(column_names)

Best Answer

Related Solutions

What’s the best strategy for unit-testing database-driven applications

Sql – Simulating group_concat MySQL function in Microsoft SQL Server 2005

Related Topic