Should I use sp_rename?

Question

Should I use sp_rename?

Rob Reid-246754

SSCarpal Tunnel

Points: 4183
More actions
May 20, 2008 at 7:30 am

#182590

I have to implement some new functionality for a website that requires me displaying the number of records per job category on a filter page. As users select more and more categories I have to show the number of records for that category taking into account all the previous categories they have selected. This means I have to know the count for each combination of category.
As this would be very slow to calculate at run time I am going to create an MS Agent job that creates a matrix table every 15 mins or so with all the different category combinations and their record count.
This is no problem but I am wondering about the best way of bringing this new table into action once created.
I was planning on either the following:
-Creating the new matrix table to the side of the existing table so that the website is still in use and then once the new table is complete using sp_rename to rename it to the live table.
-Having two tables (A, B) and then just toggling between them. So the site uses tableA until tableB is built then it uses tableB until tableA is rebuilt and so on toggling between them and storing the current table to use in memory in the application.
I want to know how fast sp_rename is as I imagine this matrix table to have hundreds of thousands of records and if websites are currently accessing it, which they will be constantly (selects only) then would there be any issues during the renaming process (Timeouts etc) or would the rename be seemless.
Thanks in advance for any help or advice recieved.

Viewing 15 posts - 1 through 14 (of 14 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'd use two separate tables, and a switch to tell the proc which one to look in. (And this should be a case of a master proc that calls one of two sub-procs. Each sub-proc only looks at one table. That way, they can have reasonable execution plans in place. Either that, or you need to have a recompile on the proc each time.)

- 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

Rob Reid-246754 SSCarpal Tunnel Points: 4183 More actions · Answer 2

Ok cheers but why would you do it this way. What would be the issue with sp_rename? Would it take too long to rename or just cause my site errors during the renaming of the new table to live table.

Thanks

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

37 to 65 milliseconds.

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

GSquared SSC Guru Points: 260824 More actions · Answer 4

There would be a table lock while it's being renamed, if not a lock on the whole database. That means every time you want to run the rename, you're stopping everything else that's running and making them wait.

I've also seen problems with renaming tables causing issues with various system data. I think that was mainly/only in SQL 2000, but I still don't fully trust it.

The solution with two tables, and alternating between them, is also just plain simpler to implement and to monitor. Simpler is usually better.

One of the things to keep in mind is that a proc that looks at a flag value, decides which one of two other procs to run, and each of them pulls data from one table, is something that a later developer (or you a year from now) can look at, figure out easily, and work with. Something that's constantly renaming database objects every few minutes is going to be harder to document, and harder to work with later on.

- 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

Rob Reid-246754 SSCarpal Tunnel Points: 4183 More actions · Answer 5

65 milliseconds doesn't sound too long to wait.

I'm running this in SQL 2005 by the way.

GSquared SSC Guru Points: 260824 More actions · Answer 6

Try it and load-test it. See if it does what you want.

- 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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

2k5 has Synonyms... much faster to flop a synonym between two tables.

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

Rob Reid-246754 SSCarpal Tunnel Points: 4183 More actions · Answer 8

I hadn't heard about SYNONYMs and have just read an article about them.

So I would still have my two tables MATRIX_A and MATRIX_B but my SELECT statements that reference

them would only ever refer to the name I give the synonym i.e SELECT * FROM MATRIX.

Then whenever I build the new table and want to toggle the site from old to new I would have

to do

DROP SYNONYM MATRIX

CREATE SYNONYM MATRIX

FOR MYDB.MYUSER.MATRIX_B

and so on. Seems like a good idea and prevents me having to do a lookup from memory/DB to find the table name to use before accessing it.

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 9

Something like

DROP SYNONYM MATRIX

CREATE SYNONYM MATRIX FOR MYDB.MYUSER.MATRIX_B

GO

Now when your agent job runs

DROP SYNONYM MATRIX

CREATE SYNONYM MATRIX FOR MYDB.MYUSER.MATRIX_A

GO

to use, all your queries would pull from MATRIX:

Select Fields from MATRIX

GO

Not sure what would happen to locking though...

Wouldnt it still lock while data is being read from the synonym?

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 10

I think an ALTER instead of a DROP would probably work faster.

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

Rob Reid-246754 SSCarpal Tunnel Points: 4183 More actions · Answer 11

From what I read in this article http://www.developer.com/db/article.php/3613301

there is no ALTER SYNONYM statement.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 12

In that case, I think I'd be tempted to use a passthrough view instead of a synonym.

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

Rob Reid-246754 SSCarpal Tunnel Points: 4183 More actions · Answer 13

Whats a passthrough view? Like a materialised view created on a timed job maybe?

I haven't done much work with views and I definitley don't need the data to be re-calculated everytime the view is accessed.

I was thinking that during the DROP SYNONYM that users might get some errors or the other way is holding the name of the current table to use in the apps memory then when the new table gets created this value gets updated afterwards so that subsequent calls will only ever use a table name that definitley exists.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 14

Rob Reid (5/23/2008)
Whats a passthrough view? Like a materialised view created on a timed job maybe?
I haven't done much work with views and I definitley don't need the data to be re-calculated everytime the view is accessed.

A "pass through" view is very simple...

[font="Courier New"]

CREATE VIEW someviewname AS

SELECT *

FROM databasename.user.tablename[/font]

The data is not "recalculated" and the views will use the indexes of the underlying table as if you were querying the table directly. We've used them very effectively at work (still on 2k at work) for years. And, yes, this is about the only place I allow SELECT *.

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