Databases
StackOverflow2013
Postsdbo
POUse temp table or table variable for stored procedure that returns 100+ rows
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUse temp table or table variable for stored procedure that returns 100+ rows
    primarykey
    data
    text
    <p>Okay basically I am creating a stored procedure that will return data for our coldfusion power search.</p> <p>I created a view, to hold data from multiple tables, with the same column names returned of course.</p> <p>Then in my stored procedure i have created a simple temporary table like this....</p> <pre><code> CREATE TABLE #TempSearchResults ( search_id int identity, id integer, type varchar(20), title varchar(50), url varchar(250), rank integer ) </code></pre> <p>Then I added an index to it, in my perhaps limited experience as a way to improve performance.</p> <pre><code>CREATE UNIQUE INDEX idx on #TempSearchResults (search_id) </code></pre> <p>Then i did my select into massive query</p> <pre><code>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 </code></pre> <p>and it goes on like that having different rank math values for where it found the keyword in the tables.</p> <p>And at the end it does...</p> <pre><code>select id, type, title, url, rank from #TempSearchResults group by id, type, title, url, rank order by rank desc, title asc; </code></pre> <p>Now when I test that stored procedure in coldfusion, it seems to take very long.</p> <p>So I am thinking, either I am using temp tables wrong or incompletely for optimal performance.</p> <p>Or perhaps I should switch to table variables...</p> <p>But I was just reading...<a href="http://sqlnerd.blogspot.com/2005/09/temp-tables-vs-table-variables.html" rel="nofollow">Temp Tables VS Table Variables</a></p> <p>Funny thing is, this stored procedure seems to be slower than me running the query directly via coldfusion, which I prefer not to. </p> <p>I am hoping for optimal performance....</p> <p>Thank you...</p> <p>Below is the basic logic or code for the view I am using.</p> <pre><code>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 </code></pre> <p>Etc like that. I can't reveal the exact details, as that would be a security breach. I hope that makes it clearer.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UScrosenblum
    1. UScrosenblum
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUse temp table or table variable for stored procedure that returns 100+ rows
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you...for editing..
      singulars
      1. POUse temp table or table variable for stored procedure that returns 100+ rows
      1. UScrosenblum
    2. COHow long does your insert statement take to run? I bet that's the slow part... run it alone as a select and see how much of the time is spent there. Gotta figure out WHAT we need to optimize first!
      singulars
      1. POUse temp table or table variable for stored procedure that returns 100+ rows
      1. USWavel
    3. COWhen running the cfstoredproc in coldfusion it says 5312 ms. In SSMS it says total execution time 171.00
      singulars
      1. POUse temp table or table variable for stored procedure that returns 100+ rows
      1. UScrosenblum
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload