麻烦大牛们帮我优化下这条Sql语句

[xieyue793162689]

select  distinct A.a0100  from lackarchives B, 
(
select a0100,'A0141' as colname,A0141 as colvalue  from sba01 where A0141!=''
union 
select a0100,'AdministrativeLevels' as colname,AdministrativeLevels as colvalue from sba01 where AdministrativeLevels!=''
union
select a0100,'TechnicalLevel' as colname,TechnicalLevel as colvalue from sba01 where TechnicalLevel!=''
union
select a0100,'PostPosition' as colname,PostPosition as colvalue from sba01 where PostPosition!=''
union
select a0100,'CulturalDegree' as colname,CulturalDegree as colvalue from sba01 where CulturalDegree!=''
union
select a0100,'AdministrativeLevels-R' as colname,AdministrativeLevels as colvalue from resume where AdministrativeLevels!=''
union
select a0100,'PostPosition-R' as colname,Position as colvalue from resume where Position!=''
union
select a0100,'PXDDDN' as colname,PXDDDM as colvalue from pxtable where PXDDDM!=''
union
select a0100,'JCDM' as colname,JCDM as colvalue from jctable where JCDM!=''
union
select a0100,'A1204' as colname, cast(A1204 as varchar) as colvalue from a12 where A1204!=''
union
select a0100,'PXXZDM' as colname,PXXZDM as colvalue from a08 where PXXZDM!=''
) As A
where A.colvalue!=''
and A.colvalue=B.dircode and  B.hlpcode=A.colname
and not exists 
(
    select 1 from lackarchives C  where C.hlpcode=A.colname and C.dircode=A.colvalue
    and C.archid in (select clcodetype from archives where archives.a0100=A.a0100 )
)) as c 
and kind='某一类材料名称的id'

麻烦各位大牛给优化下这条Sql语句，再次先谢过了！

[GoAwayZ]

不需要优化了吧，没什么可优化的

[varbinary]

union --> union All

A.colvalue!='' 去掉

C.hlpcode=A.colname and C.dircode=A.colvalue --> C.hlpcode=B.hlpcode and C.dircode=B.dircode

上面的可能都无效，那就
1、增加索引lackarchives （kind）
2、增加索引lackarchives （hlpcode，dircode，archid）

[xieyue793162689]

谢谢varbinary大牛，上述优化都啥效果，还是建索引有效，我开始这条语句，1分钟还查不出结果，建了索引后，6秒钟就搞定了，哈哈！thank you varbinary