|
From Guyren Howe >Actually, I’m mostly going to talk about the relational model, rather than SQL. Our industry seems to always settle for third-best, and SQL is the worst of all the examples of this. The world desperately needs a good relational database
based on a better query language — datalog, for example. >I put up with SQL so I can use the relational model, and I think that understanding SQL has to start with that. I can understand that that. :) Yet SQL is the interface/lens we most use to interact with an RDBMS. >>An example of this is that we have a report we're trying to write that I'd like to think can be done in SQL, but I can't think of a way to do it. Yet, if I do the base query and pull the data back into my application, I can do the last bit with 3 lines of Perl very easily. The problem here revolves around comparing a row of data to the previous row to know whether the data changed "significantly enough" to keep the new row. >>Another example is doing running totals. A couple of years ago I would have said SQL can't do that. Now I know about the OVER clause, something that I would categorize as somewhat obscure, I can do it as needed. >Actually, Window functions might be "advanced", but are certainly not obscure. Your example sounds like it’s trivially solved with LAG(). Fair enough. Perhaps it would be better to say that many don't go to the depths to learn window functions until forced to, and a lot of problems can be solved without them. I can say that would be true for me and those I work with. Thanks for the tip on LAG()! It's not a function I've used before (which makes it a great example for the previous para). >>As Michael Lewis pointed out, large dataset can also cause you to choose not to use SQL in 1 big statemet for everything (i.e. advocating the use to temp tables). In some ways, using a CTE is a type of temp table, or at least I view it as such. That allows a person to solve a problem in bite-sized chunks. I will agree that optimization can do it better at times, but the code also has to be maintained as well – a balancing act. >This appears to be good advice with SQL Server, which I’m coming to learn has a fairly poor query optimizer. But I would have thought Postgres’s optimizer would usually use a temporary table where appropriate. I'm sure Pg's optimizer does its work better than I would manually, but that's not always the point. For some I've talked to, thinking relationally and in sets is hard. I'll even admit that while I think I do it pretty well, there are times I have to stop and really consider what I'm doing. If you're used to thinking about how to solve a problem with a computer language, most of them are imperative and that mindset can be hard to get out of. So your "philosophy" approach is a well-aimed arrow in many ways, IMO. Also, don't forget about "maintenance". Solving something in bite-sized chunks allows for easier changes in the future, as well as understanding by newcomers -- generally speaking. >Curious to hear if that’s wrong. Depends on what version. In the earlier versions of Pg, CTE's were a fence and there was no crossing over. That fence now has gates in some places in the latest versions from what I read. >>I think your idea is a good one, but I do hope you present that SQL can't solve everything ... else why do we have plpgsql. :) You’re correct though, SQL isn’t used as much as it should be in many places. >An important consideration will be when relational is inappropriate. My biggest goal, though, is to get folks to understand how much relations *can* do — far too many devs in my experience don’t use the power of SQL because they don’t
understand it. >Thanks for taking the time to give me this feedback. I appreciate the efforts of teachers in all forms. I hope your project goes well and the feedback has some use. Kevin |
