sql server - SQL Select set of records from one table, join each record to top 1 record of second table matching 1 column, sorted by a column in the second table -

this first question on here, apologize if break rules.

here's situation. have table lists employees , building assigned, plus training hours, ssn id column, have table list employees in company, ssn, including name, , other personal data. second table contains multiple records each employee, @ different points in time. need select records in first table building, recent name second table, plus allow result set sorted of columns returned.

i have in place, , works fine, slow.
simplified version of tables are:

table1 (ssn char(9), buildingnumber char(7), traininghours(dec(5,2)) (7200 rows) table2 (ssn char(9), fname varchar(20), lname varchar(20), sequence int) (708,000 rows) 

the sequence column in table 2 number corresponds predetermined date enter these records, higher number, more recent entry. common/expected each employee has several records. several may not have recent(i.e. '8').

my sproc is:

@buildingnumber char(7), @sortfield varchar(25) begin declare @returnvalue table(ssn char(9), buildingnumber cahr(7), fname varchar(20), lname varchar(20), rownumber int)  insert @returnvalue(...)      select(ssn,buildingnum,fname,lname,rownum)         select(...,case @sortfield row_number() on (partition buildingnumber order {sortfield column} end rownumber)            table1                outer apply(select top 1 fname,lname table2 ssn = a.ssn order sequence desc) e            buildingnumber = @buildingnumber  select * @returnvalue order rownumber end 

i have indexes following:

table1: buildingnumber(non-unique,nonclustered)
table2: sequence_ssn(unique,nonclustered)

like said gets me correct result set, rather slow. there better way go doing this?

it's not possible change database structure or way table 2 operates. trust me if done. there indexes make speed up?

i've looked @ execution plans, , has clustered index scan on table 2(18%), compute scalar(0%), eager spool(59%), filter(0%), top n sort(14%). that's 78% of execution know it's in section names, not sure of better(faster) way it.

the reason i'm asking table 1 needs updated current data. done through webpage radgrid control. has range, start index, that, , takes forever users update data. can change how update process done, thought i'd ask query first.

thanks in advance.

i approach window functions. idea assign sequence number records in table duplicates (i think table2), such recent records have value of 1. select recent record:

select t1.*, t2.* table1 t1 join      (select t2.*,              row_number() on (partition ssn order sequence desc) seqnum       table2 t2      ) t2      on t1.ssn = t1.ssn , t2.seqnum = 1 t1.buildingnumber = @buildingnumber; 

my second suggestion use user-defined function rather stored procedure:

create function xxx (     @buildingnumber int ) returns table return (     select t1.ssn, t1.buildingnum, t2.fname, t2.lname, rownum     table1 t1 join          (select t2.*,                  row_number() on (partition ssn order sequence desc) seqnum           table2 t2          ) t2          on t1.ssn = t1.ssn , t2.seqnum = 1     t1.buildingnumber = @buildingnumber; ); 

(this doesn't have logic ordering because doesn't seem central focus of question.)

you can call as:

select * dbo.xxx(<building number>); 

edit:

the following may speed further, because selecting small(ish) subset of employees:

select * (select t1.*, t2.*,  row_number() on (partition ssn order sequence desc) seqnum       table1 t1 join            table2 t2            on t1.ssn = t1.ssn       t1.buildingnumber = @buildingnumber      ) t seqnum = 1; 

and, finally, suspect following might fastest:

select t1.*, t2.*,  row_number() on (partition ssn order sequence desc) seqnum table1 t1 join      table2 t2      on t1.ssn = t1.ssn t1.buildingnumber = @buildingnumber ,       t2.sequence = (select max(sequence) table2 t2a t2a.ssn = t1.ssn) 

in these cases, index on table2(ssn, sequence) should performance.