On Tue, Sep 21, 2010 at 10:00 AM, Ian Booth <[email protected]> wrote: > Hi > > In keeping with the theme of Performance Tuesday, I thought I'd ask a > question about a particular implementation aspect since it potentially > relates to performance. > > My question is: we don't appear to be using SQL prepared statements when > accessing the database; why is this? Given the amount of SQL being > executed, there's potentially a significant performance gain to be had, > not to mention the reduced potential for SQL injection type attacks on > the system. Having come from an Oracle background, using prepared > statements is a no brainer for me. Perhaps there's a Python/Postgres > issue I'm not aware of? > > Thanks for any insight.
I don't think we've ever had Launchpad tuned to the point that this would be a significant win: we're looking at multi second queries needing improving, not 10's of ms ones. I agree that they are a no brainer win, but I suspect we'd need some infrastructure work in storm to bring them in. If you, or someone else wants to work on them in the near term, brilliant. In the medium to long term, there are a few considerations to bear in mind. Firstly, once we have some breathing space on performance, we're going to be reevaluating our DB stack : we need things like automatic failover and write scaling that are absent today. One possible outcome there is a move away from postgresql (or even SQL per se) in which case investment in storm & stored procedures may be a dead end. I don't have any feeling for how likely that is yet: it would be a huge effort to move and theres lots of things to consider in evaluations in that area. If we stay on SQL, then we're going to get performance down to the point that optimising a few ms here and there starts to matter a great deal: and in those cases working on prepared statements is going to go into the regular kanban queues at such a time. Of course, we probably need to be on cython / pypy / jython / or some such at that point too. -Rob _______________________________________________ Mailing list: https://launchpad.net/~launchpad-dev Post to : [email protected] Unsubscribe : https://launchpad.net/~launchpad-dev More help : https://help.launchpad.net/ListHelp
