CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008t-sql
user362283
user362283

Reputation: 95

views based on a view is slow

I have created views on the SQL Server 2005 database and this view is based on views provided by a third party. I'm displaying them in our application via JDBC connection and they seems to be very slow. I tried another method and created them as a table by using SQL (select into) command in this case viewing the data in the application is fast. Can you advise me please about the best approach.

How can I improve the application performance?

  1. Indexed view.
  2. Use SSIS to get them into our database which is also an SQL Server 2008 R2.
  3. What else?

Upvotes: 1

Views: 2428

Answers (3)

Conrad Frix
Conrad Frix

Reputation: 52675

How can I improve the application performance?

  1. Indexed view.
  2. Use SSIS to get them into our database which is also an SQL Server 2008 R2.
  3. What else?

Another option not mentioned is

Don't use views

My experience is that non-indexed views typically make things slower and indexed views are difficult to create due to restrictions.

If you encounter some problem where you think you need to use a View try using a CTE or inline view instead.

Upvotes: -1

Randy Minder
Randy Minder

Reputation: 48522

The best way I have to found to improve the performance of queries (including views) is to take a look at the generated query plans produced by SSMS. The first thing I look for is Index or Table scans. When you see any of those, there is a good chance an index is needed, and often times you'll need to INCLUDE columns in the index for the index to actually get used.

Indexed views can give a tremendous performance boost. However, Microsoft has laid so many restrictions on them it's often very difficult to actually use them. They will also affect insert / update / delete performance on the base tables. So there is a trade-off.

I doubt that creating a separate table is a viable long-term and scaleable approach unless the plan is to execute these queries a very small number of times. The process of copying this data can be extremely resource intensive.

Upvotes: 2

Cade Roux
Cade Roux

Reputation: 89741

You should understand where the slowness is first.

Materializing the data into a table obviously means later selects can be faster, but the copying may be slow. If the data is slowly changing, that is certainly a design approach which can work.

Indexed views have restrictions and all indexes will affect write performance, since they need to be updated when data changes.

It sounds like two servers could be in play here. It's not clear if the views you created are on your server or the 2005 server. If you create a view in one server on views in another linked server, it is possible that more data is being pulled between the servers than is strictly necessary (compared to all the views being on the same server and being able to be optimized together).

Upvotes: 1

Related Questions