Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am starting working on a web project using django. While researching whether to use Sqlalchemy or raw sql when django orm is not sufficient which is also a question I asked here Raw Sql vs SqlAlchemy when Django ORM is not enough

One question bugged me.

As the load on the website increases and we find out that sql queries ran by ORM needs tuning. But we have no control on how does an ORM executes queries. In that case we have to use raw sql queries because they give us the best control.

But if we start using raw sql queries, all the benefits of using an ORM in first place are gone. We are stuck with both sql and orm code which will surely mess up the reading and maintainability of the code.

It feels to me that we are opting for easy code in lieu of many problems later. No doubt our initial code will be fast and easy maintainable but this can cause later problems.

I would like to know thoughts of others on it. I am not starting a question comparing orms and sql but I would like to know what are the options when we have created web app using ORM and comes the situation when its clear that ORM is not supporting our cause?

share|improve this question
    
Can your ORM map native queries to entities? Because Hibernate for example can do this and it can be used to resolve such troubles. Also how do you encapsulate data access? Do you use DAOs or respositories? They can nicely hide such implementation issues. –  Falcon Sep 26 '11 at 9:40
    
Actually I dont know. I am working first time on any ORM and hence my question. Can u tell me what are the things I should check about an ORM to further know its usefulness to me? –  codecool Sep 26 '11 at 9:46
2  
SQLAlchemy does all you need, see the features list. It can map raw queries to entities (see Raw SQL statement mapping). Just use this feature and encapsulate the implementation somewhere and your troubles are gone. –  Falcon Sep 26 '11 at 9:49
    
thnx for the info :) –  codecool Sep 26 '11 at 9:57

4 Answers 4

up vote 4 down vote accepted

This is pretty straightforward. Two solutions :

  1. Tune the ORM. This is the application of separation of concerns and the cleaner solution in the long run. Anyway this has drawbacks.
    • The ORM codebase could be hard to tweak.
    • This could be hard to achieve in a given deadline.
    • The tweak has to be ported to other versions of the ORM, or continuous obolescance will arise pretty quickly. Working with people behind the ORM is the way to go, but some company policies can prevent it.
    • The ORM can be impossible to modify that way. It can be a proprietary software or the company organisation could prevent you from doing this.
  2. Use manually done queries mapped by the ORM.
    • This is easy and fast to do. get the job done.
    • Has to be unique, or you codebase will be quickly doomed with such hacks everywhere.
    • Has to be packed in an abstraction. Make it look like standard use of the ORM. This limits the dirtyness to a small portion of code.
    • The ORM may not provide this function (and if it the case, you probably choosed a crappy one).

Depending on the conext and deadlines, solution 2 is a real world solution, even if it not clean. Technical debt can be made in a project with precaution to limit it to a small portion of code and justified by some extra technical constraints.

share|improve this answer
    
What do u mean by has to be unique point? I did not get that –  codecool Sep 26 '11 at 10:08
    
I means put the code that do the dirtyness in a single place, and call it where you need it instead of spreading this all over the codebvase. –  deadalnix Sep 26 '11 at 10:22

I really like and agree with deadalnix's answer so I won't bother repeating his points, but one more possible option that I have done in the past was to use a tuned views.

I normally don't like the use of views but if a performance or database intensive read operation should be done in a specific way and the ORM can't be tweaked for this it can be easier to write raw tuned SQL for a view and map a read-only ORM entity to that.

With that being said I view it as a quick solution and a more robust solution would likely be something like SQLAlchemy that was mentioned above.

share|improve this answer

If your ORM does not allow you to change the SQL, may be you can tweak the SQL by using stored procedures. Also, make sure the problem is in the SQL, the performance can suffer because your table design or your indexing strategy. Those are outside the ORM scope, and your DBA should be able to figure out what to do.

share|improve this answer

The first place I would start is by better tuning the database to handle the queries -- add indexes before changing code, it is shocking what they can do for you.

If you do need to go the custom SQL route, use the orms custom SQL facility if it has one. I don't think using custom SQL here and there removes all advantages of the orm, especially if it stays within the data layer.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.