Looking for Elegant Efficient T-SQL Code

Question

Looking for Elegant Efficient T-SQL Code

P Jones

SSChampion

Points: 12361
More actions
March 22, 2011 at 4:18 am

#233518

'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.

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

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

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: 1004340 More actions · Answer 2

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.

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)

P Jones SSChampion Points: 12361 More actions · Answer 3

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: 1004340 More actions · Answer 4

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.

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)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

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.

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)

UMG Developer SSChampion Points: 13482 More actions · Answer 6

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: 12361 More actions · Answer 7

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 More actions · Answer 8

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: 1004340 More actions · Answer 9

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.

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)

gsc_dba SSCertifiable Points: 5407 More actions · Answer 10

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: 4774 More actions · Answer 11

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. 😉