Re: how can i make this query run faster

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Ivan Arjentinski (jarj11-at-hotmail.com)
Date: 12/29/04 

Date: Thu, 30 Dec 2004 01:31:19 +0200

> INSERT tbl (col1, col2, col3,
> col4,
> col5, col6,
> ...
> SELECT col1, col2, col3,
> <some long expression resulting in col 4>,
> <half-long expre for col5>, <ditto for col6>,
I do the following:
--(Variant 1)
INSERT tbl (
    col1,
    col2,
    col3,
    col4,
    col5,
    col6,
    ...
SELECT
    t1.col1,
    t2.col2,
    t1.col3,
    <some long expression resulting in col 4> AS col4,
    <half-long expre for col5> AS col5,
    <ditto for col6> AS col6,
    ...

Note the "AS colX" rename. This is not required, but really helps keeping
the two lists in sync.

Sometimes, for long column lists, in order to further emphasize the rename I
even do:
--(Variant 2)
INSERT tbl (
    col1,
    col2,
    col3,
    col4,
    col5,
    col6,
    ...
SELECT
    t1.col1 AS col1,
    t2.col2 AS col2,
    t1.col3 AS col3,
    <some long expression resulting in col 4> AS col4,
    <half-long expre for col5> AS col5,
    <ditto for col6> AS col6,
    ...
so that if someone, for example, changes the column t1.col1 to expression,
the rename "AS col1" stays intact.

And finally, to be truly honest I even further emphasize the rename with
this:
--(Variant 3)
INSERT tbl (
    col1,
    col2,
    col3,
    col4,
    col5,
    col6,
    ...
SELECT
    t1.col1 AS col1_,
    t2.col2 AS col2_,
    t1.col3 AS col3_,
    <some long expression resulting in col 4> AS col4_,
    <half-long expre for col5> AS col5_,
    <ditto for col6> AS col6_,

so that if someone else comes and tries to maintain the code to easily
understand (without asking me) that this is just a rename and no other
tricks are involved.

Maybe the last case is an extreme, but I really think that code readibility
and maintainability are one of the most important things to follow when
creating code. 

--
Ivan Arjentinski

Relevant Pages