New answers tagged

1

The MATERIALIZE hint in the WITH clause does that. SQL> with cte as (select /*+ materialize */ 1,'Transaction-specific private temp table' from dual) 2 select * from cte union all select * from cte union all select * from cte; 1 'TRANSACTION-SPECIFICPRIVATETEMPTABLE' ---------- --------------------------------------- 1 Transaction-...


1

The reads and scans are part of it reading the inner sorted row source. I had a little play replicating this and it looks like the important things to note are that the two temp tables are of similar size and you are comparing a string (which is weird, you might expect the opposite to be true as you'd expect a string to be larger and more expensive to ...


3

Testing This query runs in ~1.2 seconds, so I'm not sure how much improvement you're expecting. Looking at things like scans and costs can often be misleading and fruitless when it comes to improving the overall timing of the query. I'm cutting out some of the columns in your query just to highlight the changes more easily. At the top, it may be worth going ...


1

There appears to be a many-to-many merge join. Without knowing more about the data in these temp tables, this may or may not be expected. If that's not expected and if rows in each temp table can be uniquely identified by (VID, Ioro, OtherLocationID), you can try to add a clustered, unique index on both tables on those columns. Then, SQL Server might ...


13

SQL Server generally builds a complete set of execution plans for the statements in a stored procedure before the procedure begins executing. That approach is slightly modified when temporary tables are present: Compilation of each statement that references a temporary table is deferred until the statement is actually executed. SQL Server has supported ...


8

Roughly speaking, cardinality estimates for statistics creation on #temp tables follows that for regular, non-temp tables. Having said that, as Paul White points out here, temp tables inside stored procedures can suffer from cached statistics values. From the page above, the salient takeaway is: Temporary tables in stored procedures have a number of ...


Top 50 recent answers are included