sql server - SQL Optimize Script using cte -

i want optimize script because encounter slow response in getting result if there hundred thousands of records. objective of following:

• get records has more 1 record same altid
• producttype 0 or false value, must assign buyer value producttype=1 or true
• exclude records has more 2 different buyer value producttype=1 or true value.

here's table structure, sample records , query:

create table products   (      altid       varchar(10),      itemid      varchar(10),      producttype bit,      buyer       varchar(6)   )  create unique nonclustered index idx_product   on products (altid, itemid)  insert products             (altid,itemid,producttype,buyer) values      ('a01','itema0101',0,'216')  insert products             (altid,itemid,producttype,buyer) values      ('a01','itema0102',0,null)  insert products             (altid,itemid,producttype,buyer) values      ('a01','itema0103',1,'264')  insert products             (altid,itemid,producttype,buyer) values      ('a01','itema0104',1,null)  insert products             (altid,itemid,producttype,buyer) values      ('a02','itema0201',0,'215')  insert products             (altid,itemid,producttype,buyer) values      ('a02','itema0202',1,'217')  insert products             (altid,itemid,producttype,buyer) values      ('a03','itema0301',0,'215')  insert products             (altid,itemid,producttype,buyer) values      ('a03','itema0302',1,'216')  insert products             (altid,itemid,producttype,buyer) values      ('a03','itema0303',1,'264')  insert products             (altid,itemid,producttype,buyer) values      ('a04','itema0401',1,'216')  insert products             (altid,itemid,producttype,buyer) values      ('a05','itema0501',1,'218')  insert products             (altid,itemid,producttype,buyer) values      ('a05','itema0502',0,'216')  insert products             (altid,itemid,producttype,buyer) values     ('a05','itema0503',1,null);  original_query      (         --get altid products have more 1 buyer         --join manuf table columns needed buyers         select b.altid,                p.itemid,                p.buyer,                p.producttype--p.manufid            (                 --get altid has more 1 buyer                 select a.altid                    (                         --get alt id , group altid , buyer                         select p.altid                            products p                          --where p.buyer not null                          group  p.altid,                                    p.buyer)                  group  a.altid                  having count(*) > 1)b                 join products p                   on b.altid = p.altid         --join manuf m on p.manufid = m.manufid         --where p.buyer  not null         ), -- null value buyer      getallnullbuyer      (select oq.altid,                 oq.itemid,                 oq.buyer,                 oq.producttype            original_query oq           oq.buyer null), --get buyer has no null value      getallnotnullbuyer      (select oq.altid,                 oq.itemid,                 oq.buyer,                 oq.producttype            original_query oq --join  products p --result          --on oq.altid=p.altid           oq.buyer not null         --group oq.altid,oq.buyer,p.producttype         ), --count buyer per altid , producttype      count_altidperbuyer      (select a.altid,                 a.buyer,                 count(a.buyer) buyercnt,                 a.producttype            getallnotnullbuyer          group  a.altid,                    a.buyer,                    a.producttype), --get list of buyer producttype=1 , not more 1 buyer      exclude_rec      (select altid,                 count(buyer) buyercnt            count_altidperbuyer           producttype = 1          group  altid          having count(buyer) > 1), --combine buyer value , null value did not inlcude buyer in exclude_rec      combinenullbuyer      (select altid,                 itemid,                 buyer,                 producttype            getallnotnullbuyer           altid not in (select altid                                 exclude_rec)          union          select altid,                 itemid,                 buyer,                 producttype            getallnullbuyer           altid not in (select altid                                 exclude_rec)), --get altid producttype=1 , buyer null      getproducttypebuyer      (select a.altid,                 a.itemid,                 a.producttype,                 a.buyer            combinenullbuyer                 join products p                   on a.altid = p.altid                      , a.itemid = p.itemid           p.producttype = 1                 , p.buyer null), --combine records producttype=1 , buyer null , combinenullbuyer records      combineall      (select altid,                 itemid,                 buyer,                 producttype            combinenullbuyer          union          select altid,                 itemid,                 buyer,                 producttype            getproducttypebuyer), --assign new buyer id      assign_buyer      (select r.altid,                 r.itemid,                 r.buyer,                 r.producttype,                 newbuyer=isnull((select top 1 x.buyer                                    products x                                   x.altid = r.altid                                         , x.producttype = 1), r.buyer)            combineall r), --this assign new buyer id buyer null , producttype=1      revisedbuyer      (select *,                 ( dense_rank()                     over(                       partition altid                       order buyer desc) ) seqno            assign_buyer) select p.altid,        p.itemid,        ab.buyer,        ab.producttype,        ab.newbuyer,        ( case            when ab.newbuyer null                 , ab.producttype = 1 (select top 1 x.buyer                                                revisedbuyer x                                               x.altid = ab.altid                                                     , x.producttype = 1                                                     , x.seqno = 1)            else ab.newbuyer          end ) buyer1   products p        join assign_buyer ab          on p.altid = ab.altid             , p.itemid = ab.itemid  isnull(p.buyer, '') <> isnull(case                                        when ab.newbuyer null                                             , ab.producttype = 1 (select top 1 x.buyer                                                                            revisedbuyer x                                                                           x.altid = ab.altid                                                                                 , x.producttype = 1                                                                                 , x.seqno = 1)                                        else ab.newbuyer                                      end, '') order  ab.altid,           ab.itemid  

