set rowcount

[wimvanherp]

If I do a large select on a database wich can give me f.i. 80000 lines there is a risk that the server gets stuck with this command and that other users get problems (like the tempdatabase getting full). If I use" set rowcount 100 "I get only the first 100 results. Is there a way to split the results into smaller blocks so that I'm sure not to block the sybase server ?

Wim Vanherp
Wim.Vanherp@belgacom.net

 

[sybaseguru]

It depends on many things like the size of your tempdb and whether you are doing large joins etc in select. There is no automatic (what is safe rowcount). But you can write a small "while" loop to loop over data and display data in safe chunks say 5000 record each time

select @rows = count(1) from <TABLE_NAME>
if(@rows) > ${NUMBER_OF_ROWS_TO_DO}
begin
set rowcount ${NUMBER_OF_ROWS_TO_DO}
while (@rowcount <= @rows)
begin
select * from table etc
select @rowcount = @rowcount + ${NUMBER_OF_ROWS_TO_DO}
end
end

 

[wimvanherp]

As far as i can see the select * from table always selects the first ${NUMBER_OF_ROWS_TO_DO} , the next chunks will never be selected...

Wim Vanherp
Wim.Vanherp@belgacom.net

 

[sybaseguru]

Sorry for misunderstanding. This was just a guide. I cut and pasted from a program of mine dealing with other things

You need to use a counter on the table to increment row values and move across records so you always select the next batch. For example, a unique incremental key or an identity column can be used on the table. I do not know the table structure so I cannot advise you on it.