Beefy Boxes and Bandwidth Generously Provided by pair Networks chromatic writing perl on a camel
Don't ask to ask, just ask
 
PerlMonks  

Re: How do Monks programatically construct SQL selects

by dws (Chancellor)
on Sep 03, 2003 at 07:58 UTC ( #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
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
Node Status?
node history
Node Type: note [id://288534]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (5)
As of 2014-04-21 02:11 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (489 votes), past polls