Databases
StackOverflow2013
Postsdbo
POSQL Server Multi-statement UDF - way to store data temporarily required
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server Multi-statement UDF - way to store data temporarily required
    primarykey
    data
    text
    <p>I have a relatively complex query, with several self joins, which works on a rather large table. For that query to perform faster, I thus need to only work with a subset of the data. Said subset of data can range between 12 000 and 120 000 rows depending on the parameters passed.</p> <p>More details can be found here: <a href="https://stackoverflow.com/questions/3054843/sql-server-cte-referred-in-self-joins-slow">SQL Server CTE referred in self joins slow</a></p> <p>As you can see, I was using a CTE to return the data subset before, which caused some performance problems as SQL Server was re-running the Select statement in the CTE for every join instead of simply being run once and reusing its data set.</p> <p>The alternative, using temporary tables worked much faster (while testing the query in a separate window outside the UDF body). However, when I tried to implement this in a multi-statement UDF, I was harshly reminded by SQL Server that multi-statement UDFs do not support temporary tables for some reason...</p> <p>UDFs do allow table variables however, so I tried that, but the performance is absolutely horrible as it takes 1m40 for my query to complete whereas the CTE version <em>only</em> took 40 seconds. I believe the table variables is slow for reasons listed in this thread: <a href="https://stackoverflow.com/questions/1643687/table-variable-poor-performance-on-insert-in-sql-server-stored-procedure">Table variable poor performance on insert in SQL Server Stored Procedure</a></p> <p>Temporary table version takes around 1 seconds, but I can't make it into a function due to the SQL Server restrictions, and I <em>have</em> to return a table back to the caller.</p> <p>Considering that CTE and table variables are both too slow, and that temporary tables are rejected in UDFs, What are my options in order for my UDF to perform quickly?</p> <p>Thanks a lot in advance.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USKharlos Dominguez
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    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
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server Multi-statement UDF - way to store data temporarily required
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThe issue with table variables is that the query optimiser acts as though they will always return one row and so can lead to some bad decisions. You might be able to force a better plan if you know better. What version of SQL Server are you on? 2005 or 2008?
      singulars
      1. POSQL Server Multi-statement UDF - way to store data temporarily required
      1. USMartin Smith
    2. COSQL Server 2005, thanks.
      singulars
      1. POSQL Server Multi-statement UDF - way to store data temporarily required
      1. USKharlos Dominguez
 

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