I am glad I posted the question. Yes James, there is little I can disagree with in your excellent summary. Even the critique of my perhaps poorly framed question is indeed valid. I take your point regarding spec vs implementation, and in my experience across different rdbms's I have frequently seen evidence which supports your assertions regarding db/os system influence on implementation approaches.
Application code too has it's part to play. How do we plan to access the data? There's a strong case too, IMHO, to have flexibility in the design, perhaps leading to differing approaches with the variable types of data we may be storing. The original design decisions were made by someone who was, and still is, essentially a hobby programmer. I don't think he'd ever heard of of Chris Date or Mr Codd at that time and like all novice application programmers, he had little understanding about the effect db design could have on his application and it's source code. His design choices were initially made on the basis of what he could easily understand and what was (as it appeared to him then) easy to program with. It is some time ago, and we who have lived with rdbms's for years get to say, 'that is a horrible design!'. I think R. Smith hit on a point above, regarding code overhead. Yep, plenty of that. And so the lesson is learned the hard way for someone who until recently had viewed normalisation as a way to make coding harder and to slow down the execution of queries. So with that perspective you can perhaps come some way to understanding the why component. On analysis, I agree with suggested design changes at the higher level. i.e. Dynamic tables are at the root of issues going forward. They are requiring tedious application code gymnastics, more difficult query analysis and poorer query performance. I suppose the upside is that it will be a challenge to see what improvements can be made, and that is always fun and games. I kind of like Mr Smith's other suggestion about an SQLITE testbed or prototype. So easy to work with SQLITE, and probably perfect for this task. Thanks all for your contributions. Just FYI James, the application is coded in php and connects to a mysql database. It can be installed either as a browser based, stand alone or client server app. It's common implementation is on low end shared hosts, even free hosting services. So this limits us somewhat to what is commonly allowed on such platforms. Things like Stored Procedures are unfortunately outside our scope when it come to design considerations. Thanks all. On 18 October 2014 02:24, James K. Lowden <jklow...@schemamania.org> wrote: > On Thu, 16 Oct 2014 09:05:51 +1100 > Michael Falconer <michael.j.falco...@gmail.com> wrote: > > > we just wonder if there is a better way to perform this search in > > SQL. Is there a general technique which is superior either in speed, > > efficiency or load bearing contexts? > > The simple answer is No, because SQL is a specification, not an > implementation. Different systems implement it differently and > therefore perform differently. Any "general technique" affecting > performance belongs to the implementation per se, not the SQL, which is > a logical construction. SQLite itself has changed its performance > characteristics over the course of its development. > > For that reason, any question of performance has to be answered in > terms of a particular implementation, even its specific version, and > the OS and hardware it's running on. > > That said, there is reason to suppose that a single-table design would > be more efficient. If the queries can be expressed with recursion and > the indexes lead to efficient searches, the query optimizer has less > work to do. It has fewer permutations to consider, and the search is > apt to touch fewer pages. The analysis tools of the system you're > using should be able to confirm or deny that supposition. > > I would remind your fellows, though, that efficiency is not all. The > utility of a model (that is, the database design) is measured by how > well, to its purpose, it describes the real world. Any model that must > be changed as that reality changes in predictable ways isn't really > much of a model; it turns the designer into a component of the model. > By recognizing all trees as one, you generalize your model and make it > do work you are now doing yourself (manually, or in application > logic). By any measure, that makes it a better model. > > HTH. > > --jkl > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Regards, Michael.j.Falconer. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
