Indexes on Views

This thread has had some considerable prosyletizing and chest-thumping going on in it, and I would like to ask a theoretical question here regarding Sybase's implementation of Views:

I have a client, using a Sybase database in an existing application, who needs some functionality added to it. They use many Views, to the extent that they even have Views constructed from other Views! It has been my understanding, and I glean from most of those posting to this thread, that Views are generally not indexed (and they are not in my client's application). Some seem to indicate that Views CAN be indexed but it is stupid to do so. However, I once directly asked a Microsoft SQL Server support person if Views CAN be indexed, and he indicated NO, they cannot be indexed in the first place. I understand that Sybase's database (which they have renamed to 'Adaptive Server Enterprise') and Microsoft SQL Server (at least version 6.5) have a common heritage, so perhaps they behave the same regarding views.

My client's application appears to have some performance problems with many queries on these Views. I have written Sybase technical support and sales numerous times and searched their online documentation without success
(including chasing many broken links) without yet finding an answer to this
simple question:

• Do QUERIES on VIEWS in Adaptive Server Enterprise use INDEXES, either on the Views themselves or the underlying Tables the Views are formed from? ======================

You'd think that this would be a simple question, or at least one that someone at Sybase could respond to intelligently. After several weeks of trying intermittently to find an intelligent person at Sybase, however, I am still at a loss. The possibilities seem to be:

1. Nobody at Sybase knows the answer to that simple question. 2) Nobody at Sybase cares about their customers enough to pass my question along to someone who might know the answer to it.

After such pathetic experience with Sybase technical support, I am recommending to my client that they convert their entire application to Microsoft SQL Server and abandon Sybase entirely. It's now plainly clear why Sybase has so little market share, and I will never recommend to any other clients that they use Sybase or continue to use it if they already are.

However, I am still very curious from a theoretical perspective just what happens when a View is queried. Does a query on a View use the indexes on the underlying tables from which the View is constructed, or not? If queries on Views do not use the indexes of the underlying tables then the only value of Views is for rather small recordsets or in cases when the every record
(perhaps not every field, though) is returned by the query. Otherwise you'd
be better off querying the tables directly because their indexes will speed the process greatly.

Since I'll be adapting Microsoft SQL Server for my client, I'd appreciate it if my question (do QUERIES on VIEWS use INDEXES on the underlying TABLES the VIEWS are formed from?) could be answered specifically for that platform as well as theoretically (I would think that theoretically, the answer would be 'YES', but that's just a logical perspective). If anyone happens to know how Sybase behaves in this regard too, I'd be interested in learning that just for curiosity's sake, but I am definitely abandoning that dinosaur completely.

Regards,

Karl Simanonok
karl_at_advantasolutions.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Apr 18 2000 - 00:00:00 CEST