November 29, 2005 at 11:18 pm
Hi,
I am maintaining an application having four copies one at the purchase office and others at various sales Outlets. Now, i have created individual database for every entity(purchase office and sales outlets) meaning each sales outlets has it's own databases where all the master data and it's order/sales details are maintained.
Dbs
1. dbPurchaseOffice
tblProductMaster
tblPurchaseOrders
tblSalesOrders
2. dbSalesOutlet1
tblProductMaster
tblPurchaseOrders
tblSalesOrders
3. dbSalesOutlet2
tblProductMaster
tblPurchaseOrders
tblSalesOrders
Here structure of each db is same and tblProductMaster is having same data for each db. Generally Each physical has it's own db, but some times i configure multiple application at some place. In purchase office i have configured all the apps. and data of each outlet is brought in the evening and added in it's db. so that i can just have a look at their working. We do not change any data.
and products can only be added through the purchase office application. and then these details(other around 10 tables) needs to be transferred to each sales outlet and these outlets are not connected through network, therefor i need to get them transferred using cd.
Here, for eg. the Product Master table exits in each database thus extra space is consumed. and also is it good practice to maintain same table with same data in multiple databases on same system.
1. I am thinking to seperate the master tables in to new db. and another db for managing order/sales details. so when i have to transfer the master changes i only need to backup the master data db and restore at the sales outlet.
I am mainly worried for the performance when we have any query which reference two tables in different dbs.(While showing orders which product names)
Please suggest me any solution for this situation and whether it's right practice.
Thanks,
Maulik
November 30, 2005 at 7:59 am
Though the transfering of data will be easier by separating the master tables in a new db, it may cause the following problems:
1) data integrity. FK definition is not allowed cross dbs.
2) you need to rewrite your application use dbname.owner.tablename to reference any tables in the new db
3) as you said, perfromance may or may not be an issue.
I prefer not to make the change. You can wirte a simple procedure to import/export the data in the master tables for the transfering purpose. The export SP call bcp to export data to files; The import SP bcp in the data into temp tables, and check against the data in the existing tables, and then insert/modify/delete accordingly
November 30, 2005 at 8:30 am
In my company we ended up having to split one massive database into multiple customer databases and here is some experience.
Querying different databases on the same server, no performance hit. Querying across servers is network dependant so I don't recommend it.
Foreign keys can be implemented as triggers instead of constraints. Impact should be negligable.
To avoid the naming of database.dbo.table, you can create a view of the same name and the FK triggers can reference this view.
I recommend keeping master data in one database per server as it is easier to keep it in synch in fewer places. Also RedGate's SQL bundle has tools to help you synchronize that data.
Good luck
November 30, 2005 at 10:31 am
Thanks peterhe & Brian,
The problem with the current method is that, in the master tables i have defined primary keys with identity and if i use import/export method, i need to alter table definition to remove identity, clear the data from master tables and then import data and then again chagne the table definition to add identity.
I have around 10 such tables and will have write code for all tables, but if there is no alternative will have to do this.
Brian, If i create a seperate db for master data and If i am executing a query having two tables from different db,
SELECT db1.dbo.tblSalesOrderTrans.salesOrderId,db2.dbo.tblProductMaster.productName FROM db1.dbo.tblSalesOrderTrans INNER JOIN db2.dbo.tblProductMaster ON db1.dbo.tblSalesOrderTrans.productId = db2.dbo.tblProductMaster.productId
Do you think it won't create any performance overhead ?
Thanks Again,
Maulik
November 30, 2005 at 12:24 pm
SELECT db1.dbo.tblSalesOrderTrans.salesOrderId,
db2.dbo.tblProductMaster.productName
FROM db1.dbo.tblSalesOrderTrans INNER JOIN db2.dbo.tblProductMaster
ON db1.dbo.tblSalesOrderTrans.productId = db2.dbo.tblProductMaster.productId
Do you think it won't create any performance overhead ?
As long as they are on the same server. You should get no noticeable performance hits. Some one discussed this on another forum at another site, and basically said not a problem, and so far in my use I have not seen a problem.
Theoretically if you had enough users hitting the same query at the same time from different databases, you might get trouble, but I am talking there well over a thousand, or maybe a million. Of course is someone does an update in the master table and leaves an open transaction you will have a problem, but that is true with having the data in the same database. In fact it is safer in the separate database, because there are fewer times the updates have to be done.
If you want maximum performance you could set your master database read-only when updates are not in process and this may help. You can also create a view called tblProductMaster as follows:
CREATE VIEW tblProductMaster AS SELECT * FROM db1.dbo.tblSalesOrderTrans
and your existing code needs no change. Furthermore you could make this an indexed view which would effectively put a duplicate copy of the data in the local database which would be performant and update with updates to your master table. Of course those updates would be slower.
BTW, some coding tips use table aliases: e.g.
SELECT so.salesOrderId, pm.productName
FROM db1.dbo.tblSalesOrderTrans so INNER JOIN db2.dbo.tblProductMaster pm
ON so.productId = pm.productId
is much easier to read.
Hope that helps.
November 30, 2005 at 9:28 pm
Brian,
Thanks for ur suggestions & will go ahead with this method.
Maulik
December 1, 2005 at 7:18 am
If "identity" is the main reason for you to separate the master database, you don't need to to do it at all.
In import/export, you do not need to drop the identity column. You can turn off identity by SET IDENTITY_INSERT tablename OFF. Then you can explicitly insert value for the identity column
I have dozens of import/export stored procedures doing the similar thing. The data sources are excel files instead of another database. The tables all have identity columns.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply