Forum Replies Created

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

  • RE: Help us tune a query

    Also, here's what the new zipless sqlplan does in IE...

    The XML page cannot be displayed

    Cannot view XML input using style sheet. Please correct the error and then click the...

    --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 15, 2009 at 6:11 am

    #1024947

  • RE: Help us tune a query

    Mel Harbour (7/15/2009)

    Ok, I've run a modified version of Jeff's suggestion:

    Something sure is wrong... look at the difference between the CPU and the duration compared to my box.

    --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 15, 2009 at 6:02 am

    #1024941

  • RE: Help us tune a query

    Okidoki... thought I'd throw my hat into the ring on this, as well. I didn't try G-Squared's code yet, but here's mine.

    First, the test harness. It create 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 15, 2009 at 1:01 am

    #1024834

  • RE: Help us tune a query

    I think I found out how many... would it be right around 106,916?

    The only thing I need to know now is how many users are in the Users table? ...

    --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 11:01 pm

    #1024810

  • 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

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