本地与全局临时表 - 何时使用什么? [英] Local vs Global temp tables - When to use what?

问题描述

我有一个报告，它在执行时使用 my_report_user 用户名连接到数据库.报告的最终用户可能有很多.并且在每次执行时，都会使用 my_report_user 建立一个到数据库的新连接(没有连接池)

I have a report which on execution connects to the database with my_report_user username. There can be many end-users of the report. And in each execution a new connection to the database will be made with my_report_user (there is no connection pooling)

我有一个结果集，我认为它只能创建一次(可能是在第一次运行报告时)，其他报告执行可以重用这些东西.基本上每个报告执行都应该检查这个结果集(存储为临时表)是否存在.如果它不存在，则创建该结果集，否则仅重用可用的内容.

I have a result set which I think can just be created once (may be on the first run of the report) and other report executions can just reuse that stuff. Basically each report execution should check whether this result set (stored as temp table) exists or not. If it does not exist then create that result set else just reuse whats available.

我应该使用 local 临时表 (#) 还是 global 临时表 (##)?

Should I use local temp tables (#) or global temp tables (##)?

有没有人尝试过这样的东西，如果是，请告诉我我应该关心什么?(几乎同时运行报告等)

Has anyone tried such stuff and if yes, please let me know what all things should I care about? (Almost simultaneous report runs, etc.)

我使用的是 Sql-Server 2005

I am using Sql-Server 2005

推荐答案

都没有

如果您想在您自己的控制下缓存结果结果集，那么您不能使用任何类型的临时表.您应该使用普通用户表，存储在 tempdb 中，甚至有自己的结果集缓存数据库.

If you want to cache result result sets under your own control, then you cannot use temp tables, of any kind. You should use ordinary user tables, stored either in tempdb or even have your own result set cache database.

临时表、bot #local 和 ##shared 的生命周期由连接控制.如果您的应用程序断开连接，临时表将被删除，这与您描述的情况不符.

Temp tables, bot #local and ##shared have a lifetime controlled by the connection(s). If your application disconnect, the temp table is deleted, and this does not work well with what you describe.

真正困难的问题是在并发运行下填充这些缓存的结果集而不会混淆(最终结果集包含来自并发报告运行的重复项，这些项目都被认为是第一次"运行).

The real difficult prolem will be to populate these cached result sets under concurent runs without mixing things up (end up with result sets containing duplicate items from concurent report runs that both believed are the 'first' run).

顺便说一下，SQL Server Reporting Services 已经开箱即用.您可以缓存和共享数据集，也可以缓存和共享报告，它已经可以运行并为您进行了测试.

As a side note SQL Server Reporting Services already does this out-of-the-box. You can cache and share datasets, you can cache and share reports, it already works and was tested for you.

这篇关于本地与全局临时表 - 何时使用什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！