October 24, 2016 at 1:01 pm
Hi all,
Currently we are developing a new application for our company and I got a question related to the database architecture. We one database (DatabaseA) that is our main database, our main application write and reads to that database. Now we are creating an web application so customers can view data related from that system. Now I have the following idea (also from security sight):
Create a new database (DatabaseB) and create only views in that database (And maybe one or two tables having data merged from DatabaseA for easy reading) and in those views there are SELECT statements that SELECT data from DatabaseA.
Now the question is:
Will there be a huge performance impact if I query DatabaseA from DatabaseB and does it effect even more if I put them in views or is this performance impact close to none? Tables we are going to SELECT from can have up to 40.000.000 million records.
I was looking in to indexed views and that seems to impact peformance of the query itself not the performance of reading cross database. (This is where I can be wrong...)
Thanks in advance!
October 24, 2016 at 1:19 pm
Why do you want to make a second database? You can restrict access to certain tables/views without making a second database. Performance wise querying cross database shouldn't be a problem but the bigger concern would be that if your server set up changes IE one of the databases is moved all the views will break.
October 24, 2016 at 1:22 pm
.Patrick (10/24/2016)
Hi all,Currently we are developing a new application for our company and I got a question related to the database architecture. We one database (DatabaseA) that is our main database, our main application write and reads to that database. Now we are creating an web application so customers can view data related from that system. Now I have the following idea (also from security sight):
Create a new database (DatabaseB) and create only views in that database (And maybe one or two tables having data merged from DatabaseA for easy reading) and in those views there are SELECT statements that SELECT data from DatabaseA.
Now the question is:
Will there be a huge performance impact if I query DatabaseA from DatabaseB and does it effect even more if I put them in views or is this performance impact close to none? Tables we are going to SELECT from can have up to 40.000.000 million records.
I was looking in to indexed views and that seems to impact peformance of the query itself not the performance of reading cross database. (This is where I can be wrong...)
Thanks in advance!
The impact on performance for normal views is none or close to none. However, you might find yourself with more maintenance work because you would have 2 databases. Indexed views will improve performance of reads but decrease the performance on writes, because the data is written in the tables and the views (written twice or more depending on indexes).
Have you thought on creating a new schema for the views and handle permissions using schemas.
Or maybe you're concerned about performance being affected by queries, for that, you might want to have a reporting database or a data warehouse where you store your information every certain time to reduce impact on main system.
October 24, 2016 at 1:23 pm
.Patrick (10/24/2016)
Hi all,Currently we are developing a new application for our company and I got a question related to the database architecture. We one database (DatabaseA) that is our main database, our main application write and reads to that database. Now we are creating an web application so customers can view data related from that system. Now I have the following idea (also from security sight):
Create a new database (DatabaseB) and create only views in that database (And maybe one or two tables having data merged from DatabaseA for easy reading) and in those views there are SELECT statements that SELECT data from DatabaseA.
Now the question is:
Will there be a huge performance impact if I query DatabaseA from DatabaseB and does it effect even more if I put them in views or is this performance impact close to none? Tables we are going to SELECT from can have up to 40.000.000 million records.
I was looking in to indexed views and that seems to impact peformance of the query itself not the performance of reading cross database. (This is where I can be wrong...)
Thanks in advance!
40 million million is a huge number of rows. I'd be very careful with any web application which touches that table.
But putting your queries in views in another database on the same instance should not have any noticeable additional overhead to having those same queries in your main DB.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 24, 2016 at 1:59 pm
First of all thanks for the answers.
We are doing the second database because we don't want our main database to be put in to use by other applications that our main application. We know that migration from one database to another server will grow to be a pain in the *** when it happens but for the upcoming future (2+ years) we don't see that happen.
Currently we are running a semi data warehouse though the data stored in there is of no concern to the web application we are currently creating. (And so the data warehouse is not an option at this time).
40mil in one table is indeed allot but thats one of the reasons we want to create views cause the they only need about 10mil of those records. (its dynamic and there are different kind of rows added each day).
I just read about scheme's (Didn't know they existed :ermm:..)
That is also a way to run, but then again the new scheme would be directly on to our base tables where the data is also written (And not with the most pretty column names, sadly we can not edit those names).
So if I am getting this correctly, as long as the views from databaseB is on the same instance/server as databaseA, although if one of the database is moved we are pretty much f*cked on peformance and whole lot of work to get it working again?
October 24, 2016 at 2:53 pm
Yes so if you made a new schema in the same database and put all the views for your web app in that schema and you would give your web app read access to that schema but the web app wouldn't have any visibility to the base tables or any other data in the database. And yes if you are using extensive cross database queries in your views you will be in for some work if you end up moving servers or a database name changes or what not that wouldn't be an issue with schemas.
October 24, 2016 at 3:46 pm
40mil in one table
That's manageable. But your original post mentioned 40 miliion million = 40 000 000 000 000 = 40 trillion.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 26, 2016 at 2:32 pm
So I created all the views and there working great (even query's that uses UNION ALL did not expect that :-)). However I came to the following and I am not sure how to deal with it:
The views are created me as DB Admin has access to all the databases in the instance, however the web user only has access to the views I just created. Though when I run the views on the web user I get an access denied because the user as no rights to use the SELECT statement on the underlying table.
Is it possible the create an access (Perhaps with a scheme) that allow the web user to run the view and allow that the underlying SELECT statement is run on the base tables without giving the user actual rights on those tables (there is many more information stored in those tables where the web user just don't need access to).
Again: Thanks in advance!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply