Using synonyms to redirect queries

Question

Using synonyms to redirect queries

Viewing 6 posts - 31 through 35 (of 35 total)

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Roust_m (10/22/2009)
ChiragNS (10/22/2009)
The table name and the synonym name would be different. You will have to change the code wherever you have coded with the tablename
Not at all. I will name the synonym to whatever the current view name is and point it to one of the tables at a time.

You mean to say the same name as of the view existing int the database.

You are right about the coding part. You are going to switch names of the synonyms just after the data load and indexing. So not more than 5 ms as others pointed out.

"Keep Trying"

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

To be clear, you only need one synonym and you alter it to change where it's pointed.

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

villersk SSCrazy Points: 2007 More actions · Answer 3

can you partition the table? this way you could insert into a swap table and switch the data in. Partition switching is crazy fast in 2008. I have no experience with it in 2005

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

The primary question was "... will the system block if I drop the synonym and recreate it to point to the new object with ongoing operations ..."

And off course the answer is YES it will.

Test it ...

First connection:

USE tempdb;

GO

/*

DROP SYNONYM snMyProduct;

*/

if object_id('snMyProduct') is null

begin

Select 'Primary create of synonym' as ServiceNotification;

CREATE SYNONYM snMyProduct

FOR AdventureWorks.Production.Product;

end

else

begin

Select 'Modification of synonym' as ServiceNotification ;

DROP SYNONYM snMyProduct;

-- Create a synonym for the Product table in AdventureWorks.

CREATE SYNONYM snMyProduct

FOR AdventureWorks.Person.Contact;

end

-- Query the Product table by using the synonym.

SELECT top 1 *

FROM snMyProduct

;

GO

execute this once.

Now open a second connection and run this

USE tempdb;

set nocount on

go

Set transaction isolation level repeatable read

go

begin tran

GO

-- Query the Product table by using the synonym.

SELECT top 1 *

FROM snMyProduct

;

GO 10000

rollback tran

Now whilst this second connection is executing the 10000 selects

rerun the frist connection.

TaTaa :hehe:

This proves it can occur.

If you had run the second connection using read committed isolation level, chances are your drop/recreate of the synonym would be able to succeed, however chances are a connection would generate an "object not found" error if it would execute right in between these statements.

Just keep that in mind.

Chances are 1/**** :ermm:

To ease authorization, I would create the synonym in a particular schema, so you can grant the select on schema level so you don't have to re-grant each time you recreate the synonym.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Roust_m SSCoach Points: 17410 More actions · Answer 5

ALZDBA (10/23/2009)
The primary question was "... will the system block if I drop the synonym and recreate it to point to the new object with ongoing operations ..."
And off course the answer is YES it will.
Test it ...

Thanks for the example. I will make sure that anything reading from the synonym uses nothing more then "read committed" isolation level. We can actually afford to use (nolock) on the synonym.

Roust_m SSCoach Points: 17410 More actions · Answer 6

villersk (10/23/2009)
can you partition the table? this way you could insert into a swap table and switch the data in. Partition switching is crazy fast in 2008. I have no experience with it in 2005

This option is on the table, however, partitioning the base tables will not help, as they are joined in an indexed view and as the new data is not sequential for the indexes of the indexed view, thus is not going to help much, as during the data load, the indexes on the indexed view would have a lot of page splits.

We are considering materializing the indexed view into a table and then partitioning it, but this may bring whole lot of other issues. E.g. it will require doing two data loads: one into the base tables, and the other into the denormalized table and doing it incrementally into the denormalized table may be a challenge.