6

I have an aplication that passes parameters to a procedure in SQL. One of the parameters is an table valued parameter containing items to include in a where clause.

Because the table valued parameter has no statistics attached to it when I join my TVP to a table that has 2 mil rows I get a very slow query.

What alternatives do I have ?

Again, the goal is to pass certain values to a procedure that will be included in a where clause:

select * from table1 where id in (select id from @mytvp)

or

select * from table1 t1 join @mytpv tvp on t1.id = tvp.id

Paul
  • 203
  • 3
  • 7

5 Answers5

1

I had the same issue that table-valued parameters where very slow in my context. I came up with a solution that passed the list of values as a comma separated string to the stored procedure. the procedure then made a PATINDEX(...) > 0 comparision. This was about a factor of 1:6 faster.

1

As mentioned here and explained here you can have primary key and unique constraints on the table type. E.g. 

CREATE TYPE IdList AS TABLE ( Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY )

However, check if it improves performance in your case as now, these indexes exist when the TVP is populated which might lead to a counter effect depending if your input is sorted and/or if you use more than one column.

Community
  • 1
  • 1
Dejan
  • 9,150
  • 8
  • 69
  • 117
1

although it looks like it would need to run the query once for each row in table1, EXISTS often optimizes to be more efficient than a JOIN or an IN. So, try this:

select * from table1 t where exists (select 1 from @mytvp p where t.id=p.id)

also, be sure that t.id is the same datatype as p.id and t.id has an index.

KM.
  • 101,727
  • 34
  • 178
  • 212
1

You can use a temp table with an index to boost performance....(assuming you have more than a couple of records in your @mytvp)

just before you join the table you could insert the data from the variable @mytvp to a temp table...

here's a sample code to create a temp table with index....The primary key and unique field determines which columns to index on..

CREATE TABLE #temp_employee_v3
    (rowID     int          not null  identity(1,1)
    ,lname     varchar (30) not null
    ,fname     varchar (30) not null
    ,city      varchar (20) not null
    ,state     char (2)     not null
    ,PRIMARY KEY (lname, fname, rowID)
    ,UNIQUE (state, city, rowID) )
Whimsical
  • 5,985
  • 1
  • 31
  • 39
  • 2
    although I would not index until after the table valued data is in the temp table. – HLGEM Dec 06 '10 at 18:23
  • I agree...Data insert is slower on adding non clustered/clustered indexes...It may not be noticeable all the time but it definitely exists...As a PS to the answer, i must say i was trying to put across the idea of having an index on the temp table might help.. – Whimsical Dec 06 '10 at 21:00
  • @Mulki, This is fine. But how can we pass those whole data to a procedure? using , separated values in a String Parameter then prepare a temp table? I also using User Defined Data type with 100 records and 10 columns. It may grow in future :( – Murali Murugesan Feb 13 '13 at 06:37
-1

In common with table variables, table-valued parameters have no statistics (see the section "restrictions"); the query optimiser works on the assumption that they contain only one row, which if your parameter contains a lot of rows is likely to result in an inappropriate query plan.

One way to improve your chances of a better plan is to add a statement level recompile; this should enable the optimiser to take the size of the TVP into account when selecting a plan.

select * from table1 t where exists (select 1 from @mytvp p where t.id=p.id) OPTION (RECOMPILE)

(incorporating KM's suggestion)

Ed Harper
  • 21,127
  • 4
  • 54
  • 80
  • 5
    -1, the optimizer may not know much about your TVP but it *does* know cardinality. It *will not*, as you claim, assume they only have one row. Per the this link: http://www.sommarskog.se/arrays-in-sql-2008.html Erland Sommarskog explains in the "Performance Considerations" section that the TVP (like all parameters) are "sniffed" for this information. – Matthew Mar 07 '11 at 22:58
  • I didn't see this in the referenced article, but [another article](https://www.brentozar.com/archive/2018/03/table-valued-parameters-unexpected-parameter-sniffing/) confirms this (see "Second Thing: They don’t get a fixed estimate like Table Variables"). ["Parameter sniffing"](https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/) refers to fixing the execution plan based on the parameter values in the first invocation of the procedure. – Nickolay Apr 05 '18 at 17:22