Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Composite Index - Two Different Tables - Order By Expand / Collapse
Posted Friday, August 9, 2013 8:22 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, February 8, 2016 3:54 PM
Points: 1,444, Visits: 3,373

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.
Post #1482790
Posted Monday, August 12, 2013 10:10 PM



Group: General Forum Members
Last Login: Today @ 8:36 PM
Points: 39,672, Visits: 36,812
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Favorite Quotes:
"Has anyone ever told you that a query you have written runs too fast?" - Dwain Camps - 6 Mar 2014

Helpful Links:
How to post code problems
How to post performance problems
Post #1483550
Posted Tuesday, August 13, 2013 1:35 AM



Group: General Forum Members
Last Login: Today @ 2:04 PM
Points: 44,006, Visits: 41,402
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

Post #1483594
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse