Using synonyms to redirect queries

Question

Using synonyms to redirect queries

Viewing 15 posts - 16 through 30 (of 35 total)

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

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

Ummmm.... why build the reporting view at all? Build the table so it looks like the view, index it correctly, and have synonym pointing at the table in use. When you're done building the new table, alter the synonym. It will take a very tiny number of milliseconds to do the flop.

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

Jeff, I think I can answer that one myself.. Low latency and lots of data. Apparently it takes like 40 minutes to re-index the data.. What I'm wondering is why the view performs so poorly to begin with and what can be done to the underlying structures to fix that since it seems this view is important to the structure of the thing and I see scalability issues with this whole thing..

CEWII

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

Elliott W (10/20/2009)
Jeff, I think I can answer that one myself.. Low latency and lots of data. Apparently it takes like 40 minutes to re-index the data.. What I'm wondering is why the view performs so poorly to begin with and what can be done to the underlying structures to fix that since it seems this view is important to the structure of the thing and I see scalability issues with this whole thing..
CEWII

That's why I'm suggesting the table pairs with a synonym to flop between the two... you can take your time building the table and the indexes (will go faster because of no contention) and the flop will take somewhere between 0 and 65 milliseconds. What you'll end up with is a table with perfectly ordered indexes and no page splits.

Speaking of page splits... on a table with a large number of daily inserts, the clustered index should support the temporal order of inserts to prevent page splits... the side benefit is that you'll never need to reindex the clustered index if you do it that way (unless you update the CI column(s))

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

Roust_m SSCoach Points: 17410 More actions · Answer 4

Elliott W (10/20/2009)
I think I'm going to have to suggest a different tack..
I think you should find out what is wrong with the underlying objects structures. ie, figure out why the view requires indexes to perform acceptably.. I have to think that you are going to have scalability problems moving forward.
CEWII

This option has been explored many times already. The reason we chose to go with the view is the same to many other environments where they decide to denormalise when performance starts to bite.

Roust_m SSCoach Points: 17410 More actions · Answer 5

That's why I'm suggesting the table pairs with a synonym to flop between the two... you can take your time building the table and the indexes (will go faster because of no contention) and the flop will take somewhere between 0 and 65 milliseconds. What you'll end up with is a table with perfectly ordered indexes and no page splits.
Speaking of page splits... on a table with a large number of daily inserts, the clustered index should support the temporal order of inserts to prevent page splits... the side benefit is that you'll never need to reindex the clustered index if you do it that way (unless you update the CI column(s))

Two tables structure is being considered. That’s why I've asked this question about the synonym and how seamless it would be to re-create it. There is some cost attached however, because the tables have to be very large, they have to be on two additional independent arrays, so they do not affect each other and the app, while being built. It would be the best solution if only I could sell it to the management.

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

The table name and the synonym name would be different. You will have to change the code wherever you have coded with the tablename

"Keep Trying"

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

I think you can largely make it seemless to the app by doing the swap in a transaction, even if it isn't then the time between the synonym existing and not and then existing will be on the order of <5ms on most systems.

I wanted to again bring up that I am concerned with the whole need for this since it raises strong scalability questions, at some point even this won't be able to keep up.

(climbing down off my soapbox)..

CEWII

Elaine Reeves SSC Enthusiast Points: 173 More actions · Answer 8

Have you thought about having 2 indexed views, pointing to the different tables. One for loading the other for use. Then, once the new data is loaded, indexes rebuilt and ready for use, just use a "sp_rename" to switch/rename the view names? This way the stored procedure would not need to be rebuilt and minimal impact to website.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Though the synonym solution may work, I would be concerned that the solution is not right - and may be a little more work and cost than you want / need.

It looks like, as Elaine suggested, that you could take advantage of sp_rename - bulk load a "temp" table, perform the indexing operations, and then rename it once that is complete. We use a similar setup for several processes where the SLA require ~99% uptime for the clients. By renaming the "temp" table, we achieve a very seamless result.

Second part of it is the slowness of your load operation. Elliott makes a very good point about the underlying design. With your understanding of the app and DB design, you could probably conceptualize a better db design. Developing a DB redesign may save you more time and money in the long run - while allowing for better scalability.

I would lean heavily to the sp_rename process for an interim solution while the DB restructure is being done as the long term solution.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 10

The only thought I have on the rename is that is has the same problem as the synonym. The referenced object does not exist for a while. What I mean by that is using the synonym method the synonym is dropped and rebuilt, preferably in a transaction, which I think will block access long enough to make it seemless, I may be wrong there.. When using the rename method, you have to rename the current table out of the way, so at that point it doesn't "exist" as far as the sproc is concerned, and then rename the new into its place, again in a transaction.. I don't see much of a difference, you still have to do the redirect, and I really don't like using sp_rename very much, but that is me..

CEWII

Roust_m SSCoach Points: 17410 More actions · Answer 11

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.

Roust_m SSCoach Points: 17410 More actions · Answer 12

Elaine Reeves (10/22/2009)
Have you thought about having 2 indexed views, pointing to the different tables. One for loading the other for use. Then, once the new data is loaded, indexes rebuilt and ready for use, just use a "sp_rename" to switch/rename the view names? This way the stored procedure would not need to be rebuilt and minimal impact to website.

That is a good idea, worth considering. I would not be renaming views though. I will use the same synonym to point to one of them at a time.

Roust_m SSCoach Points: 17410 More actions · Answer 13

Roust_m (10/22/2009)
Elaine Reeves (10/22/2009)
Have you thought about having 2 indexed views, pointing to the different tables. One for loading the other for use. Then, once the new data is loaded, indexes rebuilt and ready for use, just use a "sp_rename" to switch/rename the view names? This way the stored procedure would not need to be rebuilt and minimal impact to website.
That is a good idea, worth considering. I would not be renaming views though. I will use the same synonym to point to one of them at a time.

However there is a potential problem maintaining identity columns for two sets of tables. Lets say on one day some user generated content gets inserted into the active set of tables, while the passive table gets data load. The identity values will be different between the two sets of tables and we may have trouble synchronising two tables having two (the same) companies but with different CompanyID.

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

Heh... "It Depends".

It all depends on how you load the data (ie: IDENTITY_INSERT) and whether or not an IDENTITY column is even needed. I don't see an IDENTITY column being a problem never mind a show stopper on this method.

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

Roust_m SSCoach Points: 17410 More actions · Answer 15

Jeff Moden (10/22/2009)
Heh... "It Depends".
It all depends on how you load the data (ie: IDENTITY_INSERT) and whether or not an IDENTITY column is even needed. I don't see an IDENTITY column being a problem never mind a show stopper on this method.

I don't have much choice about the loading the data as this is done by other people. We do not use IDENTITY_INSERT. So whenever a new company is created by a user or a new company is created via data loading, it gets new CompanyID via identity property of the column. Doing otherwise would require a lot of changes in the application and the data loading processes which is not an option.

I think I could use the second Company table to be not so symmetric to the original one. So it will not have identity property on the column and all the data loading and user inserts will be happening on the primary table. The secondary table will only serve as a backup solution during the times the primary one gets data loaded. It will be updated from the primary table with the most recent changes when it is inactive.