October 19, 2009 at 10:43 pm
Hi,
I was thinking of using the synonyms to access two tables. One table would be active at a time, and another would be being built at that time. To redirect the synonym to another table I would have to drop it and re-create it.
I am wondering if there is user activity at that time, would I have troubles dropping it because of the blocking? If anyone had such an experience, would you be able to tell how seamless such operation would be?
Thanks.
October 19, 2009 at 11:02 pm
I don't think this is a good use for synonyms..
Why don't you tell us what problem you are trying to solve. We can probably give you some guidance..
CEWII
October 19, 2009 at 11:35 pm
I have an indexed view, which is a de-normalised structure to speed up queries on the database. Everything goes well until the data loading happens. Lots of data cause the page splits on the indexes on the indexed view and the performance degrades until the indexes get re-built on the following weekend.
I could drop the indexes on the view for the time of the data loading and later re-create them, but this would effectively put the site offline, which is not acceptable.
I want to create two physical tables, one of which will be active and another passive at a time. Ones the data loading finishes, the passive table would be re-built (in full or incrementally) and then become active and vice-versa.
To make it all transparent to the app I want to use synonyms to re-direct the app to the right table.
BTW, why is it not good use for a synonym?
October 19, 2009 at 11:59 pm
How big are these tables? How long does the re-index take?
The synonym does have to be a drop/create, but I don't think it does any blocking..
When I first read your post I was thinking something else, this may be ok..
You might consider two views, the reason I am suggesting that it that it simplifies your ETL process, it only has to know about one table. It can call a sproc that handles the guts of the switch-over. There would be a moment when the synonym didn't exist.. But I did a little test, I created 2 tables, 1 synonym. I began a transaction, dropped the synonym, created the synonym, and then rolled it back. I began a transaction, dropped the synonym, created the synonym, and then commited it. I'm thinking you can do it in a transaction so that maybe it would be invisible.
Was that clear at all?
CEWII
October 20, 2009 at 12:11 am
The tables are pretty big, even the base ones, not to mention the de-normalised ones, so the re-index may take 40 or more minutes.
Yep, this is what I was after. Short interruption would still be acceptable, but having it 100% transparent would be better.
I am not concerned about the ETL, as this is secondary, the application and its maintainability is the priority.
October 20, 2009 at 12:30 am
Does the website use the base tables as well? I'm just a little fuzzy on this whole thing.. I found I have been moving away from using indexed views and the more I deal with them the less I like them..
CEWII
October 20, 2009 at 12:47 am
It does a bit, but too many of the queries use the indexed view and would not be fast enough without it.
I am trying to do the same: move away from indexed view, but I have to substitute it with something.
October 20, 2009 at 12:50 am
How often does the underlying data change? If not a lot then you could use SSIS to assemble and bulk load a table with the denormalized data. Or if you could have some latency..
CEWII
October 20, 2009 at 12:59 am
Quite often, it may be every night sometimes. And lots of data... This causes a lot of page splits on the indexes.
BTW, the underlying tables have to be loaded as well and the clustered index on the indexed view (of future big table) is not sequential, so bulk loading will not solve the problem.
October 20, 2009 at 1:04 am
They don't want any latency, so after the data is loaded or the records inserted individually (in case of user generated content), they have to be visible on the site, so they have to be present in the "big" table too.
October 20, 2009 at 1:22 am
Short of using triggers to maintain a table I don't have a good answer.. I'd have to know a lot more about your app..
CEWII
October 20, 2009 at 1:28 am
I am experimenting with triggers at the moment, but any data load (via triggers or with something else) is going to cause index fragmentation and page splits. They are loading data 20 records every 100 ms. If the trigger does not finish updating the big table before the next batch gets in, this is going to cause severe performance problems. And this is why we are having really bad performance during the data load due to the indexes on the view. Another bad thing about those indexes (on a view or on a physical table) they are not sequential, even the clustered index.
October 20, 2009 at 1:44 am
This view, is it for reporting?
CEWII
October 20, 2009 at 4:21 pm
No, the view is for the primary app.
October 20, 2009 at 4:52 pm
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
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply