Post reply

Delete time

  • December 4, 2007 at 2:02 am

    #184522

    Hi guys.

    We are about to implement sql 2005. I have read a few articles on partitioning data on sql 2005, and several articles calims delete time and/or truncate time is dramatically changed by the use of partitioning. The change is for the better, so this seems to me as something I need to consider.

    We mostly do bulk insert of data from catalog to datamarts level, but we also have some issues where we do delete and then insert.

    What I am thinking of is to set up a partitioning based on week level, thus I just need to delete that one week in question in stead of reading an entire table of data. I see that this should take lesser time, but I want to test how much.

    Does anybody of you have any methods to track this.

    Any other comments regarding my upcoming task is also welcome.

    Thanks in advance.

    Dan

  • December 4, 2007 at 7:06 am

    #756911

    Heh... stop watch or ...

    DECLARE @StartTime DATETIME

    SET @StartTime = GETDATE()

    --...code to measure duration of goes here...

    PRINT CONVERT(CHAR(12),GETDATE()-@StartTime,114) + ' Duration (hh:mi:ss:mmm)'

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

  • December 4, 2007 at 10:26 am

    #757056

    Deletes will definitely benefit "AS LONG AS" partitioned colums are specified on the WHERE clause, Inserts will take advantange of the partitioning too. Seriously doubt that Truncate will have any effect though. 🙂


    * Noel

  • December 4, 2007 at 1:57 pm

    #757131

    How would TRUNCATE even factor in? Do you actually have something big enough to be considered for partitioning that has NO foreign key constraints? And "eligible" to be rebuilt from scratch?

    I understand you're talking theory - just curious if you had such a scenario in mind....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 4 posts - 1 through 4 (of 4 total)

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