Why are String Formatted Queries Considered So Magical?

[Stephen Hansen]

On 6/27/10 8:48 PM, Carl Banks wrote:
> I don't know the exact details of all of these, but I'm going to opine
> that at least some of these are easily expressible with a function
> call API.  Perhaps more naturally than with string queries.  For
> instance, set operations:
>
> query1 = rdb_query(...)
> query2 = rdb_query(...)
>
> final_query = rdb_union(query1,query2)
>
> or
>
> final_query = query1&  query2

But, see, that's not actually what's going on behind the scenes in the 
database. Unless your "query1" and "query2" objects are opaque 
pseudo-objects which do not actually represent results -- the query 
planners do a *lot* of stuff by looking at the whole query and computing 
just how to go about executing all of the instructions.

The engine of a SQL database is a pretty sophisticated little pieces of 
coding. Because SQL is declarative, the engine is able to optimize just 
how to do everything when it looks at the full query, and even try out a 
few different ideas at first before deciding on just which path to take. 
(This is an area where parametrized queries is even more important: but 
I'm not sure if MySQL does proper prepared queries and caching of 
execution plans).

If you go and API it, then you're actually imposing an order on how it 
processes the query... unless your API is just a sort of opaque wrapper 
for some underlining declarative structure. (Like ORM's try to be)

-- 

    ... Stephen Hansen
    ... Also: Ixokai
    ... Mail: me+list/python (AT) ixokai (DOT) io
    ... Blog: http://meh.ixokai.io/