This is the query:

Select *

from

(

select (top 500 row_number() OVER (ORDER BY(rank desc) as resnum, rank, description, table2.col, table3.col, table4.col

from table1 inner join...

Thanks to Matt and Grant!

Using the Full-text index, my friend has been able to reduce the query speed by more than half.

But there was a problem. The time taken is...

Thanks guys for all the response.

I just receive some info from my friend about how to optimize speed and wonder if this indeed can work. Anyway, the table is already...