Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Re^2: perl & SQL best practices

by JavaFan (Canon)
on Apr 27, 2012 at 22:38 UTC ( #967731=note: print w/ replies, xml ) Need Help??


in reply to Re: perl & SQL best practices
in thread perl & SQL best practices

by all means, do table joins on the database side, for example
By all means? Joins are costly, and considering there typically are more clients than servers, there are a lot of situations were it makes sense to do the join on the client -- there's a lot more client CPU available than server CPU. In other cases, it does make sense to do it on the database side.

Answers like "by all means" are fundamentally wrong.

Make use of uniqueness constraints, normalize the schema as well as you can.
Again, the answer is, it depends. One of the steps to rise above grasshopper level is to know what normalization is, when it's useful, and, as importantly, when it makes sense to not normalize. If you're a bank, you may care more about avoiding data redundancy than when you're serving many million customers on a daily bases, and page delivery time is essential.


Comment on Re^2: perl & SQL best practices
Re^3: perl & SQL best practices (joins)
by tye (Cardinal) on Apr 27, 2012 at 23:33 UTC

    By all means, start out doing table joins inside the DB. I have relatively rarely seen a situation develop where the schema vs. the needed data (and how often and how quickly) ends up showing how you've painted yourself into a bit of a corner and you can work around the problem by doing some joins in the client.

    Much of the time, this is also about limitations or quirks of the query optimizer and how much time / expertise you can throw at recrafting the query to convince the optimizer to do the efficient thing.

    Frankly, I've more often seen client-side joins done because the people working on the project rather sucked at database performance issues. Or even because the team just believed that "joins are expensive". But when I've actually worked on the performance issue that lead to client-side joins, there was a much more efficient solution that didn't involve client-side joins.

    So, I have done client-side joins for performance reasons. But only very rarely and always due to things that had better resolutions in the long run. It can be a powerful work-around.

    I've never seen client-side joins done at the outset for good reasons. So, yes, start out with server-side joins, by all means.

    - tye        

Re^3: perl & SQL best practices
by doom (Deacon) on Apr 30, 2012 at 18:57 UTC

    Joins are costly, and considering there typically are more clients than servers, there are a lot of situations were it makes sense to do the join on the client -- there's a lot more client CPU available than server CPU.
    Try another rule of thumb: the network is slow. Moving more data from server to client than you need to is rarely a good idea. First guess-- albeit not the last word-- is to use the features built into the database to crunch the data.

    And if a database join seems "expensive" to you, I suggest you need to look at your indexes.

      Try another rule of thumb: the network is slow. Moving more data from server to client than you need to is rarely a good idea. First guess-- albeit not the last word-- is to use the features built into the database to crunch the data.
      I will repeat myself, but I fear I might as well talk to the walls: it all depends. Measure, and you will know. Don't assume the network is slow -- and that hence you should overload your database server as much as possible. Slowing everyone else down, so your query gets done "at the server" may not the best solution.

      Measure. Don't assume.

      Measure. Don't assume.

      And once you have implemented a solution because it's the best today, measure the next day again and the day after, and keep repeating it, because circumstances change.

        That's right, you've got to measure everything, everything! When I design a system I always write the code every possible way and then benchmark each of them against each other before roll-out.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://967731]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (4)
As of 2014-08-31 03:40 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (294 votes), past polls