sql - Query optimization for non-equal data connection -

table employee:

empid  stw  --startworktime ------------------------------ 1      02:00:00.000 2      03:00:00.000 3      05:30:00.000 

table order:

orderid  placetime ------------------------------- 1        02:22:22.333 2        02:33:33:555 3        03:44:44:111 4        05:45:45:545 

request is: connect orders employee placed time relationship., like:

empid   orderid -------------------- 1       1 1       2 2       3 3       4 

assume last employee off work time = current 1 on work time.

currently solution is:

select e.empid, o.orderid order o cross apply      (select top(1) e.empid order e       e.stw < o.placetime       order stw desc) e 

the solution works fine when table employee small (order 1 mil rows, employee 50rows, 30 sec), performance drops employee expands (when it's 10000s row, takes on hour). there solutions optimize such case?

i first recommend indexes. first, fix query subquery uses employee table (i assume typo in question):

select e.empid, o.orderid order o cross apply      (select top(1) e.empid       employee e       e.stw < o.placetime       order stw desc      ) e; 

you want index on employee(stw, empid). see if helps query.