How can I optimize a join with multi-table sort in T-SQL?

Question

How can I optimize the following query?

   SELECT TOP 50 *
     FROM A 
LEFT JOIN B ON A.b_id = B.id 
 ORDER BY A.number, B.name DESC

I created a non-clustered index on (A.number asc, A.creation_date desc), which includes all columns from A, and another non-clustered index on B.origination_date desc, which includes all columns from B (except text columns). Neither of these indices are used, according to the actual execution plan from SQL Server Management Studio.

The thing that seems to be causing the performance hit is the B.origination_date sort. When I examine the actual execution plan in SQL Server Management Studio, I see that "Top N Sort" on these three fields takes up 91% of the execution time. If I drop off the sort on B.origination_date, the query completes almost instantaneously, using the index on A.

Edit: Updated the query to provide a better, simpler example.

Answer 1

I would guess A.number like '%%' is your problem. What is this intended to do? You should not be using a like with a wildcard as the first character if you want to use the indexes. As this stands it appears to be filtering for nothing as tere is nothing between the wildcards.

Answer 2

Since you're sorting on columns from two different tables, SQL Server has to join the tables and then do the sort. Once the tables are joined, the indexes on the individual tables are no help to the sort. An indexed view might be your best bet.