simple select, but only one core is working.

[Former Member]

Dear All,

I have a problem with my ASA.

SQL Anywhere Network Server Version 12.0.1.3152

Connection limit (licensed seats): 3

By ASA: Processors detected: 1 (containing 8 logical processors)

In reality: I have 2CPU xQuadracore

I cannot get ASA to use more than one core. In real situation I have big SQL that contains logic like

select fun(id), id from table1.

Fun(id) executes quite long (over 5 sec) and number of rows is more 30k.

I have tried to make parallel run this query using something like

select fun(id), id from table1 where id <15000
 union all
select fun(id), id from table1 where id >=15000

but still only one core was in busy (looks like ASA runs "select" part on one core and fetching on second core). When I do Get query plan - it is visible that optimizer divided query into 2 parallel tasks.

Then to eliminate effect of disks etc, I have created 2 functions - emu() and emu2() and I have tried to run these functions in parallel. But still only one core is in use (in my understanding optimized shows that 2 tasks can be executed in parallel)

 select -> into two parallel tasks "dummy" and "dummy"

What can be wrong?

Asa starts like:

dbsrv12 -gn80 -gtc 8 -gnl 80 -c 700 demo.db

demo.db is from Sybase distribution

I have tried different parameters of max_query_tasks

My functions and select:

create function emu () returns bigint
begin
    declare i BIGINT ;
    declare i2 BIGINT ;
    SET i = -3000000;
    WHILE i <= 3000000 LOOP
        SET i = i + 1;
        SET i2 = -4;
        WHILE i2 <= 0 LOOP
            SET i2 = i2 + 1;
        END LOOP;
    END LOOP;
    return i
end
go

create function emu2 () returns bigint
begin
    declare i BIGINT ;
    declare i2 BIGINT ;
    SET i = -3000000;
    WHILE i <= 3000000 LOOP
        SET i = i + 1;
        SET i2 = -4;
        WHILE i2 <= 0 LOOP
            SET i2 = i2 + 1;
        END LOOP;
    END LOOP;
    return i
end
go

select emu()
 union
select emu2();

or very similar example (if you say that no tables in query below):

select emu(),id from customers
 union
select emu2(), id from contacts;

p.s. interesting, that if I run my selects and functions on IQ 15x, then 4 cores are in use (IQ on server with 4 cores)

[Breck_Carter]

Try it without all the -g... options.

FWIW here is a query that should exercise all the cores; if it doesn't, something is awry:

SELECT COUNT_BIG(*) FROM SYSCOLUMN AS A CROSS JOIN SYSCOLUMN AS B CROSS JOIN SYSCOLUMN AS C;

[Former Member]

Breck, your select is nice. I see that all cores are becomming busy 100%. At least it means that my licence is correct and ASA can use all cores.

may be you can advise some direction how to make parallel quesries like select function(param1, param2, ..), param1, param2... from table1

I have tried via union, but not sucessfully (function is long running)

[Breck_Carter]

The code you posted doesn't do anything at all with tables, and the rules for intra-query parallelism only talks about table-related processing; see http://dcx.sybase.com/index.html#1201/en/dbusage/parallelism.html

If the code you posted is not the actual code you are interested in, please post the actual code; perhaps someone might have an idea. For example, one way to force parallelism is to use an EVENT; see http://sqlanywhere.blogspot.ca/2012/12/intra-procedure-parallelism_5.html

[Former Member]

Thank you Breck!

I was thinking that select emu(), id from customers and select emu2(), from contacts must run in parallel - scan of 2 not related tables. But I understood why it is not so when look your link.