从(SQL)链接服务器视图缓存表的最佳方法是什么? [英] What is the best way to cache a table from a (SQL) linked server view?

问题描述

我有一个从/通过链接服务器检索数据的视图，因此当通过该视图调用链接表时，我将使用select * from linkerserver.linkedtable.我的查询成功了，我得到了一张信息表.

I have a view that retrieves data from/through a linked server, thus when calling the linked table through the view I will use select * from linkerserver.linkedtable. My query works and I get a table of info back.

但是，我担心使用链接服务器时的速度和争用，尤其是当有多个用户访问/调用使用链接服务器视图的 sp (存储过程)时.

I am however concerned about speed and contention when using a linked server especially if there are multiple users accessing/calling a sp (Stored Procedure) that makes use of the Linked Server View.

我不确定如何解决此问题，甚至是有问题吗? (使用链接的服务器视图时，会不会出现面孔速度或竞争问题?).

I am unsure as to how I could solve this problem or even if it is a problem? (Will one face speed or contention issues when using a linked server view?).

解决此问题的选项可能是:

The options to get around this could be:

1. 创建一个持久性临时表(## MYTEMP)，该表与视图中的数据/表重复，但在本地存在.
2. 然后将所有调用定向到本地存在的此临时表.
3. 或在本地创建一个重复的表，并在一天的第一个调用后触发一个触发器来填充该表.

任何有关如何解决此问题或最佳用例的建议将不胜感激.

Any advise about how to solve this problem or best use case would be greatly appreciated.

推荐答案

最好的办法是要求其他团队在更新数据后向您推送数据.如果他们仅添加新记录，这就是您需要添加到表中的全部内容.如果他们也正在更新数据，则可以使用合并"，以便仅处理新记录和更改.

The best bet would be to ask the other team to push you the data after they update it. If they are only adding new records this is all you need added to your table. If they are also updating data then they could use Merge so that only new records and changes are processed.

我的第二个选择是在服务器上有一个表，一旦它们完成数据加载，就从链接的服务器中删除并重新拉表(或使用合并").您需要为此与他们的团队进行协调.

My second choice would be to have a table on your server that gets dropped and re-pulled (or use Merge) from the linked server once they do their data load. You would need to coordinate with their team on this.

我的最后选择是让SQL代理每晚提取一次此数据.除非源数据也每晚更新一次，否则这将是我的第二选择.如果他们在午夜至凌晨2点之间进行处理，我的工作将在凌晨3点或凌晨4点开始.

My last choice would be to have this data pulled nightly by SQL Agent. Unless the source data was also updated nightly, then this would be my second choice. If they processed between midnight and 2 am I would have my job run at 3 or 4am.

这篇关于从(SQL)链接服务器视图缓存表的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！