sql server - SQL Get first match of a ranked sort performance tuning -

i have query take distinct list of values , first match based off ranked sort works small scale work. problem when apply large scale work (35,000 unique entries against 40000 records) query clocks , hangs.

as experiment did first half of query , took 3 - 4 minutes on 40k rows (see below mean). changed left joins inner joins slight performance enhancement, running out of ideas on else can do.

note if @ possible want stay non-proprietary standards based sql as possible since changing ms sql server mysql or oracle in near future , don't want needless rewrites.

query

select q.* ( select      a.name   , b.id   , b.status   , b.rank  testa left join testb b on b.name = a.name    b.rank = (             select min(b2.rank)              testb b2              b.name = b2.name            ) ) q -- first half mean above line here     q.id = (         select min(q2.id)          (             select                  a.name               , b.id               , b.status               , b.rank              testa             left join testb b             on b.name = a.name                            b.rank = (                         select min(b2.rank)                          testb b2                          b.name = b2.name                        )         ) q2          q.name = q2.name        ) ; 

schema: of test data

create table testa (`name` varchar(4)) ;  insert testa (`name`) values ('bob'), ('john'), ('will') ;  create table testb (`name` varchar(4), `id` int, `status` varchar(18), `rank` int) ;  insert testb (`name`, `id`, `status`, `rank`) values ('bob', 11, 'happy', 1), ('bob', 12, 'active', 1), ('bob', 93, 'inactive', 2), ('bob', 94, 'canceled', 2), ('bob', 95, 'pending deletion', 3), ('john', 32, 'pending activation', 10), ('john', 24, 'inactive', 4), ('will', 555, 'vacation', 5), ('will', 511, 'vacation', 5), ('will', 661, 'on hold', 9) ; 

here fiddle made of schema , code

http://sqlfiddle.com/#!2/e91a8/3/0

first thing i'd mention joining on name , assuming haven't added index column (schema not have index defined). if can rather use auto increment id in tablea , use foreign key in tableb, using join id speed query.

i'd recommend run profiler in sql server management studio (ssms) while running query if have admin privileges. can check out sqlsentry claims better ssms profiling. http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view (i haven't tried looks promising).

let me know if indexes or not.