Poll: Insert Value Loop versus Insert Select Join

I’d like some feedback, if you don’t mind.

We’re reworking our online store, and I’m looking at refactoring the ordering process. Right now, it uses a traditional approach. It inserts the order header, then loops over the items in the shopping cart and inserts them into the detail table. Like the following pseudocode:

<cftransaction>
    <cfquery datasource="#Request.DSN#">
    INSERT INTO OrderHeader (...)
    VALUES (<cfqueryparam...>, ...)
    </cfquery>
    <cfloop from="1" to="#arrayLen(Session.Items)#" index="i">
        <cfquery datasource="#Request.DSN#">
        INSERT INTO OrderDetail (...)
        VALUES (<cfqueryparam...>, ...)
        </cfquery>
    </cfloop>
</cftransaction>

Traditional, right? I want to refactor it into something like this:

<cftransaction>
    <cfquery datasource="#Request.DSN#">
    INSERT INTO OrderHeader (...)
    VALUES (<cfqueryparam...>, ...)
    </cfquery>
    <cfquery datasource="#Request.DSN#">
    INSERT INTO OrderDetail (...)
    SELECT p.SKU, p.Price, d.Qty, ...
    FROM (
        <cfloop from="1" to="#arrayLen(Session.Items)#" index="i">
        <cfif i GT 1>UNION ALL</cfif>
        SELECT <cfqueryparam value="#Session.Items[i].SKU#"...> AS SKU, ...
        </cfloop>
    ) AS d
    INNER JOIN Products AS p ON (d.SKU = p.SKU)
    </cfquery>
</cftransaction>

Benefits:

Instead of n+1 queries for each order there are exactly 2.
It eliminates the need to do queries to look up prices or other information.
In my tests it is faster, especially for large numbers of items.

The drawback of course being that it’s pretty nontraditional. I’d even venture to say that it’s counter-intuitive enough that most CF coders looking at it are going to scratch their heads and think I was on crack cocaine.

Thoughts?

{democracy:21}

By Rick Osborne