Use temp table or table variable for stored procedure that returns 100+ rows

Question

Okay basically I am creating a stored procedure that will return data for our coldfusion power search.

I created a view, to hold data from multiple tables, with the same column names returned of course.

Then in my stored procedure i have created a simple temporary table like this....

    CREATE TABLE #TempSearchResults
(
    search_id int identity,
    id integer,
    type varchar(20),
    title varchar(50),
    url varchar(250),
    rank integer
)

Then I added an index to it, in my perhaps limited experience as a way to improve performance.

CREATE UNIQUE INDEX idx on #TempSearchResults (search_id)

Then i did my select into massive query

insert into #TempSearchResults
select id, type, title, url, rank + 1200 as rank
from my view
where company_id = @company_id
and title like @keyword
union all
select id, type, title, url, rank + 1100 as rank
from my view
where company_id = @company_id
and title like @keyword
and description like @keyword

and it goes on like that having different rank math values for where it found the keyword in the tables.

And at the end it does...

select id, type, title, url, rank
from #TempSearchResults
group by id, type, title, url, rank
order by rank desc, title asc;

Now when I test that stored procedure in coldfusion, it seems to take very long.

So I am thinking, either I am using temp tables wrong or incompletely for optimal performance.

Or perhaps I should switch to table variables...

But I was just reading...Temp Tables VS Table Variables

Funny thing is, this stored procedure seems to be slower than me running the query directly via coldfusion, which I prefer not to.

I am hoping for optimal performance....

Thank you...

Below is the basic logic or code for the view I am using.

select some field as id, some field as title, some field as description, 'url link' as url, 1 as rank
from table a
union
select some field as id, some field as title, some field as description, 'url link' as url, 1 as rank
from table b
union
select some field as id, some field as title, some field as description, 'url link' as url, 1 as rank
from table c

Etc like that. I can't reveal the exact details, as that would be a security breach. I hope that makes it clearer.

Answer 1

I see no need to use a temporary table or table variable at all. You can just write

select id, type, title, url, rank
from (
    select id, type, title, url, rank + 1200 as rank 
    from my view 
    where company_id = @company_id and title like @keyword 

    union all 

    select id, type, title, url, rank + 1100 as rank 
    from my view 
    where company_id = @company_id and title like @keyword and description like @keyword
) as t
group by id, type, title, url, rank
order by rank desc, title asc;

Edit:

Replacing UNION ALL by UNION, this can be simplified to

select id, type, title, url, rank + 1200 as rank 
from my view 
where company_id = @company_id and title like @keyword 

union 

select id, type, title, url, rank + 1100 as rank 
from my view 
where company_id = @company_id and title like @keyword and description like @keyword

order by rank desc, title asc;

Answer 2

Use with (Nolock) hint on tables where ever you are selecting the data; it may improve performance if your application allows for dirty reads.