Hi Ryan, Really the best thing to do is get a refresher on SQL. Almost nothing about the client being in node is relevant to performance, and programming well in sql can't really be summed up into a set of handy tips, and more that programming in javascript or c could be. That said, some handy tips :)
1. remember that you are programming in sql. That gets lost pretty easily. 2. basic performance tuning usually comes down to indexing, to prevent your query from having to look at a large number of rows that aren't in your query. So it's not that simpler queries are faster (if it's not indexed, it will have to look at all the rows, no matter how "simple" it is) so much that simpler queries are easier to index. 3. Learn to use EXPLAIN and other analysis tools. It's pretty had to guess about what's making something slow, especially if you are new at it. I get gobsmacked on a regular basis. So you are (probably) on the right track with multiple simpler queries, but it still really comes down to understanding wtf is actually happening. I would also recommend getting in touch with a postgres community, you will get better help there as really you will be having sql questions, not node questions, unless it's specifically a driver issue. Cheers, Ted On Dec 24, 2012, at 7:39 PM, Ryan Schmidt <[email protected]> wrote: > I'm writing a web site with nodejs 0.8, express 3, jade, and a PostgreSQL 9 > database. I had wanted to use a nosql database like mongodb which seemed like > a natural fit, but I have been asked to use postgres because we already run a > postgres server. I've used MySQL years ago but hadn't used postgres and am > now learning about its capabilities. > > For a particular page, I need to pull a lot of little bits of information > from the database: the document itself; information about all the tags > applied to the document; information about all of the document's authors; > information about all of the licenses under which the document is available; > information about a few different types of related documents. At the moment > I'm using a single complicated query which uses views, common table > expressions, sub-selects, array columns... It works, and is fast on my > development machine, but I'm unsure if it's the best strategy or how it will > perform under load. Particularly, I'm worried that as I want to add even more > information to the page, statements like this will become slower and also > more difficult for other fellow developers to understand. I'm already having > to use some constructs that seem weird to me, like "SELECT > array_to_json(ARRAY(SELECT ...)) ...", and then some post-processing in > JavaScript to put the final result object together. > > Is there any common wisdom about whether it would be better / faster / more > efficient to instead issue several smaller queries? I did not initially use > that strategy because I thought it would be best to minimize the number of > queries to avoid unnecessary overhead associated with each query, but now > that I think about it, with node I would be issuing all of the queries at the > about same time, and as the results from the simpler queries come in, I could > already begin processing them, instead of having to wait for the whole > result. And if the database server is multicore then wouldn't several smaller > queries put those cores to better use? > > I guess I'm looking for real world anecdotes and experience from others who > have already built node apps using SQL databases. Thanks and happy holidays. > > > -- > Job Board: http://jobs.nodejs.org/ > Posting guidelines: > https://github.com/joyent/node/wiki/Mailing-List-Posting-Guidelines > You received this message because you are subscribed to the Google > Groups "nodejs" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/nodejs?hl=en?hl=en -- Job Board: http://jobs.nodejs.org/ Posting guidelines: https://github.com/joyent/node/wiki/Mailing-List-Posting-Guidelines You received this message because you are subscribed to the Google Groups "nodejs" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/nodejs?hl=en?hl=en
