如何优化这个sql语句

SELECT     a.type, b.postnature, b.compcode
FROM  职工岗位管理情况表 AS b INNER JOIN 在职职工基本信息表 AS a ON b.name = a.statusname
WHERE   (a.move = '在岗') and  (type IN  (SELECT  name FROM  在职职工基本信息表用工类别维护表)) AND (postnature IN (SELECT  name  FROM   职工岗位性质维护表))

如果数据量很小，希望能做到以上的执行结果。
但是如果数据量大，目前最少已有50000条记录，数据库为sqlserver2005,请问该如何优化sql语句或者如何写这个效果。
问题补充：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
alter proc zgrytj (@username varchar(100),@tablename varchar(50))
as begin
declare
@qycode varchar(100),
@rolename varchar(100),
@gwlb varchar(100),
@gwxz varchar(100),
@rcount int,
@ctotalcount int,
@rtotalcount int,
@rname varchar(50),
@cname varchar(50),
@totalcount int,
@sqlstr nvarchar(4000),
@sqlstrI nvarchar(4000),
@sqlstrII nvarchar(4000)
create table #test(
   rcount int,
   ctotalcount int,
       rtotalcount int,
   rname varchar(50),
   cname varchar(50),
)
select @qycode=b.code  from sys_user a,sys_企业信息表 b
where  a.corporation=b.code and a.username=@username
select @rolename=rolename  from  sys_memberofrole where member=@username
-----------------如果不是系统帐号查询出单条的信息------
if @rolename<>'系统管理员' and @rolename<>'省局领导'
   begin
  --申明游标
declare gwlb CURSOR FOR select name from 在职职工基本信息表用工类别维护表 order by id
--打开游标
open gwlb
fetch from gwlb into @gwlb
WHILE @@FETCH_STATUS = 0
begin
declare gwxz cursor for select name from 职工岗位性质维护表   order by id
open gwxz
fetch from gwxz into @gwxz
WHILE @@FETCH_STATUS = 0
begin      
select @sqlstr='select @a=count(*)  from '+@tablename+' where '+
'  type ='''+@gwlb+''' and postnature='''+@gwxz+''' and compcode='''+@qycode+''''
        select @sqlstrI='select @a=count(*)  from '+@tablename+' where '+
'  type ='''+@gwlb+''' and compcode='''+@qycode+''''
select @sqlstrII='select @a=count(*)  from '+@tablename+' where '+
'  postnature ='''+@gwxz+''' and compcode='''+@qycode+''''
        exec sp_executesql @sqlstr,N'@a int output',@rcount output
        exec sp_executesql @sqlstrI,N'@a int output',@rtotalcount output
        exec sp_executesql @sqlstrII,N'@a int output',@ctotalcount output
insert into #test
values(@rcount,@ctotalcount,@rtotalcount,@gwlb,@gwxz)
fetch  next from gwxz into @gwxz
end
close gwxz
deallocate gwxz
fetch  next from gwlb into @gwlb
end
CLOSE gwlb--关闭游标
DEALLOCATE gwlb--释放游标
select * from #test
end

else
begin
  --申明游标
declare gwlb CURSOR FOR select name from 在职职工基本信息表用工类别维护表 order by id
--打开游标
open gwlb
fetch from gwlb into @gwlb
WHILE @@FETCH_STATUS = 0
begin
declare gwxz cursor for select name from 职工岗位性质维护表   order by id
open gwxz
fetch from gwxz into @gwxz
WHILE @@FETCH_STATUS = 0
begin      
select @sqlstr='select @a=count(*)  from '+@tablename+' where '+
'  type ='''+@gwlb+''' and postnature='''+@gwxz+''''
        select @sqlstrI='select @a=count(*)  from '+@tablename+' where '+
'  type ='''+@gwlb+''''
select @sqlstrII='select @a=count(*)  from '+@tablename+' where '+
'  postnature ='''+@gwxz+''''
        exec sp_executesql @sqlstr,N'@a int output',@rcount output
        exec sp_executesql @sqlstrI,N'@a int output',@rtotalcount output
        exec sp_executesql @sqlstrII,N'@a int output',@ctotalcount output
insert into #test
values(@rcount,@ctotalcount,@rtotalcount,@gwlb,@gwxz)
fetch  next from gwxz into @gwxz
end
close gwxz
deallocate gwxz
fetch  next from gwlb into @gwlb
end
CLOSE gwlb--关闭游标
DEALLOCATE gwlb--释放游标
select * from #test
end
end

按照
SELECT     a.type, b.postnature, b.compcode
FROM  职工岗位管理情况表 AS b
INNER JOIN 在职职工基本信息表 AS a ON b.name = a.statusname
join 在职职工基本信息表用工类别维护表 c on a.type = c.name
join 职工岗位性质维护表 d on b.postnature = d.name
WHERE   (a.move = '在岗') 建好的视图 名为 zgry

之前问的那个sql语句是为了在这个存储过程中调用的动态表或视图.问题I:select count(*),postnature from zgry group by postnature这个语句执行起来都费事.问题II：我不知道存储过程是否也能优化，不知道有什么问题，之前写的一个没执行很快，和这个存储过程差不多，就是动态传入的视图或表的数据量较大。希望能够给与支持。