Pssst! Wanna see some Intra-Query Parallelism?

If you think about this query for a moment,

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

you might not be surprised that it does this to a SQL Anywhere 12 server... even with an empty SQL Anywhere 12 database:



That's because SYSCOLUMN is a view on top of ISYSTABCOL, and there are 2,011 rows in ISYSTABCOL even for an "empty" SQL Anywhere 12 database (there are a lot of columns in all those system catalog tables)...

...and there are 2,011 times 2,011 times 2,011 equals 8,132,727,331 rows in the three-way CROSS JOIN of SYSCOLUMN with itself.

That's a lotta rows to count, and if there are eight CPUs with nothing else to do, SQL Anywhere's gonna give them all a piece of the work to do.

Let's recap: That's one query running all 8 CPUs up to 100%.

Hubba Hubba!

That's nasty!

No, that's cool!

That's intra-query parallelism!

Wanna see another feelthy peecture?

Here you go, here's Foxhound showing 8 separate "INT" connections inside SQL Anywhere running the "Exchange algorithm" to hurry things along:



Those eight "INT: Exchange" connections are separate from the dbisql connection "SQL_DBC_4bdc7f00" which is fired off the SELECT COUNT(*), and they are a special kind of connection:

• they are not counted in the server-level "Conns" column which is based on the ConnCount database-level property, but
  

• they are counted in the "Executing" column which is based on the ReqStatus connection-level property, and of course
  

• all sorts of information is available at the connection level, otherwise Foxhound wouldn't be showing all that detail in the "10 connections" section.
  

The "CPU Time" columns are interesting: At the server level, the total is 4m 8.1s across all 8 CPUs, while at the connection level each "INT: Exchange" shows CPU Time over 4m 5s for its individual CPU.

---