CODEWITHSUNDEEP
sql-servert-sqljoinviewtemp-tables
BaltoStar
BaltoStar

Reputation: 8967

Join user-defined table type param to view

SQL Server 2012: I have a stored procedure with a parameter of user-defined table-type.

To create its return rowset, the stored procedure selects from a view joined to the param table on a few columns none of which have indexes (which are problematic to add for other reasons).

Is it better to :

  1. load the entire view into a local temp table and then select against a join of the temp table and the param table
  2. directly select from the view joined to the parameter table

The view can contain up to ~1 million rows.

Is there any advantage either way? Or does SQL Server internally implement both approaches in a similar manner?

Upvotes: 0

Views: 989

Answers (1)

gotqn
gotqn

Reputation: 43636

I will start with creating temporary table in which to insert the user-defined table type. The defined table-type is practically a table-variable and joining table variables to other objects is pretty slow in some cases because the SQL Engine does not good information about how many rows the table-variable has.

The second thing that can be optimize is the view. The questions here are:

  1. Do you really need view? Won't be better to use a inline-table valued function also know as view with parameters? If you can pass some parameters to this function that can be use to filter the data in advanced (imagine not returning all rows but part of them) you can improve the performance as well.
  2. If you really need this data in view, could be the view indexed?

Upvotes: 1

Related Questions