Forum Replies Created

Viewing 15 posts - 43,036 through 43,050 (of 59,098 total)

  • RE: Help us tune a query

    Mel Harbour (7/13/2009)

    The UserPoints table currently has about 185,000 rows. Execution plans are attached.

    Mel... I'm trying to setup the proverbial parallel universe for testing and need just a wee bit...

    --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)

    July 14, 2009 at 7:36 pm

    #1024790

  • RE: Create a Tally or Numbers Table

    What I meant by the BETWEEN was, if you have a zero-based table and need a unit-based solution, then the WHERE clause would need to include WHERE N BETWEEN 1...

    --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)

    July 14, 2009 at 7:08 pm

    #1024788

  • RE: Help us tune a query

    Here's the trace from my home machine...

    And, here's the code I'm running... All I did was take your code, remove your comments, add mine, make each query dump to a...

    --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)

    July 14, 2009 at 5:46 pm

    #1024784

  • RE: Help us tune a query

    Something else is going on as well. Look at the difference in durations on your machine compared to mine. The machine I have at work is just a...

    --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)

    July 14, 2009 at 4:24 pm

    #1024765

  • RE: Help us tune a query

    GilaMonster (7/14/2009)

    But on a single statement insert the rows will be sorted before they're inserted if they're inserted into a table with a cluster, so there won't be page splits.

    Ach......

    --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)

    July 14, 2009 at 1:46 pm

    #1024682

  • RE: Help us tune a query

    GilaMonster (7/14/2009)

    Jeff Moden (7/14/2009)

    Jack Corbett (7/14/2009)

    Was Jeff referring to the Order By in the ROW_NUMBER() function?

    Yes.

    Ok, misunderstood you earlier. Are you suggesting adding a second temp table, one to store...

    --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)

    July 14, 2009 at 12:59 pm

    #1024644

  • RE: Help us tune a query

    Jack Corbett (7/14/2009)

    Was Jeff referring to the Order By in the ROW_NUMBER() function?

    Yes.

    --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)

    July 14, 2009 at 10:54 am

    #1024565

  • RE: Help us tune a query

    GilaMonster (7/14/2009)

    Christopher Stobbs (7/14/2009)

    but creating the clustered Index on #TopScores after the actual insert?

    Would this help at all?

    If the cluster goes on after the insert then first SQL has to...

    --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)

    July 14, 2009 at 10:53 am

    #1024563

  • RE: Help us tune a query

    Mel,

    I haven't had the time to look at all the fine suggestions folks may have given, but if I look at your original query, I see the potential for a...

    --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)

    July 14, 2009 at 5:48 am

    #1024334

  • RE: Update where field is NULL

    You bet. Thank you for the feedback.

    As a bit of a sidebar, NULLs are some of the oddest things and can also be a very, very powerful tool depending...

    --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)

    July 14, 2009 at 5:30 am

    #1024327

  • RE: DBCC IND - how can I run it when there is a locked row in the table I am looking at?

    cb (7/13/2009)

    Hi Gaby

    Whilst everything you say is true, the environment I have to deal with is:

    Software vendor creates a product based on SQL, and sells it to client.

    Client wants some...

    --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)

    July 14, 2009 at 5:19 am

    #1024319

  • RE: Create a Tally or Numbers Table

    I'd have a hard time doing that because Number is a reserved word but to each their own. Also, are you implying that you have two separate tables? ...

    --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)

    July 14, 2009 at 4:59 am

    #1024315

  • RE: Are the posted questions getting worse?

    Matt Miller (7/13/2009)

    Jeff Moden (7/13/2009)

    Dave Ballantyne (7/13/2009)

    You can lead a horse to water but you cant force him to drink

    Heh... well... you can if you don't mind getting your lips...

    --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)

    July 14, 2009 at 4:44 am

    #1024310

  • RE: Update where field is NULL

    On most systems, you cannot say WHERE something NULL... instead, you usually have to say WHERE something IS NOT NULL or WHERE something IS NULL depending on what you're...

    --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)

    July 13, 2009 at 10:27 am

    #1024027

  • RE: Are the posted questions getting worse?

    Dave Ballantyne (7/13/2009)

    You can lead a horse to water but you cant force him to drink

    Heh... well... you can if you don't mind getting your lips dirty. I'm going...

    --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)

    July 13, 2009 at 8:59 am

    #1023937

Viewing 15 posts - 43,036 through 43,050 (of 59,098 total)