How to improve view's performance?

How to improve view's performance?

Post by Billy La » Thu, 01 Oct 1998 04:00:00



I think view can give us many good features such as providing aggregate
function as a logical field.

But, view doesn't support indexs which would make the performance very bad.
How can we improve it?

Thanks
--
Best Regards,

Billy Lau

 
 
 

How to improve view's performance?

Post by Ruud de Kote » Fri, 23 Oct 1998 04:00:00


Hi Billy,

With some reluctance I answer to your posting. Being an Oracle-man, and
working only recently with SQL Server, I dont't feel I am the right person
to answer you. On the other hand, I do see your posting without answers for
quite a long time.

The positive site is that I do know how relational systems work. Supposing
(;-)) SQL Server works according to relation theory, it doesn't seem a
correct statement that views do not support indexes. A view is a runtime
object (a virtual table, as the fancy term is) that is constructed every time
you query it from the physical tables present in your database. In MS terms:
the view is a recordset constructed from your tables as you ask for it. The
only difference in query execution is that part of the SQL statement is stored
within the RDBMS (the view definition) and combined with the statement you
(or your program) has created. Data retrieval from a view can therefore be
analyzed and influenced just as any other query, including the use of indexes.

Incidently, this also implies that views can not take away the problems that
are caused by complicated data models. If you write a view hoping that the
performance of a multi-table join will improve after you have hidden it in the
view, you will not have any success (I don't say you are doing this, but I have

seen quite some people hoping to solve performance problems in doing so).

So, final advice: check your view execution for index use, and if this is not
what you hope it is, add the appropriate indexes.

In conclusion a call to any SQL Server gurus: please correct my advice if
SQL Server works differently!!!

Regards,

Ruud de Koter

--------------------------------------------------------------------------------

Ruud de Koter
Senior Software Engineer
HP OpenView Software Business Unit
IT Service Management Operation
Telnet:                547 15 89
Telephone:         +31 - 020 514 15 89
Telefax:              +31 - 020 514 15 90

Internet: http://www.openview.hp.com/itsm
Intranet: http://ovweb.bbn.hp.com/itservicemanager
--------------------------------------------------------------------------------


> I think view can give us many good features such as providing aggregate
> function as a logical field.

> But, view doesn't support indexs which would make the performance very bad.
> How can we improve it?

> Thanks
> --
> Best Regards,

> Billy Lau



 
 
 

1. Index materialized views - improve performance?

Does anyone know how much performance is affected if views are materialized
for data that sees a lot of insert/update activity?

We have a client who is complaining of slow speeds and the main query that
is being used from the page where they are facing the problem contains joins
with multiple views that in turn have multiple joins in them. All this is
not really avoidable and I do not have the time to re-write the queries as
then they will have to go through a QA process and what not.

I have tuned the queries and created all the indexes that are appropriate
and performance is still slow. I feel that materializing the views will
help. But am hesistant to do so because I have a one-shot deal at this and
am worried about slowing down performance on the entire website and then I
will be in real trouble!

Has anyone out there done this sort of thing before? I know MS advises
against it but ......

Thanks,

Rohan

2. NT user group

3. Improving performance of view

4. NewEra Upgrade from 2.x to 3.x

5. ADO's Performance vs DAO's Performance

6. My Access SQL Doesn't seem to work

7. Improved SQL query/view generating tool for FoxPro 6.0

8. Using JOINS improve performance?

9. Improving performance of batch cycle

10. HOWTO: Improve SQL 6.5 Performance for very simple SQL

11. Improved performance with MS-SQL server?