 | |
Updating an Instance from the production database
microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools
 |
| | Thread Tools | Display Modes |
#51
 
| |||
| |||
|
Re: Updating an Instance from the production database -
05-30-2008
, 05:10 PM
|
I have a website that will be looking at the subscriber database. The production database will have multiple tables that will need to be combined together and put into the subscriber database as a single table. What I am hoping to do is reduce time for the website to query the databases from the subsciber. The website will look at the subscriber database and have a simple SELECT statement to grab the data. The replication, publisher, will have the complex query when it transfers the data to the subscriber, meaning the INNER JOINS, GROUP BYs, etc... So the thought was taking a view and transfering the data into a table. Think of it like the publisher database has 3 tables that are joined. I want to combine these tables and place them into the subscriber database. That way my website doesn't have to do the Inner Join part when users view the data. Yes this is simple and it shouldn't have too much of an impact on speed, but I have way more complex Inner Joins that I will be using that will slow down website viewing. |
1) Consider this as a typical ETL thing, and use SSIS (which I cannot
assist with).
2) Use replication, and have triggers on the subscriber to do the
transformation.
3) As Tom suggested, use indexed views on the website. I would also
add the NOEXPAND hint to the queries, so that the views actually
get used.
What I would recommend against is to do the transformation on the
production database, so that could slow down throughput. (Assuming
that this is a busy OLTP system.)
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#52
| |||
| |||
|
|
I have a website that will be looking at the subscriber database. The production database will have multiple tables that will need to be combined together and put into the subscriber database as a single table. What I am hoping to do is reduce time for the website to query the databases from the subsciber. The website will look at the subscriber database and have a simple SELECT statement to grab the data. The replication, publisher, will have the complex query when it transfers the data to the subscriber, meaning the INNER JOINS, GROUP BYs, etc... So the thought was taking a view and transfering the data into a table. Think of it like the publisher database has 3 tables that are joined. I want to combine these tables and place them into the subscriber database. That way my website doesn't have to do the Inner Join part when users view the data. Yes this is simple and it shouldn't have too much of an impact on speed, but I have way more complex Inner Joins that I will be using that will slow down website viewing. |
1) Consider this as a typical ETL thing, and use SSIS (which I cannot
assist with).
2) Use replication, and have triggers on the subscriber to do the
transformation.
3) As Tom suggested, use indexed views on the website. I would also
add the NOEXPAND hint to the queries, so that the views actually
get used.
What I would recommend against is to do the transformation on the
production database, so that could slow down throughput. (Assuming
that this is a busy OLTP system.)
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#53
| |||
| |||
|
|
I have a website that will be looking at the subscriber database. The production database will have multiple tables that will need to be combined together and put into the subscriber database as a single table. What I am hoping to do is reduce time for the website to query the databases from the subsciber. The website will look at the subscriber database and have a simple SELECT statement to grab the data. The replication, publisher, will have the complex query when it transfers the data to the subscriber, meaning the INNER JOINS, GROUP BYs, etc... So the thought was taking a view and transfering the data into a table. Think of it like the publisher database has 3 tables that are joined. I want to combine these tables and place them into the subscriber database. That way my website doesn't have to do the Inner Join part when users view the data. Yes this is simple and it shouldn't have too much of an impact on speed, but I have way more complex Inner Joins that I will be using that will slow down website viewing. |
1) Consider this as a typical ETL thing, and use SSIS (which I cannot
assist with).
2) Use replication, and have triggers on the subscriber to do the
transformation.
3) As Tom suggested, use indexed views on the website. I would also
add the NOEXPAND hint to the queries, so that the views actually
get used.
What I would recommend against is to do the transformation on the
production database, so that could slow down throughput. (Assuming
that this is a busy OLTP system.)
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#54
| |||
| |||
|
|
I have a website that will be looking at the subscriber database. The production database will have multiple tables that will need to be combined together and put into the subscriber database as a single table. What I am hoping to do is reduce time for the website to query the databases from the subsciber. The website will look at the subscriber database and have a simple SELECT statement to grab the data. The replication, publisher, will have the complex query when it transfers the data to the subscriber, meaning the INNER JOINS, GROUP BYs, etc... So the thought was taking a view and transfering the data into a table. Think of it like the publisher database has 3 tables that are joined. I want to combine these tables and place them into the subscriber database. That way my website doesn't have to do the Inner Join part when users view the data. Yes this is simple and it shouldn't have too much of an impact on speed, but I have way more complex Inner Joins that I will be using that will slow down website viewing. |
1) Consider this as a typical ETL thing, and use SSIS (which I cannot
assist with).
2) Use replication, and have triggers on the subscriber to do the
transformation.
3) As Tom suggested, use indexed views on the website. I would also
add the NOEXPAND hint to the queries, so that the views actually
get used.
What I would recommend against is to do the transformation on the
production database, so that could slow down throughput. (Assuming
that this is a busy OLTP system.)
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#55
| |||
| |||
|
|
I have a website that will be looking at the subscriber database. The production database will have multiple tables that will need to be combined together and put into the subscriber database as a single table. What I am hoping to do is reduce time for the website to query the databases from the subsciber. The website will look at the subscriber database and have a simple SELECT statement to grab the data. The replication, publisher, will have the complex query when it transfers the data to the subscriber, meaning the INNER JOINS, GROUP BYs, etc... So the thought was taking a view and transfering the data into a table. Think of it like the publisher database has 3 tables that are joined. I want to combine these tables and place them into the subscriber database. That way my website doesn't have to do the Inner Join part when users view the data. Yes this is simple and it shouldn't have too much of an impact on speed, but I have way more complex Inner Joins that I will be using that will slow down website viewing. |
1) Consider this as a typical ETL thing, and use SSIS (which I cannot
assist with).
2) Use replication, and have triggers on the subscriber to do the
transformation.
3) As Tom suggested, use indexed views on the website. I would also
add the NOEXPAND hint to the queries, so that the views actually
get used.
What I would recommend against is to do the transformation on the
production database, so that could slow down throughput. (Assuming
that this is a busy OLTP system.)
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.