Post reply

URGENT PLEASE Got a SQL Scenario to solve

  • April 22, 2012 at 8:32 am

    #1477170

    GSquared (4/20/2012)

    I tested two-step upserts vs Merge, and [font="Arial Black"]it[/font] was faster in most cases in my code. But [font="Arial Black"]it[/font] isn't always faster, and possibly not even faster the majority of the time.

    What is the "IT" you're referring to in both of those statements? The two-step update or the merge? Is sounds like you're referring to the two-step update but I don't want to assume.

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

  • April 22, 2012 at 8:34 am

    #1477171

    mtassin (4/20/2012)

    GSquared (4/20/2012)

    I tested two-step upserts vs Merge, and it was faster in most cases in my code. But it isn't always faster, and possibly not even faster the majority of the time.

    Well presently I've done [font="Arial Black"]it[/font] to about 30 different execute SQL tasks in SSIS all showing remarkable improvement....

    But it could be I'm lucky. As with everything, it depends 🙂

    Reading back in this thread, it would appear that the "IT" you're talking about is the two-step update but I don't want to assume. What is the "IT" you're talking about here?

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

  • April 23, 2012 at 6:42 am

    #1477452

    Jeff Moden (4/22/2012)

    GSquared (4/20/2012)

    I tested two-step upserts vs Merge, and [font="Arial Black"]it[/font] was faster in most cases in my code. But [font="Arial Black"]it[/font] isn't always faster, and possibly not even faster the majority of the time.

    What is the "IT" you're referring to in both of those statements? The two-step update or the merge? Is sounds like you're referring to the two-step update but I don't want to assume.

    It becomes clear if you read what I was responding to. Merge was faster in the case of my particular project. It often isn't, perhaps more often that not per some reports I've read. I haven't done enough testing of my own to have any general statement to make on that, just that Merge was faster (and had other advantages regarding ACIDity of the data) than more traditional 2-step upserts.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • April 23, 2012 at 10:17 am

    #1477622

    Heh... even when I went back to read what you and Mark had said, there's (IMHO) some abiguity and I didn't want to just assume. Thanks for the answer.

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

  • April 23, 2012 at 10:29 am

    #1477629

    GSquared (4/20/2012)

    mtassin (4/19/2012)

    GSquared (4/19/2012)

    Once the data is on the remote servers, I have various T-SQL jobs in SQL Agent that do all the more complex transformations and distribute the data to three different databases for different websites. Complex business rules in each one, and so on. T-SQL procedures (LOTS of Merge statements, mainly) do that part more efficiently. But the initial long-distance copy of specific tables is most efficiently done by SSIS, at least in this case.

    Weirdly enough for me with SSIS, I've started replacing the MERGE statements for our DW with a series of lookups that stream new records in nice and quick and do updates for the changed already existant ones... Took merge statements that typically take 5 minutes and cut them down to 30-40 seconds...

    I was shocked as hell when I tested that out because I didn't expect to outperform the MERGE statement, but I did.

    I tested two-step upserts vs Merge, and it was faster in most cases in my code. But it isn't always faster, and possibly not even faster the majority of the time.

    Relevant portion bold. I posted that I was using lots of Merge statements, mtassin posted that Merge statements were slower in his solution, and I replied to that. If one assumes I didn't deliberately choose the slower solution (since I mentioned using lots of Merge statements, that's the solution I must have chosen), then "it" refers to Merge statements.

    Sorry if that was ambiguous. It didn't occur to me that it might be read that I tested Merge vs 2-step, found Merge was slower, and then used Merge anyway.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 5 posts - 16 through 19 (of 19 total)

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