Worker processes side effect

[Guest_imported]

Hi,

I've found this problem using Sybase 12.
As you know, it uses worker processes for performing table scans, what is good.

But this simple example (imagine field_1 is not part of and index thus, it'll launch worker processes)

set rowcount 1
select @var_name = field_1 from TABLE_NAME
select @var_name

Sometimes returns a value and sometimes not. It seems it has some problems assigning the result to a value or whatever.

However, this work fine:

set rowcount 1
select @var_name = field_1 from TABLE_NAME (parallel 1)
select @var_name

And what is really weird, this always returns a row too:

set rowcount 1
select field_1 from TABLE_NAME

Any documentation on this? Explanations?

Regards,
Roberto

 

[JMCraig]

Check Sybase's site for an EBF whose cover letter mentions assigning values or something else that might be related.

I have seen similar odd behavior at times in the past, but I can't put my finger on an example. What I try to do when I'm trying find something like that (a non-key value) is go through the table by key ranges anyway. E.g.:

declare @key int, @maxkey int
select @key = min(key_column) from the_table
/* Sybase docs say to combine assignments when possible; don't do it here; the optimizer may choose to scan the table from min to max--just use two SELECT's */
select @maxkey = max(key_column) from the_table

declare @var_name /* your type goes here */

while @key is not null begin
select @var_name = field_1
from the_table
where key_column = @key

/* do your thing with @var_name value */

select @key = min (key_column) from the_table
where key_column > @key
end /* while */

Now, you could also use a cursor--depending upon what you're doing you might also want to jump around more in the table (e.g. skipping over all the rows that have the same @var_name value as the row ID'd by @key). At any rate, that basic approach works well for things that don't exactly fit a cursor model (or where you prefer to not have the overhead of a cursor).

HTH,

J M Craig
Alpha-G Consulting, LLC
nsjmcraig@netscape.net