Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    706

    Tip: Avoid the proliferation of queries

    Look through a typical database application, and what do you see everywhere?
    Code:
     qry = "SELECT id, fname, lname, custtype FROM customer WHERE ctype = 'P' AND prodcode = '" & pc & "'" 
    db.ExecQuery(qry)
    qry = "UPDATE translog SET ccode = '412' ..etc..
    ...
    ...
    In other words, you see SQL statements being laboriously constructed and executed, right there on the spot, and you find those statements scattered all throughout the code!

    What's wrong with this picture? What you have created here is a whole snarled web of functional and data dependencies, scattered willy-nilly all throughout the code. These dependencies include the current structure of the database, field names, and data-dependent things like exactly what "ccode = '412'" happened to mean on the day that the programmer wrote that code.

    If anything about the database or the application changes, as inevitably they will, these dependencies will explode throughout the code. How will you know conclusively what statements will need to be altered? How will you know if you've overlooked something? What would you possibly do if your employer acquires another business and the systems from the old company must somehow, but quickly, be integrated with yours? This happens all the time!

    I suggest that the best solution to this problem, even if you find yourself making the necessary changes slowly and incrementally throughout the code while doing other things (which, btw, is the normal situation...), is to compartmentalize the application code so that all of the logic related to one "thing," like "a customer," is contained in one place and one place only. If you can use Objects, then so much the better. Even if you write a routine and use it only once, you are documenting the fact that the logic is used somewhere, and pointing out (with each call...) where it is.

    I also strongly suggest that you and your team maintain an ongoing log or diary of the changes that are being made. Use a source-code control system. Use a bug tracking system. Use anything. But don't rely upon your memory. The application-specific knowledge in your own head doesn't stay there for long, and it's impossible to transfer it very well to the next co-worker, unless... that information has been written down and s/he can simply read through it over a weekend.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Good point sundial for the same reason we have constants and stored procedures. The logic is in one place, the change is in on place and it can be easily documented.

  3. #3
    Join Date
    Oct 2003
    Posts
    706

    Good point sundial for the same reason we have constants and stored procedures. The logic is in one place, the change is in on place and it can be easily documented.
    Constants are very good (they avoid the proliferation of "magic numbers"). Stored-procedures are very good (if you're using a server that implements them decently!). Now specifically what I'm advocating is that you should strive to achieve the same kind of encapsulation within your program's logic, too.

    I like to think of a database as providing me with at least the informal notion of a "persistent object." Somewhere in that database, somehow represented (maybe in one database table, maybe spread among several) there are records of "things." There is sufficient information to allow me to know and to update the status of those "things." But the data structures in the database, are only the means of representation, and of course, of transaction-processing. They are "data structures."

    But "Algorithms + Data Structures = Programs." Therefore it is necessarily true that, scattered through one or more programs and places within those programs, there must exist many algorithms, big and small, which reference those data structures and which in so doing become functionally dependent upon them. In a typical program, the size and extent of those functionally-dependent areas is unknown. It is very critical that we should seek to minimize and to isolate those dependencies ... even the ones as innocent and as ubiquitous and pervasive as "building an SQL query string."

    I think we still mumble the word "efficiency" at times like these, as justification, but "at 500 million ops per second, nobody can hear you scream..."
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I think I understand the reasoning.

    Could you put together a brief example of what you are trying to achieve?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •