Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  Query runs too long

Author  Topic  BIGGY Starting Member 17 Posts Posted - 2006-03-28 : 15:32:04 declare @shipperid varchar(15) declare @preshipperid varchar(15) declare @trackingnbr varchar(30) declare @strTrackingNbr varchar(100) set nocount on create table #soevent (shipperid varchar(15), trackingnbr varchar(100)) create table #tracking (shipperid varchar(15), trackingnbr varchar(100)) insert into #soevent select b.shipperid, '' as trackingnbr from soevent as a with (nolock) inner join soshipheader as b with (nolock) on a.shipperid = b.shipperid and b.cancelled = 0 and b.status = 'C' where a.shipperid = 'GCS0151024' insert into #tracking select a.shipperid, b.trackingnbr from soshipheader as a with (nolock) inner join soshippack as b with (nolock) on a.shipperid = b.shipperid where a.shipperid in (select shipperid from #soevent) declare tracking cursor for select * from #tracking open tracking fetch next from tracking into @shipperid, @trackingnbr select @strTrackingNbr = '', @preshipperid = '' while @@fetch_status = 0 begin if @preshipperid = @shipperid begin select @strTrackingNbr = @strTrackingNbr + ',' + @trackingnbr end else begin if @strTrackingNbr = '' begin select @strTrackingNbr = @trackingnbr end end select @preshipperid = @shipperid fetch next from tracking into @shipperid, @trackingnbr end update #soevent set trackingnbr = @strTrackingNbr where trackingnbr = '' or trackingnbr is null close tracking deallocate tracking declare cursor1 cursor for select distinct shipperid, trackingnbr from #soevent open cursor1 fetch next from cursor1 into @shipperid, @trackingnbr -- create table #sns_shipper_serials(shipperid varchar(15), shipper_invtid varchar(30), linenbr int, component_invtid varchar(30), serial varchar(25), lotsernbralt1 varchar(25), lotsernbralt2 varchar(25), lotsernbralt3 varchar(25), lotsernbralt4 varchar(25), whseloc varchar(10)) while @@fetch_status = 0 begin insert into #sns_shipper_serials select distinct shipperid, shipper_invtid, linenbr, component_invtid, serial, lotsernbralt1, lotsernbralt2, lotsernbralt3, lotsernbralt4, whseloc from sns_shipper_serials where shipperid = 'GCS0151024' --@shipperid <----------*****************ERROR HERE********************* fetch next from cursor1 into @shipperid, @trackingnbr end close cursor1 deallocate cursor1 set nocount off select a.custordnbr, a.custid, a.orddate, a.shipname, a.shipaddr1, a.shipaddr2, a.shipcity, a.shipstate, a.shipzip, a.shipviaid, c.trackingnbr, b.alternateid, b.user1, case f.user5 when 'YES' then 1 else b.qtyship end, d.serial, f.user5 from soshipheader as a with (nolock) inner join soshipline as b with (nolock) on a.shipperid = b.shipperid and a.cancelled = 0 and a.status = 'C' left join #soevent as c with (nolock) on a.shipperid = c.shipperid left join #sns_shipper_serials as d with (nolock) on b.shipperid = d.shipperid and b.invtid = d.shipper_invtid and b.user2 = d.lotsernbralt2 inner join inventory as f with (nolock) on b.invtid = f.invtid where a.shipperid = 'GCS0151024' order by a.custordnbr drop table #tracking drop table #soevent drop table #sns_shipper_serials If you see above, you'll notice the arrow with ERROR HERE. If the exact shipper number is removed and @shipperid is uncommented, then the query seems to run forever...however with a fixed id number it runs just fine. Any ideas what would make this run so much slower? Trying to help a friend out with this problem but am not really getting it.