Looking for Elegant Efficient T-SQL Code

  • P Jones

    SSChampion

    Points: 12323

    'd like to find a more elegant an efficient solution than a cursor or a dynamic "exec @sql" loop for this little job which clears the staging tables in an SSIS package - all in SQL Server 2008.

    It runs at a quiet time and as a sysadmin user and there are no constraints, referential integrity etc. just plain stand-alone tables so I could just use a cursor, but the "code artist" in me says there has to be a better way.

    I gather that sp_msforeachtable is just a glorified cursor, so I'd rather roll my own than use that. It is planned to be a stored procedure called by an execute sql task (unless there's a better suggestion).

    The skeleton script is:

    Delete From dbo.<tablename> t Where (t.Company = @CoNo)

    and (<tablename> in

    (SELECT name FROM sys.objects WHERE type = 'U'

    and right(rtrim(name),7)='Staging'))

    i.e. for every user table in the database with the name ending in "Staging" I want to delete all records where field company = @CoNo where @CoNo is the parameter passed in.

    The company field is guaranteed to exist in every staging table too so no checks needed.

    Any bright ideas welcome.

  • The Dixie Flatline

    SSC Guru

    Points: 53197

    This is one of those jobs where I have nothing against a cursor, or a while loop, because you are doing the same operation against multiple TABLES, as opposed to multiple ROWS within a table. Significant efficiency gains for set based coding don't exist in this context.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Jeff Moden

    SSC Guru

    Points: 994558

    I have to agree with Dixie... although my tendency is cringe at the mere mention of "Cursor" or "While Loop" and I really do understand the "code artist" in good folks like yourself, there is no gain in performance or ease of maintenance by avoiding them for a task like this.

    You could write some set based code to dynamically create all of the necessary commands to avoid the cursor, but they'd still be executed only one at a time just like a cursor does.

    All that notwithstanding, if you're still interested, I could cobble together an example but, be advised, it will still use dynamic SQL because of the requirement to change table names in the FROM clause. Let me know.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • P Jones

    SSChampion

    Points: 12323

    Thanks for the idea and the offer Jeff and I'll probably implement something along those lines. I'd rather cobble it together myself though, so I really understand it, especially when it needs modifying a year down the line!.

  • Jeff Moden

    SSC Guru

    Points: 994558

    P Jones (3/22/2011)


    I'd rather cobble it together myself though, so I really understand it, especially when it needs modifying a year down the line!.

    Can't ask for a better attitude than that! 😉 I like it! C'mon back if you run into a brick wall on it.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994558

    CELKO (3/22/2011)


    Why don't you know the names of the staging tables and hardcode them?

    Hmmm... good question, Joe.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • UMG Developer

    SSChampion

    Points: 13482

    I assume because the staging tables can change over time, and they don't want to have to update and maintain the reset procedure. (Or they want to use the same reset procedure across multiple databases with different staging tables.)

  • P Jones

    SSChampion

    Points: 12323

    Why don't you know the names of the staging tables and hardcode them?

    I do but they're increasing regularly and I'd hate to miss one in trying to keep in sync. There's at least 50 already and more to come as a new business system gets implemented.

  • TheSQLGuru

    SSC Guru

    Points: 134017

    Hard-coding the table names instead of using a sys.objects driven cursor to do these deletes may save you 0.001% of the total execution time. I say it would be ridiculous to NOT use a cursor for this process given your stated requirements.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Jeff Moden

    SSC Guru

    Points: 994558

    TheSQLGuru (3/23/2011)


    Hard-coding the table names instead of using a sys.objects driven cursor to do these deletes may save you 0.001% of the total execution time. I say it would be ridiculous to NOT use a cursor for this process given your stated requirements.

    And now we have a good answer to Joe's question. 🙂

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • gsc_dba

    SSCertifiable

    Points: 5407

    How about using your existing method but using a schema (in your example "staging")

    SELECT [sys].[objects].[name]

    FROM sys.objects

    INNER JOIN sys.[schemas] ON [sys].[objects].[schema_id] = [sys].[schemas].[schema_id]

    WHERE type = 'U'

    AND [sys].[schemas].[name] = 'Staging'

    So ALL of your SSIS tables are created in the Staging schema...

    In response to:

    I do but they're increasing regularly and I'd hate to miss one in trying to keep in sync. There's at least 50 already and more to come as a new business system gets implemented.

    gsc_dba

  • Wildcat

    SSCarpal Tunnel

    Points: 4714

    gsc_dba (3/24/2011)


    How about using your existing method but using a schema (in your example "staging")

    So ALL of your SSIS tables are created in the Staging schema...

    In response to:

    I do but they're increasing regularly and I'd hate to miss one in trying to keep in sync. There's at least 50 already and more to come as a new business system gets implemented.

    We use this approach in our environment. 😉

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

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