here's solution problems:

    --save temporary product table select b.altid, p.itemid, p.buyer,p.producttype #products         (     --get altid has more 1 altid     select a.altid     (         --get alt id , group altid , buyer         select p.altid products p         --where p.buyer not null         group p.altid,p.producttype,p.buyer         )         group a.altid         having count(*) > 1     )b join products p on b.altid = p.altid create unique nonclustered index idx_product on #products(altid,itemid) --- end save temp table  ---temporary table excluded record wherein has more 2 buyer prodcuttype=1 ;with count_altidperbuyer (         select altid,buyer,count(buyer) cnt,producttype     #products     group altid,buyer,producttype ),--list of altid has more 2 manufid producttype=1 exclude_rec as(     select altid,count(buyer) buyercnt     count_altidperbuyer     producttype=1     group altid     having count(buyer)>1 ) select altid #excluded_altid exclude_rec create unique nonclustered index idx_excluded_altid on #excluded_altid(altid) ---end of temporary excluded record ;with cte (--does not include records found in #excluded_altid table     select altid,itemid,buyer,producttype     #products      altid not in (select altid #excluded_altid ) ), cte2 (     select a.altid,a.itemid,a.producttype,a.buyer     cte join products p     on a.altid=p.altid , a.itemid=p.itemid     p.producttype=1 , p.buyer null     union     select a.altid,a.itemid,a.producttype,a.buyer     cte ),assign_buyer (     select  r.altid,r.itemid,r.buyer,r.producttype,newbuyer=isnull(     isnull((select top 1 x.buyer products x x.altid=r.altid , x.producttype=1),r.buyer),     (select top 1 x.buyer products x x.altid=r.altid , x.producttype=1 , x.buyer not null))     cte2 r )--,--this assign new buyer id buyer null , producttype=1 --assign_buyertonewbuyer --( --  select  ab.altid,ab.itemid,ab.buyer,ab.producttype,newbuyer, newbuyer2=(case when newbuyer null --      (select top 1 x.buyer products x x.altid=ab.altid , x.producttype=1 , x.buyer not null) --      else newbuyer end) --  assign_buyer ab --  --select *, (dense_rank() over(partition altid order buyer desc)) seqno --  --from assign_buyer --) select p.altid,p.itemid,ab.buyer,ab.producttype,ab.newbuyer products p join assign_buyer ab on p.altid=ab.altid , p.itemid=ab.itemid isnull(p.buyer,'') <> isnull(ab.newbuyer,'') order ab.altid, ab.itemid  drop table #products drop table #excluded_altid 

sql performance improves compare old queries. did, create temporary table index , use cte. if have other solution problems, please post answer.

thanks @jamesz comments , idea. here's link sql fiddler reference.