Limit Resources to UGLY query

  • Just a little warning... you are using an undocumented behaviour and a SP could belly up your project.

    Ummm... what undocumented behaviour?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Ninja's_RGR'us (10/2/2007)


    What is it for?

    Stocks and such. We have about 50 performance markers for a stock, bond etc. Rarely are we looking at more than 1 or two, but this code is for that exceptional scenario where I might want to look at any number of them. It not only allows the user to look at what might be considered dissimilar, but also look at comparisons between day, week, month etc averages.

    Lets just say, no missles, but more money that I will make in my life will be on the line 🙁

  • I still don't know what "undocumented behaviour" Noel is talking about...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • So, Bob, should we buy or should we sell?

  • I'm with Jeff, what's undocumented? The (#index)? That should be ok.

  • FROM BOL

    SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.


    Not sure what isn't documented.

  • Bob Fazio (10/3/2007)


    FROM BOL

    SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.


    Not sure what isn't documented.

    In what part is the UPDATE statement there ? 😉


    * Noel

  • In what part is the UPDATE statement there ? ;)[/quote]

    Sorry here is the whole thing.

    [ WITH [...n] ]

    UPDATE

    [ TOP ( expression ) [ PERCENT ] ]

    { | rowset_function_limited

    [ WITH ( [ ...n ] ) ]

    }

    SET

    { column_name = { expression | DEFAULT | NULL }

    | { udt_column_name.{ { property_name = expression

    | field_name = expression }

    | method_name ( argument [ ,...n ] )

    }

    }

    | column_name { .WRITE ( expression , @Offset , @Length ) }

    | @variable = expression

    | @variable = column = expression [ ,...n ]

    } [ ,...n ]

    [ ]

    [ FROM{ } [ ,...n ] ]

    [ WHERE {

    | { [ CURRENT OF

    { { [ GLOBAL ] cursor_name }

    | cursor_variable_name

    }

    ]

    }

    }

    ]

    [ OPTION ( [ ,...n ] ) ]

    [ ; ]

    ::=

    {

    [ server_name . database_name . schema_name .

    | database_name .[ schema_name ] .

    | schema_name .

    ]

    table_or_view_name}

  • Ok that's the statement. Where does it says that it will update the varibles following a certain order ?

    We know that, so far, we have been able to force the use of an index on a table but the variable updating order is *not* documented 😀

    Hints are just that, "hints", the optimizer may or may NOT use them 😉


    * Noel

  • Nothing undocumented... you need to look in BOL for the UPDATE... I've bolded the part of the update I used....

    UPDATE

    {

    table_name WITH ( [ ...n ] )

    | view_name

    | rowset_function_limited

    }

    SET

    { column_name = { expression | DEFAULT | NULL }

    | @variable = expression

    | @variable = column = expression } [ ,...n ]

    { { [ FROM { } [ ,...n ] ]

    [ WHERE

    ] }

    |

    [ WHERE CURRENT OF

    { { [ GLOBAL ] cursor_name } | cursor_variable_name }

    ] }

    [ OPTION ( [ ,...n ] ) ]

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • noeld (10/3/2007)


    Ok that's the statement. Where does it says that it will update the varibles following a certain order ?

    We know that, so far, we have been able to force the use of an index on a table but the variable updating order is *not* documented 😀

    Hints are just that, "hints", the optimizer may or may NOT use them 😉

    Columns will be returned in the order listed... variables will be returned (populated) in the order listed. Items in the SELECT list will be done in the order listed. From BOL...

    Using the Select List

    The select list defines the columns in the result set of a SELECT statement. The select list is a series of expressions separated by commas. Each expression defines a column in the result set. The columns in the result set are in the same order as the sequence of expressions in the select list.

    Index or "Table" hints are only ignored if the table is not actually accessed... from BOL...

    Table Hints

    A table hint specifies a table scan, one or more indexes to be used by the query optimizer, or a locking method to be used by the query optimizer with this table and for this SELECT. Although this is an option, the query optimizer can usually pick the best optimization method without hints being specified.

    Caution Because the query optimizer of SQL Server usually selects the best execution plan for a query, it is recommended that only be used as a last resort by experienced developers and database administrators.

    The table hints are ignored if the table is not accessed by the query plan. This may be a result of the optimizer's choice not to access the table at all, or because an indexed view is accessed instead. In the latter case, the use of an indexed view may be prevented by using the OPTION (EXPAND VIEWS) query hint.

    Translation... INDEX hints are always used unless the table they are attached to is not accessed by the query...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden (10/3/2007)

    Columns will be returned in the order listed... variables will be returned (populated) in the order listed. Items in the SELECT list will be done in the order listed. From BOL...

    Using the Select List

    The select list defines the columns in the result set of a SELECT statement. The select list is a series of expressions separated by commas. Each expression defines a column in the result set. The columns in the result set are in the same order as the sequence of expressions in the select list.

    Index or "Table" hints are only ignored if the table is not actually accessed... from BOL...

    Table Hints

    A table hint specifies a table scan, one or more indexes to be used by the query optimizer, or a locking method to be used by the query optimizer with this table and for this SELECT. Although this is an option, the query optimizer can usually pick the best optimization method without hints being specified.

    Caution Because the query optimizer of SQL Server usually selects the best execution plan for a query, it is recommended that only be used as a last resort by experienced developers and database administrators.

    The table hints are ignored if the table is not accessed by the query plan. This may be a result of the optimizer's choice not to access the table at all, or because an indexed view is accessed instead. In the latter case, the use of an indexed view may be prevented by using the OPTION (EXPAND VIEWS) query hint.

    Translation... INDEX hints are always used unless the table they are attached to is not accessed by the query...

    The UPDATE Statement does not specify any ORDER on the updated ROWS so what your columns are being updated with are the previous-value which is NOT guarantee to be the prevoius ROW. in TSQL there is no ORDER BY in the update statement.

    The Query Optimization Team on all occassions that I have been questioning this "feaure" DO say that HINTS are nothing but that HINTS and you should never rely on that (specially in an update).

    If the optimizer code changes tomorrow your query "could" break.


    * Noel

  • Heh... and if they change the way Order By works, we'd all be screwed, too!...

    ...Wait, they did that 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • You know exactly that I am talking about standards but hey if you want to play that game with M$ the real answer is ... you never know not "if" but "when" is going to happen 😀


    * Noel

Viewing 14 posts - 46 through 59 (of 59 total)

You must be logged in to reply to this topic. Login to reply