June 23, 2009 at 3:16 pm
I have a work table that has 1 to N records where N could be very high (several hundred million).
I'd like to create several views on this table so that I can chunk up the work. Unfortunately, I don't have control over the app so the views would have to have the same name as the underlying table.
I believe this can be done in Oracle with materailized views and multiple schemas. Is this possible in sql server?
I believe I can use an indexed view but the questions I have is whether or not an indexed view can have the same name as the underlying table. Is this true?
Also, if I created many sql server logins could each account have a view that contained a different slice of the worktable yet have the same name? For example workaccount1 would have a an indexed view called worktable and would have a where clause to restrict it to rows where the id is less than 10 million. Workaccount2 would have a view also called worktable and would be restricted to rows where id is 10,000,001 and less than 20M.
Does this approach sound feasible?
Thanks.
June 23, 2009 at 6:21 pm
If I understand you correctly, you would like to run multiple instances of the application to introduce some element of parallelism to this task - and you are looking for a way to 'fool' each application into thinking that it is operating exclusively on the named table? You are not able to change the application, but you can change the login it uses. The application changes data in the table, and you need that to be reflected back to the 'real' table.
If all that is right, there are several ways to do this, some less acceptable than others. It would help to know which edition of SQL 2005 (it is 2005?) you are running - Enterprise or Standard for example.
As far as I can tell, there is no need to materialize the views - that would just add overhead and lock contention. Indexed views are heavily optimized for read activity (especially aggregates) rather than update activity.
One solution (not necessarily the best) would be to create one view per chunk. Each view would need to be created in the default schema for the login used to access it, and the application would need to access the table by name only - that is, without specifying the schema.
To clarify: if the main table is dbo.A and you create views schema1.A, schema2.A, and schema3.A, the login with schema1 as its default would see table 'A' resolved to view schema1.A
Obviously, the views would need to be defined to access non-overlapping ranges of the main table. The view should be created with the SCHEMABINDING and WITH CHECK_OPTION clauses.
A view on a single table is generally updatable by default, so it need not be persisted first - see CREATE VIEW in Books Online for details concerning view updatability.
Paul
edit: removed quoted text
June 23, 2009 at 6:48 pm
Paul:
Thanks for the response. You hit the nail on the head as to what I'm trying to do.
It's SQL 2005 Enterprise in most cases but could be SQL 2000 as well (I'm supporting multiple customers).
I feel like I'm on the right track and will do some testing now.
Thanks again for the feedback.
Jay
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply