Views

Question

Views

Tara-1044200

SSCoach

Points: 15951
More actions
June 16, 2010 at 6:42 am

#223902

I have database with 6 yrs of data and now i am splitting that into 2 different database's having 3yrs of data in each because we use only latest 3 yrs of data and the old database will be marked as read only.
For the above process i want to make all my views presented to the users in the same way as before but i have to modify them so as to point older and new databases both. what is the best approach ?
do i have to change like this..
Current View
ALTER VIEW [dbo].[Employee]
AS
Select * from EmployeeR
union all
Select * from EmployeeB
Expected View:
ALTER VIEW [dbo].[Employee]
AS
Select * from EmployeeR
union all
Select * from EmployeeB
UNION ALL
Select * from OldDB.dbo.EmployeeR
union all
Select * from OldDB.dbo.EmployeeB
Is there any other method to get better peroformance ? Do you think i get gud performance by listing all columns instead of "select*" ?

Viewing 5 posts - 1 through 5 (of 5 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Have you heard about table partitioning?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Tara-1044200 (6/16/2010)
I have database with 6 yrs of data and now i am splitting that into 2 different database's having 3yrs of data in each because we use only latest 3 yrs of data and the old database will be marked as read only.

Looks like a good place to apply partitioning.

Expected View:
ALTER VIEW [dbo].[Employee]
AS
Select * from EmployeeR
union all
Select * from EmployeeB
UNION ALL
Select * from OldDB.dbo.EmployeeR
union all
Select * from OldDB.dbo.EmployeeB

This is how partitioned views used to work. You could also do it this way. Why are you moving this data in a different database? Wouldn't it be easier to keep everything in the same DB and use differente filegroups for older data?

Do you think i get gud performance by listing all columns instead of "select*" ?

Absolutely not.

-- Gianluca Sartori

Tara-1044200 SSCoach Points: 15951 More actions · Answer 3

All the databases are partitioned but still we want to split them for which we have reasons to do. The reason is that we backup and restore database from stage to production and it would be easier for us to play with small data.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

any other thoughts on improving performance on my views ?