sql server - Optimize sql except query -

i have query needs optimized , im not sure how. sql script:

declare @temptable table(color1 int, color2 int, color3 int, color4 int, newtoken uniqueidentifier default newid(), ordinal int identifier(1,1))  insert @temptable  select color1, color2, color3, color4 @colorcombination except select c1.color color1, c2.color color2, c3.color color3, c4.color color4 products p inner join attributes c1 on c1.pid = p. id , c1.type = 'primary' inner join attributes c2 on c2.pid = p. id , c2.type = 'secondary' inner join attributes c3 on c3.pid = p. id , c3.type = 'other1' inner join attributes c4 on c4.pid = p. id , c4.type = 'other2' p.category = 'furniture' 

without except, both select statement run fast except, took 15minutes

@colorcombination has 24,000 rows

the productattributes return 11,000 rows

i used except because need find 13,000 not in database yet , insert it.

this running on sql server 2008

is there better way missing records instead of using except?

the poor performance few rows caused table variables , how impact execution plan.

this answer uses temporary tables nonclustered indexes on (color1, color2, color3, color4). original table variables did not specify not null colors, assume null values acceptable. if not, drop surrogate id , use clustered index on 4 color columns. allow nulls, switchs except instead of not exists(). if null not factor, not exists() should bit faster.

reference concerning table variables , temp tables:

• temporary tables in stored procedures - paul white
• what difference between temp table , table variable in sql server? - answer martin smith
• when should use table variable vs temporary table in sql server? - answer martin smith

---

/* step 1: existing colors in temp table index */  create table #productcolors (     id int not null identity (1,1) primary key clustered   , color1 int , color2 int , color3 int , color4 int    );  insert #productcolors (color1, color2, color3, color4) select distinct     color1  = case when c.[type] = 'primary'   c.color end   , color2  = case when c.[type] = 'secondary' c.color end   , color3  = case when c.[type] = 'other1'    c.color end   , color4  = case when c.[type] = 'other2'    c.color end from products p   inner join attributes c     on p.id = c.id group p.id;  create nonclustered index ix_productcolors   on #productcolors (color1, color2, color3, color4);  /* step 2: color combinations in temp table index */  create table #colorcombinations (     id int not null identity (1,1) primary key clustered   , color1 int , color2 int , color3 int , color4 int  );  insert #colorcombinations (color1, color2, color3, color4) select distinct color1 , color2 , color3 , color4  @colorcombinations;  create nonclustered index ix_colorcombinations   on #colorcombinations (color1, color2, color3, color4);  /* step 3: insert new color combinations #temptable */  create table #temptable (color1 int   , color2 int   , color3 int   , color4 int   , newtoken uniqueidentifier default newid()   , ordinal int identifier(1,1) );  insert #temptable(color1, color2, color3, color4)   select color1, color2, color3, color4   #colorcombination   except   select color1, color2, color3, color4   #productcolors 

---

old answer:

using except de-duplicates rows in @temptable (which might better off #temptable instead of @tablevariable depending on how using it).

if not need de-duplicate rows @temptable, can use not exists() (or add distinct select, take performance hit):

insert @temptable  select color1, color2, color3, color4 @colorcombination cc not exists (   select 1   products p   inner join attributes c1 on c1.pid = p. id , c1.type = 'primary'   inner join attributes c2 on c2.pid = p. id , c2.type = 'secondary'   inner join attributes c3 on c3.pid = p. id , c3.type = 'other1'   inner join attributes c4 on c4.pid = p. id , c4.type = 'other2'   p.category = 'furniture'     , c1.color = cc.color1     , c2.color = cc.color2     , c3.color = cc.color3     , c4.color = cc.color4 )