sql server 2008 - Use temp table or table variable for stored procedure that returns 100+ rows -

okay creating stored procedure return data our coldfusion power search.

i created view, hold data multiple tables, same column names returned of course.

then in stored procedure have created simple temporary table this....

    create table #tempsearchresults (     search_id int identity,     id integer,     type varchar(20),     title varchar(50),     url varchar(250),     rank integer ) 

then added index it, in perhaps limited experience way improve performance.

create unique index idx on #tempsearchresults (search_id) 

then did select massive query

insert #tempsearchresults select id, type, title, url, rank + 1200 rank view company_id = @company_id , title @keyword union select id, type, title, url, rank + 1100 rank view company_id = @company_id , title @keyword , description @keyword 

and goes on having different rank math values found keyword in tables.

and @ end does...

select id, type, title, url, rank #tempsearchresults group id, type, title, url, rank order rank desc, title asc; 

now when test stored procedure in coldfusion, seems take long.

so thinking, either using temp tables wrong or incompletely optimal performance.

or perhaps should switch table variables...

but reading...temp tables vs table variables

funny thing is, stored procedure seems slower me running query directly via coldfusion, prefer not to.

i hoping optimal performance....

thank you...

below basic logic or code view using.

select field id, field title, field description, 'url link' url, 1 rank table union select field id, field title, field description, 'url link' url, 1 rank table b union select field id, field title, field description, 'url link' url, 1 rank table c 

etc that. can't reveal exact details, security breach. hope makes clearer.

i see no need use temporary table or table variable @ all. can write

select id, type, title, url, rank (     select id, type, title, url, rank + 1200 rank      view      company_id = @company_id , title @keyword       union       select id, type, title, url, rank + 1100 rank      view      company_id = @company_id , title @keyword , description @keyword ) t group id, type, title, url, rank order rank desc, title asc; 

edit:

replacing union union, can simplified to

select id, type, title, url, rank + 1200 rank  view  company_id = @company_id , title @keyword   union   select id, type, title, url, rank + 1100 rank  view  company_id = @company_id , title @keyword , description @keyword  order rank desc, title asc;