"Patrick" <patria***@gmail.com> wrote in message
news:%239MCu$aAGHA.204@TK2MSFTNGP15.phx.gbl...
> Hi Freinds,
> SQL 2000 SP3
>
> I am running the following :
> -----------------------------------------------------------------------
> DECLARE curMove_no CURSOR FOR
> SELECT top 10 lm.move_no
> FROM lib_movement lm with (nolock)
> left outer JOIN lib_shelf ls  with (nolock) ON lm.shelf = ls.shelf
> WHERE move_no in (select move_no from lib_movement_detail where master_no
> in (select master_no from lib_master where master_desc = 'The Wild
> Bunch'))
>
> DECLARE @recip varchar(255) --Email Address
> DECLARE @move_no bigint  -- Working Movement number
>
> OPEN curMove_no
> FETCH NEXT FROM curMove_no INTO @move_no
> WHILE @@FETCH_STATUS = 0
> BEGIN
> SET @recip = (SELECT  isnull(ls.note_text,'notvalid') FROM lib_movement lm
> with (nolock) INNER JOIN  lib_shelf ls ON lm.shelf = ls.shelf  and
> ls.vault_no in (13,40)  WHERE move_no = @move_no)
> print @recip
> print @move_no
> print '----------------'
>
> FETCH NEXT FROM curMove_no INTO @move_no
>
> END
> CLOSE curMove_no
> DEALLOCATE curMove_no
>
> --------------------------------------------------------------------------------------
>
> Which runs in less than a second , but if I eliminate the top 10 from
> cursor declare section then this will take almost 30 MINUTES to complete.
> I am sure the select section is returning only 4 records, so having top 10
> is only on safe side.
>
> but why is this happeing? what is the different between top 10 and not
> mentioning it? why that much time difference is there ?
>
> Thanks in advance,
> Pat
>

"JI" <jida***@hotmail.com> wrote in message
news:uu%232wrbAGHA.2156@TK2MSFTNGP11.phx.gbl...
>I have never tried this but I don't think you can do a top 10 with a
>cursor. Just write a loop to call the fetch 10 times.
> "Patrick" <patria***@gmail.com> wrote in message
> news:%239MCu$aAGHA.204@TK2MSFTNGP15.phx.gbl...
>> Hi Freinds,
>> SQL 2000 SP3
>>
>> I am running the following :
>> -----------------------------------------------------------------------
>> DECLARE curMove_no CURSOR FOR
>> SELECT top 10 lm.move_no
>> FROM lib_movement lm with (nolock)
>> left outer JOIN lib_shelf ls  with (nolock) ON lm.shelf = ls.shelf
>> WHERE move_no in (select move_no from lib_movement_detail where master_no
>> in (select master_no from lib_master where master_desc = 'The Wild
>> Bunch'))
>>
>> DECLARE @recip varchar(255) --Email Address
>> DECLARE @move_no bigint  -- Working Movement number
>>
>> OPEN curMove_no
>> FETCH NEXT FROM curMove_no INTO @move_no
>> WHILE @@FETCH_STATUS = 0
>> BEGIN
>> SET @recip = (SELECT  isnull(ls.note_text,'notvalid') FROM lib_movement
>> lm with (nolock) INNER JOIN  lib_shelf ls ON lm.shelf = ls.shelf  and
>> ls.vault_no in (13,40)  WHERE move_no = @move_no)
>> print @recip
>> print @move_no
>> print '----------------'
>>
>> FETCH NEXT FROM curMove_no INTO @move_no
>>
>> END
>> CLOSE curMove_no
>> DEALLOCATE curMove_no
>>
>> --------------------------------------------------------------------------------------
>>
>> Which runs in less than a second , but if I eliminate the top 10 from
>> cursor declare section then this will take almost 30 MINUTES to complete.
>> I am sure the select section is returning only 4 records, so having top
>> 10 is only on safe side.
>>
>> but why is this happeing? what is the different between top 10 and not
>> mentioning it? why that much time difference is there ?
>>
>> Thanks in advance,
>> Pat
>>
>
>

> Hi Freinds,
> SQL 2000 SP3
>
> I am running the following :
> -----------------------------------------------------------------------
> DECLARE curMove_no CURSOR FOR
> SELECT top 10 lm.move_no
> FROM lib_movement lm with (nolock)
> left outer JOIN lib_shelf ls  with (nolock) ON lm.shelf = ls.shelf
> WHERE move_no in (select move_no from lib_movement_detail where master_no in
> (select master_no from lib_master where master_desc = 'The Wild Bunch'))
>
> DECLARE @recip varchar(255) --Email Address
> DECLARE @move_no bigint  -- Working Movement number
>
> OPEN curMove_no
> FETCH NEXT FROM curMove_no INTO @move_no
> WHILE @@FETCH_STATUS = 0
> BEGIN
>  SET @recip = (SELECT  isnull(ls.note_text,'notvalid') FROM lib_movement lm
> with (nolock) INNER JOIN  lib_shelf ls ON lm.shelf = ls.shelf  and
> ls.vault_no in (13,40)  WHERE move_no = @move_no)
>  print @recip
>  print @move_no
>  print '----------------'
>
>  FETCH NEXT FROM curMove_no INTO @move_no
>
> END
> CLOSE curMove_no
> DEALLOCATE curMove_no
>
> --------------------------------------------------------------------------------------
>
> Which runs in less than a second , but if I eliminate the top 10 from cursor
> declare section then this will take almost 30 MINUTES to complete.
> I am sure the select section is returning only 4 records, so having top 10
> is only on safe side.
>
> but why is this happeing? what is the different between top 10 and not
> mentioning it? why that much time difference is there ?
>
> Thanks in advance,
> Pat

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:1134682129.676419.243750@z14g2000cwz.googlegroups.com...
> Patrick wrote:
>> Hi Freinds,
>> SQL 2000 SP3
>>
>> I am running the following :
>> -----------------------------------------------------------------------
>> DECLARE curMove_no CURSOR FOR
>> SELECT top 10 lm.move_no
>> FROM lib_movement lm with (nolock)
>> left outer JOIN lib_shelf ls  with (nolock) ON lm.shelf = ls.shelf
>> WHERE move_no in (select move_no from lib_movement_detail where master_no
>> in
>> (select master_no from lib_master where master_desc = 'The Wild Bunch'))
>>
>> DECLARE @recip varchar(255) --Email Address
>> DECLARE @move_no bigint  -- Working Movement number
>>
>> OPEN curMove_no
>> FETCH NEXT FROM curMove_no INTO @move_no
>> WHILE @@FETCH_STATUS = 0
>> BEGIN
>>  SET @recip = (SELECT  isnull(ls.note_text,'notvalid') FROM lib_movement
>> lm
>> with (nolock) INNER JOIN  lib_shelf ls ON lm.shelf = ls.shelf  and
>> ls.vault_no in (13,40)  WHERE move_no = @move_no)
>>  print @recip
>>  print @move_no
>>  print '----------------'
>>
>>  FETCH NEXT FROM curMove_no INTO @move_no
>>
>> END
>> CLOSE curMove_no
>> DEALLOCATE curMove_no
>>
>> --------------------------------------------------------------------------------------
>>
>> Which runs in less than a second , but if I eliminate the top 10 from
>> cursor
>> declare section then this will take almost 30 MINUTES to complete.
>> I am sure the select section is returning only 4 records, so having top
>> 10
>> is only on safe side.
>>
>> but why is this happeing? what is the different between top 10 and not
>> mentioning it? why that much time difference is there ?
>>
>> Thanks in advance,
>> Pat
>
> What's the purpose of the cursor? If all it does is PRINT results in a
> loop then why not use a SELECT statement instead? Please post DDL,
> sample data, required results if you need more help.
>
> --
> David Portas
> SQL Server MVP
> --
>