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 )
Comments
Post a Comment