As a side note, there is actually a book on design patterns in SQL, although I personally haven't read it. From the reviews I recall reading about it, I think its mostly based on Oracle Features. Still might be a good read as far as PostgreSQL is concerned except for the sections on Graphs and recursive trees since Oracle has special syntactical sugar for that kind of stuff that is unique to Oracle. http://www.rampant-books.com/book_2006_1_sql_coding_styles.htm Hope that helps, Regina -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Ted Byers Sent: Tuesday, December 04, 2007 9:59 AM To: Richard Huxton; Ivan Sergio Borgonovo Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: stored procedures and dynamic queries --- Richard Huxton <dev@xxxxxxxxxxxx> wrote: > Ivan Sergio Borgonovo wrote: > > On Tue, 04 Dec 2007 08:14:56 +0000 > > Richard Huxton <dev@xxxxxxxxxxxx> wrote: > > > >> Unless it's an obvious decision (millions of > small identical > >> queries vs. occasional large complex ones) then > you'll have to > >> test. That's going to be true of any decision > like this on any > >> system. > > > > :( > > > > I'm trying to grasp a general idea from the view > point of a developer > > rather than a sysadmin. At this moment I'm not > interested in > > optimisation, I'm interested in understanding the > trade off of > > certain decisions in the face of a cleaner > interface. > > Always go for the cleaner design. If it turns out > that isn't fast > enough, *then* start worrying about having a bad but > faster design. > I don't know about "clean", but the mantra here is "First you make it provably correct, then you make it fast." I am a fan of making things as simple as practicable, BUT NO SIMPLER. I don't know how that meshes with "clean", but the general idea is right. If you look at my code, my C++ and my java code is infinitely better than my SQL, so with either of those, I end up making fewer variants, and my starting point is generally much closer to my end point, and that is just a function of experience. With my SQL code, I generally find myself producing a variety of scripts to support a given task. And these include exploration of just about everything SQL has to offer. I TRY everything, from joins to correlated subqueries to stored procedures to views, and beyond. And I am still trying to develop an intuition as to which options will serve best in a given situation, analogous to design patterns I routinely use in my C++ and Java code. Your reaction to Richard's advice to test seems to imply you want a more direct approach to "THE" answer. That is chasing something that doesn't exist! I tell you, as a developer (and NOT a sysadmin), there is no substitute for experience and THAT can ONLY be obtained through trial and error. I haven't seen any books about design patterns in SQL (while there are plenty in C++ and Java, and other languages), so there is no short cut. In short, the general idea most developers I know work with is "code and test, and then code again and test again, until you've tried the variety of options that exist." There is no substitute for testing yourself. I have seen repeated advice to replace correlated subqueries by left joins, claiming that ubiquitous experience is that the joins are faster, and yet, in my tests, in most cases there was little difference in speed while in others the correlated subqueries were faster. So it appears the advice was based on experience with dated software and the RDBMS in question had subsequently dramatically improved how it handled correlated subqueries. And my use of EXPLAIN confirmed what I was finding through testing. > > Most of the documents available are from a > sysadmin point of view. > > That makes me think that unless I write terrible > SQL it won't make a > > big difference and the first place I'll have to > look at if the > > application need to run faster is pg config. > > The whole point of a RDBMS is so that you don't have > to worry about > this. If you have to start tweaking the fine details > of these things, > then that's a point where the RDBMS has reached its > limits. In a perfect > world you wouldn't need to configure PG either, but > it's not that clever > I'm afraid. > I am not sure I buy this, if I properly understand it. Trust me, I have written some really bad but simple queries that took hours to complete a task that was completed in less than a minute with smarter code. And yet the "bad" code I'd written was similar in nature to examples used in some texts to explain ideas in SQL. The point is, until you get extensive experience in SQL programming and optimization, you won't know what is bad code until you test it. Personally, I rely on the sysadmin to administer the RDBMS properly, to ensure it is configured appropriately for our application, AND I ask his or her advice and input on how I design and implement my SQL code, as well as for input on distributed application architecture. You can't do it all. On my development machine, I just use whatever the default configuration is, so I have it up and running in a flash and can focus on my development. I'll change that configuration ONLY if the sysadmin tells me there is a problem with the default. My advice, therefore is forget about configuration issues and focus on getting your SQL right, and then fast, and let your sysadmin advise, and possibly help, with changes to your configuration should he or she feel it needs to be modified to better represent how your application will behave once in production. > Keep your database design clean, likewise with your > queries, consider > whether you can cache certain results and get > everything working first. > Richard, could you possibly clarify what you mean by a "clean design"? Is it different from what I normally do with regard to ensuring all the requisite data is available, properly normalized, with a suitable suite of indeces, keys, &c., and as simple as practicable? (That is, over-simplification is avoided.) I also tend to ensure that all user access to the data is through either a stored procedure or a read only view (perhaps with a little paranoia thrown in ;). Ted ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ----------------------------------------- The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
