Linked server slow slow slow

• jbrabant

  SSC Enthusiast

  Points: 153

  More actions

  December 5, 2003 at 7:15 am

  #65585

  I run the following piece of code in Query Analyzer and it takes not even a second when connected directly on the server and the same thing (beside the FROM tbdpcy.gbdb.dbo.tests) is taking forever to run. I can run any other kind of query through my linked server but this one .....

  Any ideas ?

  DECLARE @varid int, @var_title varchar(20), @today numeric(18,2),

  @mtd numeric(18,2), @ytd numeric(18,2),@displayid int,@monthstart smalldatetime,@yearstart smalldatetime,@timestamp smalldatetime

  create table #temp_results (varid INT,today numeric(18,2),mtd numeric(18,2),ytd numeric(18,2))

  set @displayid=1

  Set @timestamp='2003/12/04 06:00:00'

  set @monthstart='2003/12/01'

  set @yearstart='2003/09/29'

  if @displayid=1

  DECLARE var_results CURSOR FOR

  SELECT var_id, result

  FROM tests

  WHERE var_id IN (12054,12052,12051) and result_on=@timestamp

  ORDER BY var_id

  else

  DECLARE var_results CURSOR FOR

  SELECT var_id, result

  FROM tests

  WHERE var_id IN (1) and result_on=@timestamp

  ORDER BY var_id

  OPEN var_results

  FETCH NEXT FROM var_results

  INTO @varid,@today

  WHILE @@FETCH_STATUS = 0

  BEGIN

  set @mtd=(select sum(convert(numeric(18,2),result)) from tests where var_id=@varid and result_on >=@monthstart and result_on <=@timestamp)

  set @ytd=(select sum(convert(numeric(18,2),result)) from tests where var_id=@varid and result_on >=@yearstart and result_on <=@timestamp)

  insert #temp_results values (@varid,@today,@mtd,@ytd)

  FETCH NEXT FROM var_results

  INTO @varid,@today

  END

  CLOSE var_results

  DEALLOCATE var_results

  select * from #temp_results

  drop table #temp_results