Would descending index help our View's performance?

12 May 2005 4:57 PM

Wayne Erfling

We store multiple revisions of some data in a single table. When we want the current revision we use a subquery with MAX(revision) in our VIEW.

Our user key and the revision are indexed:

"user key"   ascending
"revision"   ascending

Would changing "revision" to "descending" in the index help SQL Server find the "MAX(revision)" faster?

"Books Online" seems to leave this open in the following paragraph:
The internal algorithms of SQL Server can navigate equally efficiently in both directions on a single-column index, regardless of the sequence in which the keys are stored. For example, specifying DESC on a single-column index does not make queries with an ORDER BY IndexKeyCol DESC clause run faster than if ASC was specified for the index.
So, what about our two-column index?

CREATE VIEW dbo.V_QUOT_MAST_CURR_REVI
AS
SELECT QUOT_MAST.*,
FROM     dbo.QUOT_MAST
              WHERE   ((SELECT MAX(QUOT_REVI) AS MAX_REVI
                                  FROM         QUOT_MAST IQM
                                  WHERE     QUOT_MAST.QUOT_MAIN_ID =
                                                     IQM.QUOT_MAIN_ID)
               = QUOT_REVI)

Thanks in advance!

---Wayne

12 May 2005 5:59 PM

John Bell

Hi

I would expect Books online to be correct, but you don't give enough
information to see if there are other things to help your view such as making
the index clustered or having indexed views. It is also not clear whether
your index is a compound one or single columns.

If you looked at the query plan and statistics it would show if one is being
more efficient.

It is not good practice to use SELECT * in production code.

John

Show quote

"Wayne Erfling" wrote:

> We store multiple revisions of some data in a single table. When we want the current revision we use a subquery with MAX(revision) in our VIEW.
>
> Our user key and the revision are indexed:
>
> "user key"   ascending
> "revision"   ascending
>
> Would changing "revision" to "descending" in the index help SQL Server find the "MAX(revision)" faster?
>
> "Books Online" seems to leave this open in the following paragraph:
>   The internal algorithms of SQL Server can navigate equally efficiently in both directions on a single-column index, regardless of the sequence in which the keys are stored. For example, specifying DESC on a single-column index does not make queries with an ORDER BY IndexKeyCol DESC clause run faster than if ASC was specified for the index.
> So, what about our two-column index?
>
> CREATE VIEW dbo.V_QUOT_MAST_CURR_REVI
> AS
> SELECT QUOT_MAST.*,
> FROM     dbo.QUOT_MAST
>               WHERE   ((SELECT MAX(QUOT_REVI) AS MAX_REVI
>                                   FROM         QUOT_MAST IQM
>                                   WHERE     QUOT_MAST.QUOT_MAIN_ID =
>                                                      IQM.QUOT_MAIN_ID)
>                = QUOT_REVI)
>
> Thanks in advance!
>
> ---Wayne
>
>
>
>
>

12 May 2005 6:43 PM

Wayne Erfling

I included both the index columns and the view itself in my post.

What more should I include?

I thought of a clustered index and an indexed view, but at present SQL
Server is able to reach down into my view for other columns indexed on the
underlying table, and I'm not sure if I would lose that by using an indexed
view. And of course the same question I asked for a regular index (will
descending help?) applies to a clustered index.

That is why my question is only "will descending help MAX() with a
two-column index?"

I would appreciate any specific knowledge on the question I am trying to
pursue.

Thanks!

---Wayne

Show quote

"John Bell" <jbellnewspo***@h0tmail.com> wrote in message
news:2270B55B-3312-453D-B5A2-4FA471FE1DAB@microsoft.com...
> Hi
>
> I would expect Books online to be correct, but you don't give enough
> information to see if there are other things to help your view such as
> making
> the index clustered or having indexed views. It is also not clear whether
> your index is a compound one or single columns.
>
> If you looked at the query plan and statistics it would show if one is
> being
> more efficient.
>
> It is not good practice to use SELECT * in production code.
>
> John
>
>
> "Wayne Erfling" wrote:
>
>> We store multiple revisions of some data in a single table. When we want
>> the current revision we use a subquery with MAX(revision) in our VIEW.
>>
>> Our user key and the revision are indexed:
>>
>> "user key"   ascending
>> "revision"   ascending
>>
>> Would changing "revision" to "descending" in the index help SQL Server
>> find the "MAX(revision)" faster?
>>
>> "Books Online" seems to leave this open in the following paragraph:
>>   The internal algorithms of SQL Server can navigate equally efficiently
>> in both directions on a single-column index, regardless of the sequence
>> in which the keys are stored. For example, specifying DESC on a
>> single-column index does not make queries with an ORDER BY IndexKeyCol
>> DESC clause run faster than if ASC was specified for the index.
>> So, what about our two-column index?
>>
>> CREATE VIEW dbo.V_QUOT_MAST_CURR_REVI
>> AS
>> SELECT QUOT_MAST.*,
>> FROM     dbo.QUOT_MAST
>>               WHERE   ((SELECT MAX(QUOT_REVI) AS MAX_REVI
>>                                   FROM         QUOT_MAST IQM
>>                                   WHERE     QUOT_MAST.QUOT_MAIN_ID =
>>                                                      IQM.QUOT_MAIN_ID)
>>                = QUOT_REVI)
>>
>> Thanks in advance!
>>
>> ---Wayne
>>
>>
>>
>>
>>

13 May 2005 11:46 AM

John Bell

Hi

See Steves reply. The following is a good example on how to post
http://www.aspfaq.com/etiquette.asp?id=5006, wordy descriptions are
prone to ambiguity and miss-interpretation, DDL is clear and a working
example.

John