Table Valued Parameter has slow performance because of table scan

Question

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

Answer 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.

Answer 2

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) )