2 replaced http://stackoverflow.com/ with https://stackoverflow.com/
source | link

I believe this is a perfect use case for a view, in other words a "virtual table" defined by an SQL query on real tables in the database.

By default a view is generated from the real data every time a query needs to be executed on it, but some RDMSs support views with caching/precomputing/etc. Oracle calls these "materialized views", and SQL Server calls them "indexed views".

To get a little more specific, Improving Performance with SQL Server 2008 Indexed Views says that:

Indexed views can increase query performance in the following ways:

  • Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
  • Tables can be prejoined and the resulting data set stored.
  • Combinations of joins or aggregations can be stored.

You may not need any of the huge performance improvements they claim, but this gives us a concrete idea of what SQL Server's indexed views are capable of, so I think it's fair to assert that an indexed view is functionally equivalent to your option #1 of creating an additional table, while a non-indexed view is functionally equivalent to your option #2 of creating a temporary table every time. Except that it's probably much easier to switch between a regular view and an indexed view than it is to switch between a permanent table and a temporary table, so at the very least you'll probably save yourself a some time experimenting with your options this way.

This SO answerThis SO answer has some more helpful links on the subject.

I believe this is a perfect use case for a view, in other words a "virtual table" defined by an SQL query on real tables in the database.

By default a view is generated from the real data every time a query needs to be executed on it, but some RDMSs support views with caching/precomputing/etc. Oracle calls these "materialized views", and SQL Server calls them "indexed views".

To get a little more specific, Improving Performance with SQL Server 2008 Indexed Views says that:

Indexed views can increase query performance in the following ways:

  • Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
  • Tables can be prejoined and the resulting data set stored.
  • Combinations of joins or aggregations can be stored.

You may not need any of the huge performance improvements they claim, but this gives us a concrete idea of what SQL Server's indexed views are capable of, so I think it's fair to assert that an indexed view is functionally equivalent to your option #1 of creating an additional table, while a non-indexed view is functionally equivalent to your option #2 of creating a temporary table every time. Except that it's probably much easier to switch between a regular view and an indexed view than it is to switch between a permanent table and a temporary table, so at the very least you'll probably save yourself a some time experimenting with your options this way.

This SO answer has some more helpful links on the subject.

I believe this is a perfect use case for a view, in other words a "virtual table" defined by an SQL query on real tables in the database.

By default a view is generated from the real data every time a query needs to be executed on it, but some RDMSs support views with caching/precomputing/etc. Oracle calls these "materialized views", and SQL Server calls them "indexed views".

To get a little more specific, Improving Performance with SQL Server 2008 Indexed Views says that:

Indexed views can increase query performance in the following ways:

  • Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
  • Tables can be prejoined and the resulting data set stored.
  • Combinations of joins or aggregations can be stored.

You may not need any of the huge performance improvements they claim, but this gives us a concrete idea of what SQL Server's indexed views are capable of, so I think it's fair to assert that an indexed view is functionally equivalent to your option #1 of creating an additional table, while a non-indexed view is functionally equivalent to your option #2 of creating a temporary table every time. Except that it's probably much easier to switch between a regular view and an indexed view than it is to switch between a permanent table and a temporary table, so at the very least you'll probably save yourself a some time experimenting with your options this way.

This SO answer has some more helpful links on the subject.

1
source | link

I believe this is a perfect use case for a view, in other words a "virtual table" defined by an SQL query on real tables in the database.

By default a view is generated from the real data every time a query needs to be executed on it, but some RDMSs support views with caching/precomputing/etc. Oracle calls these "materialized views", and SQL Server calls them "indexed views".

To get a little more specific, Improving Performance with SQL Server 2008 Indexed Views says that:

Indexed views can increase query performance in the following ways:

  • Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
  • Tables can be prejoined and the resulting data set stored.
  • Combinations of joins or aggregations can be stored.

You may not need any of the huge performance improvements they claim, but this gives us a concrete idea of what SQL Server's indexed views are capable of, so I think it's fair to assert that an indexed view is functionally equivalent to your option #1 of creating an additional table, while a non-indexed view is functionally equivalent to your option #2 of creating a temporary table every time. Except that it's probably much easier to switch between a regular view and an indexed view than it is to switch between a permanent table and a temporary table, so at the very least you'll probably save yourself a some time experimenting with your options this way.

This SO answer has some more helpful links on the subject.