SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Composite Index - Two Different Tables - Order By


Composite Index - Two Different Tables - Order By

Author
Message
curious_sqldba
curious_sqldba
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2938 Visits: 3637
I have a view which is performing a order by on three columns a/b/c, a/b are indexed and is from table 1, c is from table2. How are the indices used in this case? How does sql use two different indices on two different tables? I am trying not to use indexed views or is indexing the view only way? This is more of a general question.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86770 Visits: 41103
I guess my intitial response would be...

1. Why are you doing a sort in a view? In other words, why is the sort required and what/where will the result set be used for? Sorts in a view are generally considered to be a "worst practice" although, like everything else, there are exceptions.

2. Do you know how to read the "Actual Execution Plan"? And, it's ok to say "No".

3. How often does the data in the underlying tables change?

4. I could be wrong but it seems obvious that you're having performance issues. Is it possible to comply with the article at the second link in my signature line below?

As to your general question, a view will use the indexes of the underlying tables just as if you had written a stored procedure or even just a script with the same query in it. If the indexes aren't being used correctly for the script version of the query, then the indexes won't be used correctly for the View. Using an indexed view to materialize the data may be one workaround but that has some serious implications for tables that suffer a good number of inserts or updates. Generally speaking (there are, of course, exceptions but they're more rare than not), the creation of an indexed view for performance is a poor substitute for correct tuning because, ironically, the indexed view will perform better if its code is tuned and the tuned (or just properly written) code may totally negate the need for an indexed view.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87893 Visits: 45274
Honestly, the question is a little too vague to answer. Depends on any filtering done, the joins and a whole lot more.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search