Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Re: How do Monks programatically construct SQL selects

by dws (Chancellor)
on Sep 03, 2003 at 07:58 UTC ( [id://288534]=note: print w/replies, xml ) Need Help??


in reply to How do Monks programatically construct SQL selects

How do Monks programatically construct SQL selects.

I don't. I tend to work in highly normalized schemas, with "interesting" queries (3-6 table joins, some non-equi-joins, some outer joins). I could spend time trying to warp a framework into generating workable queries, or I could code them by hand. So far, I've gotten there quicker by coding them by hand. By approaching the problem from use cases, building specific queries, I avoid having to solve a bunch of otherwise general-case problems, and can spend the time instead writing specific unit tests.

The problem with programatically generated queries is edge cases--particularly vendor-specific ones. By the time you fight your way around one or two of them, you've eaten up more time than you would have spent hand-coding a few dozen queries.

  • Comment on Re: How do Monks programatically construct SQL selects

Replies are listed 'Best First'.
Re: Re: How do Monks programatically construct SQL selects
by mpeppler (Vicar) on Sep 03, 2003 at 18:30 UTC
    How do Monks programatically construct SQL selects.

    I don't.

    Indeed. It's interesting how this particular question comes up again and again. Yes, SQL queries can be generated, but when you start using a normalized schema auto-generated queries tend to fall down. In order to get good, repeatable performance it is essential to write well behaved queries (i.e. queries where the JOIN and/or WHERE clauses use appropriate indexes) as a single badly written query can bring even very powerful hardware to its knees...

    This is one of the reasons why I always try to use stored procedures where I can.

    Michael

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://288534]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others scrutinizing the Monastery: (6)
As of 2024-11-06 10:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    chatterbot is...






    Results (32 votes). Check out past polls.